Bug #22119 Changing MI_KEY_BLOCK_LENGTH makes a wrong myisamchk
Submitted: 8 Sep 2006 13:33 Modified: 23 Jan 2007 20:34
Reporter: Guilhem Bichot Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S2 (Serious)
Version:5.1-bk OS:Linux (linux)
Assigned to: Ingo Strüwing CPU Architecture:Any

[8 Sep 2006 13:33] Guilhem Bichot
Description:
Modify MI_KEY_BLOCK_LENGTH in include/myisam.h to be 8192 instead of 1024, recompile, run storage/myisam/ma_test2: storage/myisam/myisamchk reports that the table storage/myisam/test2 is corrupted:
error: key delete-link-chain corrupted
I have debugged this a bit and think the bug is in myisamchk.
For example, add --myisam_block_size=1024 to the myisamchk parameters, and it reports no corruption (and if you look, mi_test2 does create an index with block size 1024).
Looks like myisamchk should take into account non-default block sizes. It's wrong that myisamchk needs to be told the block size on the command line, it should derive it from the table's information (with all backward compatibility problems I have not thought about).

How to repeat:
Modify MI_KEY_BLOCK_LENGTH in include/myisam.h to be 8192 instead of 1024, recompile, run storage/myisam/ma_test2: storage/myisam/myisamchk reports that the table storage/myisam/test2 is corrupted:
error: key delete-link-chain corrupted
[12 Oct 2006 7:59] Ingo Strüwing
The problem is much worse than this. I ran the test suite on the changed MI_KEY_BLOCK_LENGTH. 22 tests failed. A couple of them was expainable. The rest looks bad. In most cases EXPLAIN showed that the optimizer sees a wrong number of rows to examine. In some cases this leads to a different strategy. And there are cases where the order of the SELECT results differs.

Anonther thing is that the efficiency of the key cache decreased. I see less Key_read_requests and more Key_reads. Since the MyISAM key blocks are bigger, fewer of them needs to be requested as every one carries more data to work with. But since the key cache still reads in 1024 bytes blocks, it needs to read more blocks to satisfy the 8192 bytes requests. Performance-wise it may be good to increase KEY_CACHE_BLOCK_SIZE too.
[16 Oct 2006 15:42] Ingo Strüwing
It turned out that the optimizer estimates the time needed for reading through the index. It takes the key block length into account. This leads to slightly less estimates for larger blocks. In a couple of cases this lets the optimizer select a different strategy. The number of rows is an estimate for the number of rows required to read by the strategy. There are major differences between strategies. But there are also remarkable differences for the same strategy. Some estimates work on a single key block. Bigger blocks contain more keys and produce slightly more accurate estimates.

The only differences that I could not explain in a reasonable time are three SELECTs that produce a different order of rows in the result. However, the trace looks fine. There is no obvious wrong behaviour. I'll leave the miracle unresolved.

However, the corruption is repeatable as described. I'll attack this now.
[17 Oct 2006 8:31] Ingo Strüwing
Analyzing the problem in the table check code, I found the following test case to repeat the problem:

CREATE TABLE t1 (
  c1 INT,
  c2 VARCHAR(300),
  KEY (c1) KEY_BLOCK_SIZE 1024,
  KEY (c2) KEY_BLOCK_SIZE 8192
  );
