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: | |
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
[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.