Take a look at this:
SQL:
- mysql> repair TABLE a3;
- +---------+--------+----------+----------+
- | TABLE | Op | Msg_type | Msg_text |
- +---------+--------+----------+----------+
- | test.a3 | repair | STATUS | OK |
- +---------+--------+----------+----------+
- 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
from
