Take a look at this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
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 🙂
About the Author
Peter ZaitsevPeter managed the High Performance Group within MySQL until 2006, when he founded Percona. Peter has a Master's Degree in Computer Science and is an expert in database kernels, computer hardware, and application scaling.
Hi Peter,
is there a bug report for this already or should we create one?
Harmut,
I remember I ran into this 4-5 years ago when I was still with MySQL and it was described as product feature.
Though now it is the time to fix it I think – especially as there will be more and more meta data which will need to be tested. For example Global foreign keys with MyISAM – how do you check they are in order if there is no such command. What if you add CHECK constrains ?
How’s check table even going to if there’s an error here w/o carrying a second copy of the metadata in the MYD file? Seems like he can only reasonably be expected to compare the data in the MYD with the data in the FRM and if they look consistent all is well.
If you spoof the metadata like this, I think that qualifies as an event the product can’t reasonably be expected to recover from. Like going out and deleting the binary log from the file system and then opening a bug that replication is broken.
You can detect this situation to some extent by having some kind of hash of the frm in the myd/myi files. On the other hand, you can always break systems by messing directly with their internal files, so I wouldn’t argue that this is as important as perhaps providing tools for managing rollback/restore of these files as a group. Or just telling you not to do that sort of thing 🙂
Pat,
Actually there IS a second copy of meta data in MYI files…. which can be checked against what is in .frm files but it is not. This is artificial example of course but believe me I’ve seen mismatching .frm files from MYI/MYD files many times. In particular for MyISAM – because this is table type which is advertised as being possible to move around… so people do it. Not always correctly.
The example with replication is wrong. If you delete the binary log file from the system replication will complain just as you would expect. If you trash binary log it may not… though it is common complain too what checksums are missing in binary logs.
I wanted to thank you for this excellent read!! I definitely loved every little bit of it.Cheers for the info!!!! & This is the perfect blog for anyone who wants to know about this topic. You know so much its almost hard to argue with you ………
thanks