MySQL Bugs: #6151: myisam index corruption

Bug #6151	myisam index corruption
Submitted:	19 Oct 2004 2:27	Modified:	22 Oct 2004 21:14
Reporter:	Matthew 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:	Windows (windows & linux)
Assigned to:	Ingo Strüwing	CPU Architecture:	Any

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');

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)

Not reproducible on 4.0 from BK.

I cannot get at the file. Please attach it to the "Files" section of this bug report.

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.

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.

bk commit - 4.1 tree (ingo:1.2099) BUG#6151

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.