INSERT INTO t1 VALUES (10, REPEAT('a', CEIL(RAND(10) * 300))),
  (11, REPEAT('b', CEIL(RAND() * 300))),
  (12, REPEAT('c', CEIL(RAND() * 300))),
  (13, REPEAT('d', CEIL(RAND() * 300))),
  (14, REPEAT('e', CEIL(RAND() * 300))),
  (15, REPEAT('f', CEIL(RAND() * 300))),
  (16, REPEAT('g', CEIL(RAND() * 300))),
  (17, REPEAT('h', CEIL(RAND() * 300))),
  (18, REPEAT('i', CEIL(RAND() * 300))),
  (19, REPEAT('j', CEIL(RAND() * 300))),
  (20, REPEAT('k', CEIL(RAND() * 300))),
  (21, REPEAT('l', CEIL(RAND() * 300))),
  (22, REPEAT('m', CEIL(RAND() * 300))),
  (23, REPEAT('n', CEIL(RAND() * 300))),
  (24, REPEAT('o', CEIL(RAND() * 300))),
  (25, REPEAT('p', CEIL(RAND() * 300))),
  (26, REPEAT('q', CEIL(RAND() * 300))),
  (27, REPEAT('r', CEIL(RAND() * 300))),
  (28, REPEAT('s', CEIL(RAND() * 300))),
  (29, REPEAT('t', CEIL(RAND() * 300))),
  (30, REPEAT('u', CEIL(RAND() * 300))),
  (31, REPEAT('v', CEIL(RAND() * 300))),
  (32, REPEAT('w', CEIL(RAND() * 300))),
  (33, REPEAT('x', CEIL(RAND() * 300))),
  (34, REPEAT('y', CEIL(RAND() * 300))),
  (35, REPEAT('z', CEIL(RAND() * 300)));
DELETE FROM t1 WHERE c1 >= 10;
CHECK TABLE t1;
DROP TABLE t1;

This means that not only 'myisamchk' is affected, but also the server.
[17 Oct 2006 9:38] Ingo Strüwing
A similar problem exists when checking clean indexes (not the delete chain). The following insertion between the INSERT and DELETE statements shows it:

INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
CHECK TABLE t1;
REPAIR TABLE t1;

(A somewhat bigger table is necessary to have nodes and leaves in the index, hence the INSERT ... SELECT statements).
[17 Oct 2006 15:41] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/13810

ChangeSet@1.2345, 2006-10-17 17:40:33+02:00, istruewing@chilla.local +3 -0
  Bug#22119 - Changing MI_KEY_BLOCK_LENGTH makes a wrong myisamchk
  
  When compiling with a default key block size greater than the
  smallest key block size used in a table, checking that table
  failed with bogus errors. The table was marked corrupt. This
  affected myisamchk and the server.
  
  The problem was that the default key block size was used at
  some places where sizes less or equal to the block size of the
  index in check was required.
  
  We do now use the key block size of the particular index
  when checking.
[18 Oct 2006 10:04] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/13848

ChangeSet@1.2348, 2006-10-18 12:03:44+02:00, istruewing@chilla.local +3 -0
  Bug#22119 - Changing MI_KEY_BLOCK_LENGTH makes a wrong myisamchk
  
  Not to be pushed. It still contains GCov injections.
  
  When compiling with a default key block size greater than the
  smallest key block size used in a table, checking that table
  failed with bogus errors. The table was marked corrupt. This
  affected myisamchk and the server.
  
  The problem was that the default key block size was used at
  some places where sizes less or equal to the block size of the
  index in check was required.
  
  We do now use the key block size of the particular index
  when checking.
[18 Oct 2006 13:24] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/13869

ChangeSet@1.2348, 2006-10-18 15:23:52+02:00, istruewing@chilla.local +3 -0
  Bug#22119 - Changing MI_KEY_BLOCK_LENGTH makes a wrong myisamchk
  
  Not to be pushed. It still contains GCov injections.
  
  When compiling with a default key block size greater than the
  smallest key block size used in a table, checking that table
  failed with bogus errors. The table was marked corrupt. This
  affected myisamchk and the server.
  
  The problem was that the default key block size was used at
  some places where sizes less or equal to the block size of the
  index in check was required.
  
  We do now use the key block size of the particular index
  when checking.
[18 Oct 2006 14:01] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/13871

