Bug #6151 myisam index corruption
Submitted: 19 Oct 2004 2:27 Modified: 22 Oct 2004 21:14
Reporter: Matt Lord Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:4.1.6 and 4.1.7-bk OS:Microsoft Windows (windows & linux)
Assigned to: Ingo Strüwing

[19 Oct 2004 2:27] Matt Lord
Description:
.MYI file gets corrupted every time when using this dump file and delete statements.

How to repeat:
run the statements in ftp.mysql.com/pub/mysql/secure/inc.dump.gz

then these delete statements:

DELETE FROM incidents WHERE (ref_no = '041018-000031');
DELETE FROM incidents WHERE (ref_no = '041018-000032');
DELETE FROM incidents WHERE (ref_no = '041018-000034');
DELETE FROM incidents WHERE (ref_no = '041018-000035');
DELETE FROM incidents WHERE (ref_no = '041018-000036');
DELETE FROM incidents WHERE (ref_no = '041018-000037');
DELETE FROM incidents WHERE (ref_no = '041018-000038');
DELETE FROM incidents WHERE (ref_no = '041018-000043');
DELETE FROM incidents WHERE (ref_no = '041018-000048');
DELETE FROM incidents WHERE (ref_no = '041018-000051');
DELETE FROM incidents WHERE (ref_no = '041018-000053');
DELETE FROM incidents WHERE (ref_no = '041018-000056');
DELETE FROM incidents WHERE (ref_no = '041018-000060');
DELETE FROM incidents WHERE (ref_no = '041018-000064');
DELETE FROM incidents WHERE (ref_no = '041018-000069');
DELETE FROM incidents WHERE (ref_no = '041018-000073');
DELETE FROM incidents WHERE (ref_no = '041018-000075');
DELETE FROM incidents WHERE (ref_no = '041018-000076');
DELETE FROM incidents WHERE (ref_no = '041018-000077');
DELETE FROM incidents WHERE (ref_no = '041018-000078');
DELETE FROM incidents WHERE (ref_no = '041018-000079');
DELETE FROM incidents WHERE (ref_no = '041018-000080');
DELETE FROM incidents WHERE (ref_no = '041018-000084');
DELETE FROM incidents WHERE (ref_no = '041018-000085');
DELETE FROM incidents WHERE (ref_no = '041018-000086');
DELETE FROM incidents WHERE (ref_no = '041018-000089');
DELETE FROM incidents WHERE (ref_no = '041018-000090');
DELETE FROM incidents WHERE (ref_no = '041018-000091');
DELETE FROM incidents WHERE (ref_no = '041018-000092');
DELETE FROM incidents WHERE (ref_no = '041018-000096');
DELETE FROM incidents WHERE (ref_no = '041018-000098');
DELETE FROM incidents WHERE (ref_no = '041018-000100');
DELETE FROM incidents WHERE (ref_no = '041018-000102');
DELETE FROM incidents WHERE (ref_no = '041018-000103');
DELETE FROM incidents WHERE (ref_no = '041018-000104');
DELETE FROM incidents WHERE (ref_no = '041018-000106');
DELETE FROM incidents WHERE (ref_no = '041018-000107');
DELETE FROM incidents WHERE (ref_no = '041018-000108');
DELETE FROM incidents WHERE (ref_no = '041018-000109');
DELETE FROM incidents WHERE (ref_no = '041018-000110');
DELETE FROM incidents WHERE (ref_no = '041018-000111');
DELETE FROM incidents WHERE (ref_no = '041018-000112');
DELETE FROM incidents WHERE (ref_no = '041018-000125');
DELETE FROM incidents WHERE (ref_no = '041018-000129');
DELETE FROM incidents WHERE (ref_no = '041018-000131');
DELETE FROM incidents WHERE (ref_no = '041018-000132');
DELETE FROM incidents WHERE (ref_no = '041018-000133');
DELETE FROM incidents WHERE (ref_no = '041018-000137');
DELETE FROM incidents WHERE (ref_no = '041018-000139');
DELETE FROM incidents WHERE (ref_no = '041018-000140');
DELETE FROM incidents WHERE (ref_no = '041018-000142');
DELETE FROM incidents WHERE (ref_no = '041018-000144');
DELETE FROM incidents WHERE (ref_no = '041018-000145');
DELETE FROM incidents WHERE (ref_no = '041018-000151');
DELETE FROM incidents WHERE (ref_no = '041018-000153');
DELETE FROM incidents WHERE (ref_no = '041018-000157');
DELETE FROM incidents WHERE (ref_no = '041018-000158');
DELETE FROM incidents WHERE (ref_no = '041018-000159');
[19 Oct 2004 2:37] Matt Lord
To be a little more specific about the machines I verified the problem on:
windows 2000 SMP
RH 9 (Linux booty 2.4.21 #12 SMP Thu Aug 14 00:49:40 EDT 2003 i686 i686 i386 GNU/Linux)
[19 Oct 2004 17:23] Alexey Kopytov
Not reproducible on 4.0 from BK.
[20 Oct 2004 10:20] Ingo Strüwing
I cannot get at the file. Please attach it to the "Files" section of this bug report.
[21 Oct 2004 13:25] Ingo Strüwing
My current findings are the following: The order of keys in the key file changed from 4.0 to 4.1. In 4.1 it appears that keys are compared as if they had blanks past their end, which is not the case in 4.0. The new key order seems to disturb the (packed-) key removal function. Based on this findings I could break down the customers case to:

DROP TABLE IF EXISTS t1; 
CREATE TABLE t1 (
  c1 int, 
  c2 varbinary(240),
  UNIQUE KEY (c1), 
  KEY (c2)
) ENGINE=MyISAM;
INSERT INTO t1 VALUES (1,'\Z\Z\Z\Z');
INSERT INTO t1 VALUES (2,'\Z\Z\Z\Z\Z\Z');
INSERT INTO t1 VALUES (3,'\Z\Z\Z\Z');
DELETE FROM t1 WHERE (c1 = 1);
DELETE FROM t1 WHERE (c1 = 3);

It does not matter, if c2 is varbinary(240) or char(10) or some other character type. The only thing that matters is that we need to have these control characters (ASII code below BLANK). With character codes above BLANK, everything works OK.

The question is now if the new key order is intentional.
[21 Oct 2004 13:48] Alexander Barkov
Ingo, you are right. Keys are compared like if the values were
padded up to the maximum length with space character.

There are two functions in CHARSET_INFO implementing comparison:

strnncoll - for usual comparison
strnncollsp - for comparison with space padding behaviour.

Probably strnncoll should be changed into strnncollsp in 
some places in MyISAM.
[21 Oct 2004 20:22] Ingo Strüwing
bk commit - 4.1 tree (ingo:1.2099) BUG#6151
[22 Oct 2004 21:14] Ingo Strüwing
Fixed the packed key removal code. It relied on the fact that shorter keys come before longer keys with the same start. This is not always true anymore, since in 4.1 keys are compared by blank padding.

Fixed in 4.1.7.