Description:
During analyzing bug #34298 I figured out that CHECK TABLE don't react like documented.
There is nothing written down at http://dev.mysql.com/doc/refman/5.1/en/check-table.html which let you assume that CHECK TABLE will repair Indexes.
After I figured out that a simple "CHECK TABLE" is enough to display data in the right way at bug #34298, I flushed tables and copied all database files.
Then I ran "CHECK TABLES" and after this I looked to the MD5 of the files.
.MYI md5 were different.
I hexdumped both files and made a diff from the hexdumps.
After copying test.MYI:
mysql> check table test;
+--------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------+-------+----------+----------+
| miracee.test | check | status | OK |
+--------------+-------+----------+----------+
$ md5sum test.MYI
eb2254ff10638b25ad4ded21586d4257 test.MYI
$ md5sum test2.MYI
b76edf3611e80c56398551b461290f05 test2.MYI
$ diff -u hex1 hex2 > hexdiff
ยง less hexdiff
--- hex1 2008-03-11 14:44:44.000000000 +0100
+++ hex2 2008-03-11 14:44:38.000000000 +0100
@@ -1,5 +1,5 @@
0000000 fefe 0107 0200 a903 b000 6400 2401 0f00
-0000010 0000 0006 0108 0000 0000 ff30 0000 0000
+0000010 0000 0006 0108 0000 0000 ff38 0000 0000
0000020 0000 ac0d 0000 0000 0000 0000 0000 0000
0000030 0000 ac0d ffff ffff ffff ffff 0000 0000
0000040 0300 00b0 0000 0000 0600 f031 0000 0000
@@ -11,9 +11,9 @@
00000a0 0300 0034 0000 0000 0300 00ac ffff ffff
00000b0 ffff ffff 0000 0000 0000 0000 d647 368b
00000c0 0000 0000 0000 3f00 0000 0000 d647 368b
-00000d0 0000 0000 0000 0000 0000 0000 d647 ec8b
-00000e0 0000 0000 0000 ac0d 0000 ac0d 0000 0b00
-00000f0 0000 0b00 0000 0b00 0000 0100 0000 0100
+00000d0 0000 0000 0000 0000 0000 0000 d647 648b
+00000e0 0000 0000 0000 ac0d 0000 0000 0000 0000
+00000f0 0000 0000 0000 0000 0000 0000 0000 0000
0000100 0000 0100 0000 ac0d 0000 0b00 0000 0b00
0000110 0000 0b00 0000 ac0d 0000 0b00 0000 d606
0000120 0000 0300 0000 0000 0000 0004 0000 0000
How to repeat:
Get the data dump of bug #34298
mysql> create table test(`P_PLANT` char(4) NOT NULL DEFAULT '', `P_MATNR` char(18) NOT NULL DEFAULT '', `P_REFCD` char(2) DEFAULT NULL, `P_DIS_CON_IND` char(1) DEFAULT NULL, `P_PRO_TYPE` char(1) DEFAULT NULL, `P_SPEC_PRO` char(2) DEFAULT NULL, `BOM_USE` char(1) NOT NULL DEFAULT '', `ALT_BOM` char(2) NOT NULL DEFAULT '', `P_MRP_CON` char(3) DEFAULT NULL, `DISCON_IND` char(1) DEFAULT NULL, `POSNR` char(4) NOT NULL DEFAULT '', `C_PLANT` char(4) NOT NULL DEFAULT '', `C_MATNR` char(18) NOT NULL DEFAULT '', `C_REFCD` char(2) DEFAULT NULL, `C_DIS_CON_IND` char(1) DEFAULT NULL, `C_PRO_TYPE` char(1) DEFAULT NULL, `C_SPEC_PRO` char(2) DEFAULT NULL, `C_MRP_CON` char(3) DEFAULT NULL, `QTY` decimal(13,3) DEFAULT NULL, `UNIT` char(3) DEFAULT NULL, `ITEM_CAT` char(1) DEFAULT NULL, `REL_COST` char(1) DEFAULT NULL, `SORT_STR` char(10) DEFAULT NULL, `MAT_TYPE` char(4) DEFAULT NULL, `VALID_FR` date DEFAULT NULL, `CREATED_DATE` date DEFAULT NULL, `CHANGED_DATE` date DEFAULT NULL, `CREATED` datetime DEFAULT NULL, PRIMARY KEY (`P_PLANT`,`P_MATNR`,`BOM_USE`,`ALT_BOM`,`POSNR`,`C_PLANT`,`C_MATNR`), KEY `COMP2` (`P_PLANT`,`P_MATNR`,`ALT_BOM`,`BOM_USE`), KEY `P_PLANT` (`P_PLANT`), KEY `P_MATNR` (`P_MATNR`), KEY `C_PLANT` (`C_PLANT`), KEY `C_MATNR` (`C_MATNR`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
mysql> load data infile '/PATH/DATADUMP' into table test fields terminated by ';' lines terminated by '\n';
mysql> flush tables;
$ cp DATADIRECTORY/test.MYI DATADIRECTORY/test2.MYI
mysql> check table test;
On Linux: md5sum, on FreeBSD md5:
$ md5sum DATADIRECTORY/test.MYI
$ md5sum DATADIRECTORY/test2.MYI
You will see two different md5.
$ hexdump test.MYI > hex1
$ hexdump test2.MYI > hex2
$ diff -u hex1 hex2 > hexdiff
$ less hexdiff
you will see some changes.
Suggested fix:
CHECK TABLE shouldn't change/repair MYI files without giving a note to the user and it shouldn't do this without documentation.
