Can you Trust CHECK TABLE ?

September 11, 2008

Author

Peter Zaitsev

Insight for DBAs

Share this Post:

Take a look at this:

mysql&gt; repair table a3;
+---------+--------+----------+----------+
| Table   | Op     | Msg_type | Msg_text |
+---------+--------+----------+----------+
| test.a3 | repair | status   | OK       |
+---------+--------+----------+----------+
1 row in set (0.10 sec)

mysql&gt; select * from a3 order by i;
+------------+
| i          |
+------------+
| 2147483648 |
|         11 |
|         13 |
|         14 |
| 2147483647 |
+------------+
5 rows in set (0.00 sec)

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

mysql> repair table a3;

+---------+--------+----------+----------+

+---------+--------+----------+----------+

+---------+--------+----------+----------+

1 row in set (0.10 sec)

mysql> select * from a3 order by i;

+------------+

| i |

+------------+

| 2147483648 |

| 11 |

| 13 |

| 14 |

| 2147483647 |

+------------+

5 rows in set (0.00 sec)

The sort order is obviously wrong while CHECK TABLE is not reporting any error

Why ? Because CHECK TABLE only looks at MyISAM data and Index files and it does not compare information in these to table definition (.frm file)

In this particular case I replaced .frm file for the table from different one changing INT to UNSIGNED INT to see what effect it will give – as you can see you get quite funny table which is considered OK by CHECK TABLE, which does store values larger than max signed int but which sorts them as unsigned ints. Quite fun.

I hope the task of fixing this is somewhere on MySQL roadmap 🙂