Tag Archives: MySQL

Pre-packaged MAMP, LAMP, and WAMP stacks have been common on developer’s computers for years. Such packages are convenient because they provide a single-step install process, with all components in the server stack preconfigured to work together, and off you go.

Building MySQL web applications with a team of developers will inevitably present the challenge of database schema changes. Any good web developer understands the importance of keeping all code under version control, but how many follow the same principle for the database?

What follows are some best practices that I have learned over the past several years from the school of hard knocks. Continue reading →

If you have ever touched a MySQL slave, you know that they can and do frequently halt. While sync problems can be caused by many things—network outages, schema changes, etc—one of the most common problems in a dual-master setup is primary key collision.

Primary Key Collision

…happens when records are added on two different servers to the same table and get the same AUTO_INCREMENT value. Fortunately, there is a trivially easy way to prevent this from happening.

auto-increment-increment=N

Adding this to your my.cnf or my.ini file will make AUTO_INCREMENT increment by N rather than by 1. N is the number of replicated servers that are masters.

INSERT INTO…SELECT locks the table being read by the SELECT statement due to MySQL 5.0’s statement-based replication. Here’s a great post from the MySQL Performance Blog explaining the problem in detail and what to do about it.

Note: this problem is supposed to be eliminated when using MySQL 5.1 row-based replication.

I thought it would be interesting to see how the Drizzle developers where handling this scenario in their high-performance, high-concurrency remake of the MySQL. Below is an e-mail I got back from Jay Pipes on the subject: Continue reading →

If a MySQL slave encounters an error while replicating commands from the master, the slave will abort.

One way this can happen is if you are using triggers on a table that calls a stored procedure, but the stored procedures are missing on the slave because you forgot to include the –routines option when generating a mysqldump from the master to import to the slave.

So what do you do? First, verify that the slave is indeed encountering errors:

If either Slave_IO_Running or Slave_SQL_Running are ‘No’ then the slave has aborted. Here’s how to fix it:

Stop the slave

mysql> STOP SLAVE;

Resolve the error

Pay attention to the Last_Error field and try to resolve it. If this is a recurring issue you may need to import a fresh dump from the master to bring your slave back into sync. If the situation merits it, you can run the following to have the slave skip the offending SQL query:

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

Be careful, though because this could have severe repercussions down the line.

Restart the slave

mysql> START SLAVE;

Verify that the slave is running

mysql> SHOW SLAVE STATUS \G

When Slave_IO_Running and Slave_SQL_Running both are set to ‘Yes’ then you’re good to go. It would probably be a good idea to have some sort of monitor to ensure your slaves are indeed running when you need them to. Perhaps I’ll throw together a quick script to do that…

Indexes are NOT used if MySQL can calculate that it will probably be faster to scan the whole table. For example if key_part1 is evenly distributed between 1 and 100, it’s not good to use an index in the following query:

In this table (~175,000 rows), leadId, campaignId, and xid all have indexes, but MySQL was doing a table scan (~2.2 sec) because of the duplicate use of leadId and the way the WHERE clause was structured. This rather unusual optimization was more than 10x faster (~.17 sec):