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 up long after it was triggered, and show you how to work backward to find the error. This section does not deal directly with problems in transactions, which are discussed in Chapter 2. Here I show cases where data in the database is stable, i.e., all transactions, if used, were completed. I will continue using examples reduced down from real cases.

Let’s start from the best possible case, when data inconsistency was noticed right after the error was made. We will use the following initial data set:

mysql> CREATE TEMPORARY TABLE t1(f1 INT);
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TEMPORARY TABLE t2(f2 INT);
Query OK, 0 rows affected (0.08 sec)
mysql> INSERT INTO t1 VALUES(1);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM t1;
+------+
| f1   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

In the application, the tables shown were temporary tables containing partial result sets selected from the main log table. This is a common technique frequently used in stored routines when only a small set of data from the main table is needed but the user doesn’t want to change the data in the main table or lock the table.

So in this example, after finishing with a result set, the user wanted to delete rows from both tables. It always looks amazing when you can do things in a single query. But real life can work out differently from your plans, and you can get unexpected results or side effects:

mysql> DELETE FROM t1, t2 USING t1, t2;
Query OK, 0 rows affected (0.00 sec)

If the user paid attention to the string printed in response to the DELETE, he would realize right away that something had gone wrong. No rows were affected by the DELETE, meaning that it did nothing. The output from a statement is often not so obvious, however, and sometimes it is never seen, because the SQL statement is run within a program or script with no human being to watch over the results. In general, you should always check information returned by a statement execution to learn how many rows were affected and whether this value is same as you expect. In an application, you must explicitly check information functions.

Let’s continue. If you run SELECT immediately, you could be surprised, thinking that the query was incorrect or even that the query cache had not been cleared:

mysql> SELECT * FROM t1;
+------+
| f1   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

You can be sure this is not a cache or some other problem if you convert the SELECT to ask for the number of rows. This easy example shows how we can use different ways to query a table to be sure the data is consistent:

mysql> SELECT count(*) FROM t1;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

COUNT(*) still returns a positive number here, which shows the table is not empty as desired. As an attentive user would have seen, the DELETE didn’t actually remove any rows. To find out why, we can convert the DELETE to the corresponding SELECT. This will show us which rows satisfy the condition for the delete.

Although our simple example had no WHERE clause, the technique is certainly useful to check the impact of a WHERE clause in a delete or update. The rows returned by a SELECT are the ones that DELETE would delete or that UPDATE would change:

mysql> SELECT * FROM t1, t2;
Empty set (0.00 sec)

Consistent with previous results, this returns an empty set. That’s why no rows were removed! It still might not be clear why this happens, but now that we have a SELECT, we can use familiar techniques from the previous section. For this case, our best choice is to run SELECT followed by EXPLAIN and analyze its output:

mysql> \W
Show warnings enabled.

mysql> EXPLAIN EXTENDED SELECT * FROM t1, t2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: system
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra:
*************************** 2. row ***************************
           id: 2
  select_type: SIMPLE SUBQUERY
        table: t2
         type: system
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 0
     filtered: 0.00
        Extra: const row not found
2 rows in set, 1 warning (0.03 sec)

Note (Code 1003): select '1' AS `f1`,'0' AS `f2` from `test`.`t1` join `test`.`t2`

The final note in the output shows that the query was modified to an (inner) JOIN, which can return rows from each table only if there are matching rows in the other table. For each row in table t1, there should be at least one row in table t2 with a matching value in a matching row. In this case, because table t2 is empty, naturally the join returns an empty set.

  • We just learned another important technique that helps us find out what is wrong with an UPDATE or DELETE: convert it to a SELECT with the same JOIN and WHERE conditions. With a SELECT, you can use EXPLAIN EXTENDED[3] to get the actual execution plan, as well as to manipulate the result set without the risk of modifying the wrong rows.

Here’s a more complex example using UPDATE. We will use the items table again:

mysql> SELECT SUBSTR(description, 1, 20), additional IS NULL FROM items;
+----------------------------+--------------------+
| substr(description, 1, 20) | additional IS NULL |
+----------------------------+--------------------+
| NULL                       |                  1 |
| NULL                       |                  1 |
| One who has TRIGGER        |                  1 |
| mysql> revoke insert       |                  1 |
| NULL                       |                  0 |
+----------------------------+--------------------+
5 rows in set (0.00 sec)

mysql> SELECT description IS NULL, additional IS NULL FROM items;
+---------------------+--------------------+
| description IS NULL | additional IS NULL |
+---------------------+--------------------+
|                   1 |                  1 |
|                   1 |                  1 |
|                   0 |                  1 |
|                   0 |                  1 |
|                   1 |                  0 |
+---------------------+--------------------+
5 rows in set (0.00 sec)

