MySQL Troubleshooting

When the Problem May Have Been a Previous Update

If a SELECT returns a result set you don’t expect, this does not always mean something is wrong with the query itself. Perhaps you didn’t insert, update, or delete data that you thought you had.

Before you investigate this possibility, you should faithfully carry out the investigation in the previous section, where we discussed a badly written SELECT statement. Here I examine the possibility that you have a good SELECT that is returning the values you asked for, and that the problem is your data itself. To make sure the problem is in the data and not the SELECT, try to reduce it to a simple query on a single table. If the table is small, go ahead and remove all the WHERE clauses, as well as any GROUP BY clauses, and examine the full data set with a brute-force SELECT * FROM table_name. For a larger table, judiciously use WHERE to cull the values you want to examine, and consider COUNT(*) if you just want to make sure the number of rows matching the query is what you expect.

Once you are sure the SELECT works fine, this means the data is inconsistent and you need to find where it is broken. There can be a lot of reasons: a badly applied backup, an incorrect UPDATE statement, or a slave getting out of sync with the master, just to name the most common. In this section, we’ll look at some examples where a bad DELETE or UPDATE isn’t revealed until a later SELECT. In a later section, we’ll address those puzzling cases where the problem turns ...