MySQL Troubleshooting

Tracing Back Errors in Data

If you rigorously check the results of your queries and updates, you’ll catch many of the problems that could otherwise go undetected for weeks and cause a lot of grief when the problems finally grow too large to miss. But problems do creep up on you. Sometimes a SELECT suddenly starts returning wrong results, but your experiments with the query just confirm there is nothing wrong with it.

In this case, you need to imitate user actions, but in reverse order, until you find the source of the error. If you are lucky, you will catch the problem in a single step. Usually it will take multiple steps, and sometimes a very long time.

A lot of these issues happen because the data is different on the master and slave in a replication environment. One common problem is duplicate values where they are supposed to be unique (e.g., if a user relies on an INSERT ON DUPLICATE KEY UPDATE statement but a table has a different structure on the master and slave). For such setups, the user usually notices the problem later when SELECT statements query the slave, instead of noticing them when the INSERT takes place. Things become even worse when this happens during circular replication.

To illustrate this problem, we’ll work with a stored procedure that inserts into a table from a temporary table that was created to hold the results of other selects. This is another example of a common technique when a user wants to handle data from large tables without the risk of modifying ...