The description and additional fields are of type TEXT. In this example, we will use an erroneous query that is supposed to replace NULL values with more descriptive text (“no description” for one table and “no additional comments” for the other):

mysql> UPDATE items SET description = 'no description' AND
additional = 'no additional comments' WHERE description IS NULL;
Query OK, 3 rows affected, 3 warnings (0.13 sec)
Rows matched: 3  Changed: 3  Warnings: 3

This query updates some data (“3 rows affected”), but let’s check whether we have the proper values in the table now:

mysql> SELECT SUBSTR(description, 1, 20), additional IS NULL FROM items;
+----------------------------+--------------------+
| substr(description, 1, 20) | additional IS NULL |
+----------------------------+--------------------+
| 0                          |                  1 |
| 0                          |                  1 |
| One who has TRIGGER        |                  1 |
| mysql> revoke insert       |                  1 |
| 0                          |                  0 |
+----------------------------+--------------------+
5 rows in set (0.09 sec)

As we see, three rows changed their values in the description field, but 0 is different from the “no description” string we thought we were setting. Furthermore, the values in the additional field have not changed at all. To find out why this happened, we should check warnings. Notice in these statements returned by the server that we see a warnings count of three:

Query OK, 3 rows affected, 3 warnings (0.13 sec)
Rows matched: 3  Changed: 3  Warnings: 3

mysql> SHOW WARNINGS;
+---------+------+----------------------------------------------------+
| Level   | Code | Message                                            |
+---------+------+----------------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'no description' |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'no description' |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'no description' |
+---------+------+----------------------------------------------------+
3 rows in set (0.00 sec)

The message looks strange. Why does it complain about a DOUBLE when description and additional are TEXT fields, as the following queries prove?

mysql> SHOW FIELDS FROM items LIKE 'description';
+-------------+------+------+-----+---------+-------+
| Field       | Type | Null | Key | Default | Extra |
+-------------+------+------+-----+---------+-------+
| description | text | YES  |     | NULL    |       |
+-------------+------+------+-----+---------+-------+
1 row in set (0.13 sec)

mysql> SHOW FIELDS FROM items LIKE 'additional';
+------------+------+------+-----+---------+-------+
| Field      | Type | Null | Key | Default | Extra |
+------------+------+------+-----+---------+-------+
| additional | text | YES  |     | NULL    |       |
+------------+------+------+-----+---------+-------+
1 row in set (0.13 sec)

We also want to know why we did not get any warning about the additional field, when it was not changed at all.

Let’s split the query in chunks and examine what it going in each of them:

UPDATE items

This is a common start for an UPDATE, and nothing’s wrong with it:

SET description = 'no description' AND additional = 'no additional comments'

That used a SET statement. Let’s examine what it is actually doing. What does the keyword AND mean in this case? Let me add parentheses to the query to underline operator precedence:

SET description = ('no description' AND additional = 'no additional comments')

So actually, the statement calculates the expression:

'no description' and additional = 'no additional comments'

and then assigns it to description. Evaluating the equal sign produces a Boolean result as a LONGLONG value. To prove this, start the MySQL command line client with the --column-type-info option and run the SELECT again:

$ mysql --column-type-info
mysql> SELECT 'no description' AND additional = 'no additional comments' FROM items;
Field   1:  `'no description' AND additional = 'no additional comments'`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONGLONG
Collation:  binary (63)
Length:     1
Max_length: 1
Decimals:   0
Flags:      BINARY NUM


+------------------------------------------------------------+
| 'no description' AND additional = 'no additional comments' |
+------------------------------------------------------------+
|                                                          0 |
|                                                          0 |
|                                                          0 |
|                                                          0 |
|                                                          0 |
+------------------------------------------------------------+
5 rows in set, 5 warnings (0.09 sec)

We clearly see that the result of the expression is 0, which was inserted into the description field. And because our update to the additional field got buried inside the absurd expression, nothing was inserted into the field, nor did the server see any reason to comment about the field.

Now you can rewrite the query without logic errors:

UPDATE items SET description = 'no description',
additional = 'no additional comments' WHERE description IS NULL;

You can examine the WHERE clause if you want, but in this case it has no error.

This example shows the importance of return values and information about query execution. Let’s discuss them a bit more.


[3] Since version 5.6.3, you can use EXPLAIN with UPDATE and DELETE as well, but converting such a query into SELECT still makes sense because it is easier to examine an actual result set and manipulate it than to just use EXPLAIN. This is especially true for complicated JOINs when EXPLAIN shows it examined more rows than were actually updated.

Get MySQL Troubleshooting now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial