Inconsistent query results from MySQL

Question

Today we have spotted a strange behavior in SQL queries (we use MySQL v5.6.36 and InnoDB).

Take this query:

mysql> SELECT count(*)  FROM `inventory` WHERE `user_id` = 12345;

It returns the following result:

+----------+
| count(*) |
+----------+
|      495 |
+----------+

But then when a following query is run:

mysql> SELECT count(*)  FROM `inventory` WHERE `user_id` = 12345 AND list_type = 3;

We get:

+----------+
| count(*) |
+----------+
|     1263 |
+----------+

As you can see the result count is bigger when query is restricted which should not happen. What could be the cause of this? It happens in master database only while both replication databases show the correct results. We suspect corrupted indexes. How to prevent such errors in the future?

Any other conditions apart from list_type return invalid (too high) counts as well.

Answer 1

Inconsistent results could mean corrupt database or (if you're lucky) a corrupt index. Try to launch the above queries without using an index and see what you get:

SELECT count(*)  FROM `inventory` USE INDEX () WHERE `user_id` = 12345;
SELECT count(*)  FROM `inventory` USE INDEX () WHERE `user_id` = 12345 AND list_type = 3;

In case this is only an index you could try

OPTIMIZE TABLE `inventory`;

Which recreates the tables and indexes and then does ANALYZE on it. That's because InnoDB doesn't support REPAIR TABLE. Another option could be to try and add an identical index and then dropping the original index.

To perform checks on a table you could also use CHECK TABLE but if you wish to check the entire database you could try

mysqlcheck --login-path=credentials --databases db_name

and to optimize all tables

mysqlcheck --login-path=credentials --optimize --databases db_name

Looking at the error logs of the server might give you a hint on whether this was a hardware issue or some MySQL bug you've ran into.

In case your actual database is corrupt, it'll make sense to check the hardware and then try to see what has been corrupt and how to restore it by comparing to a recent backup.