ChangeSet@1.2348, 2006-10-18 16:00:37+02:00, istruewing@chilla.local +3 -0
  Bug#22119 - Changing MI_KEY_BLOCK_LENGTH makes a wrong myisamchk
  
  When compiling with a default key block size greater than the
  smallest key block size used in a table, checking that table
  failed with bogus errors. The table was marked corrupt. This
  affected myisamchk and the server.
  
  The problem was that the default key block size was used at
  some places where sizes less or equal to the block size of the
  index in check was required.
  
  We do now use the key block size of the particular index
  when checking.
[18 Oct 2006 14:50] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/13879

ChangeSet@1.2348, 2006-10-18 16:49:54+02:00, istruewing@chilla.local +3 -0
  Bug#22119 - Changing MI_KEY_BLOCK_LENGTH makes a wrong myisamchk
  
  When compiling with a default key block size greater than the
  smallest key block size used in a table, checking that table
  failed with bogus errors. The table was marked corrupt. This
  affected myisamchk and the server.
  
  The problem was that the default key block size was used at
  some places where sizes less or equal to the block size of the
  index in check was required.
  
  We do now use the key block size of the particular index
  when checking.
[18 Oct 2006 15:08] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/13880

ChangeSet@1.2348, 2006-10-18 17:08:31+02:00, istruewing@chilla.local +3 -0
  Bug#22119 - Changing MI_KEY_BLOCK_LENGTH makes a wrong myisamchk
  
  When compiling with a default key block size greater than the
  smallest key block size used in a table, checking that table
  failed with bogus errors. The table was marked corrupt. This
  affected myisamchk and the server.
  
  The problem was that the default key block size was used at
  some places where sizes less or equal to the block size of the
  index in check was required.
  
  We do now use the key block size of the particular index
  when checking.
[18 Oct 2006 16:40] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/13895

ChangeSet@1.2348, 2006-10-18 18:39:42+02:00, istruewing@chilla.local +3 -0
  Bug#22119 - Changing MI_KEY_BLOCK_LENGTH makes a wrong myisamchk
  
  When compiling with a default key block size greater than the
  smallest key block size used in a table, checking that table
  failed with bogus errors. The table was marked corrupt. This
  affected myisamchk and the server.
  
  The problem was that the default key block size was used at
  some places where sizes less or equal to the block size of the
  index in check was required.
  
  We do now use the key block size of the particular index
  when checking.
[25 Oct 2006 11:40] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/14341

ChangeSet@1.2534, 2006-10-25 13:39:40+02:00, istruewing@chilla.local +1 -0
  Bug#22119 - Changing MI_KEY_BLOCK_LENGTH makes a wrong myisamchk
  
  When compiling with a default key block size greater than the
  smallest key block size used in a table, checking that table
  failed with bogus errors. The table was marked corrupt. This
  affected myisamchk and the server.
  
  The problem was that the default key block size was used at
  some places where sizes less or equal to the block size of the
  index in check was required.
  
  We do now use the key block size of the particular index
  when checking.
  
  A test case is available for later versions only.
[25 Oct 2006 16:45] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/14378

ChangeSet@1.2320, 2006-10-25 17:23:32+02:00, istruewing@chilla.local +2 -0
  Bug#22119 - Changing MI_KEY_BLOCK_LENGTH makes a wrong myisamchk
  
  Added the promised test case.
[26 Oct 2006 12:13] Ingo Strüwing
Queued to 5.1-engines, 5.0-engines, and 4.1-engines.
[27 Dec 2006 0:15] Antony Curtis
Pushed to 5.1.15-beta repository
[27 Dec 2006 2:26] Antony Curtis
Pushed to 5.0.34 repository
[27 Dec 2006 10:01] Antony Curtis
Pushed to 4.1.23 repository
[23 Jan 2007 20:34] Paul Dubois
Noted in 4.1.23, 5.0.34, 5.1.15 changelogs.

Changing the value of MI_KEY_BLOCK_LENGTH in myisam.h and recompiling
MySQL resulted in a myisamchk that saw existing MyISAM tables as
corrupt.