| Bug #19079 | key_cache_block_size set to 4K corrupts indexes | ||
|---|---|---|---|
| Submitted: | 13 Apr 2006 17:31 | Modified: | 2 May 2006 1:46 |
| Reporter: | David Lombardi | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: MyISAM storage engine | Severity: | S2 (Serious) |
| Version: | 5.0.19,5.0-bk,4.1-bk | OS: | Windows (Windows XP & 2K3, Linux) |
| Assigned to: | Igor Babaev | CPU Architecture: | Any |
| Tags: | corruption, key_cache_block_size, myisam | ||
[13 Apr 2006 17:31]
David Lombardi
[13 Apr 2006 21:07]
MySQL Verification Team
Hi, it didn't corrupt my 5.0.19 when started like mysqld-nt.exe --myisam_block_size=4096 mysql> INSERT INTO session(session_id,pat_reco_id,bed_id,unit_id,state) VALUE (1,2,3,4,5); Query OK, 1 row affected (0.00 sec) mysql> CHECK TABLE session; +--------------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------------+-------+----------+----------+ | test.session | check | status | OK | +--------------+-------+----------+----------+ 1 row in set (0.02 sec) Please give your my.ini file, and the exact INSERT state you used, as well as the exist error message. Thanks.
[14 Apr 2006 6:02]
David Lombardi
My.ini that generates corruption (uncomment myisam_block_size key)
Attachment: my.ini (application/octet-stream, text), 9.89 KiB.
[14 Apr 2006 7:10]
David Lombardi
Hi Shane, I just uploaded the my.ini used. Most of the lines where generated running the setup wizard, twicked to fit our needs. It all worked with no problems. The INSERT statement that fails is similar to the following (no primary key specified) INSERT INTO session(pat_reco_id,bed_id,unit_id,state) VALUE (2,3,4,5) Starting from scratch, at the very first attempt I get "Incorrect key file for table .\<dbname>\session.myi; try to repair it" Error 126 Check table returns ------------------------------------------------------------------------------------------------------------------------ Status checked for table scdb_master.session. Table is marked as crashed ------------------------------------------------------------------------------------------------------------------------ Status checked for table scdb_master.session. Found key at page 4096 that points to record outside datafile ------------------------------------------------------------------------------------------------------------------------ Status checked for table scdb_master.session. Corrupt Re-issuing, without repairing first, the INSERT statement cause the following error "Duplicate entry "1" for key 1" Error 1062. Hence, my first post was incorrect (that is I mixed causes and effects). Repair is always successful. If you cannot still reproduce it, I'll send you detailed instructions and scripts for building our system. Thanks David
[14 Apr 2006 7:57]
MySQL Verification Team
On 5.0.19-nt I get: mysql> INSERT INTO session(pat_reco_id,bed_id,unit_id,state) VALUE (2,3,4,5); ERROR 126: Incorrect key file for table '.\test\session.MYI'; try to repair it. I'm testing on the latest source on windows/linux and will inform of those results soon. The problem is caused by key_cache_block_size setting. Please comment it out for now. Also, use myisam_repair_threads=1.
[14 Apr 2006 8:06]
MySQL Verification Team
Verified on 5.0-bk on linux.
Start server:
./bin/mysqld_safe --key_cache_block_size=65535 --myisam_block_size=4096
Your MySQL connection id is 1 to server version: 5.0.21-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> DROP TABLE IF EXISTS session;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> CREATE TABLE session (
-> session_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
-> pat_reco_id SMALLINT NOT NULL DEFAULT '-1',
-> bed_id TINYINT UNSIGNED NOT NULL DEFAULT '0',
-> unit_id TINYINT UNSIGNED NOT NULL DEFAULT '0',
-> state TINYINT UNSIGNED NOT NULL,
-> PRIMARY KEY (session_id),
-> INDEX bed_unit_state_idx (bed_id,unit_id,state),
-> INDEX pat_idx (pat_reco_id)
-> );
Query OK, 0 rows affected (0.05 sec)
mysql>
mysql> INSERT INTO session(pat_reco_id,bed_id,unit_id,state) VALUE (2,3,4,5);
Query OK, 1 row affected (0.13 sec)
mysql> check table session;
+--------------+-------+----------+---------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+--------------+-------+----------+---------------------------------------------------------------+
| test.session | check | warning | Table is marked as crashed |
| test.session | check | error | Found key at page 4096 that points to record outside datafile |
| test.session | check | error | Corrupt |
+--------------+-------+----------+---------------------------------------------------------------+
3 rows in set (0.01 sec)
mysql> select * from session;
ERROR 145: Table './test/session' is marked as crashed and should be repaired
mysql>
[18 Apr 2006 13:14]
Sergey Vojtovich
This problem is not about myisam_block_size, but rather about key_cache_block_size: SET GLOBAL key_cache_block_size=1600; CREATE TABLE t1(a INT, b INT, c INT, INDEX(a), INDEX(b), INDEX(c)); INSERT INTO t1 VALUES(1,2,3); CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check warning Table is marked as crashed test.t1 check error Can't read indexpage from filepos: 1024 test.t1 check error Corrupt
[18 Apr 2006 14:22]
David Lombardi
Hi Sergey, I seem to remember that keeping myisam_block_size to the default (commenting the key) and changing key_cache_block_size did not cause any corruption to the DB. Let me know if you want me to do some tests. Thanks David
[18 Apr 2006 14:32]
Sergey Vojtovich
David, index corruption requires certain criteria. I believe setting myisam_block_size and/or key_cache_block_size to certain values could result in index crash. After looking into this problem closer I can say that it is rather about key_cache_block_size rather than myisam_block_size. This problem occurs inside keycache code. Also I have even smaller testcase now: SET GLOBAL key_cache_block_size=1600; CREATE TABLE t1(a INT, INDEX(a)); INSERT INTO t1 VALUES(1); CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check warning Table is marked as crashed test.t1 check error Can't read indexpage from filepos: 1024 test.t1 check error Corrupt
[19 Apr 2006 3:57]
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/5113
[20 Apr 2006 19:35]
MySQL Verification Team
Hi Igor, I just cloned 4.1 and both the above testcases come back with a clean CHECK TABLE: OK message.. Looks like it works. mysql> CREATE TABLE t1(a INT, b INT, c INT, INDEX(a), INDEX(b), INDEX(c)); Query OK, 0 rows affected (0.05 sec) mysql> INSERT INTO t1 VALUES(1,2,3); Query OK, 1 row affected (0.00 sec) mysql> CHECK TABLE t1; +---------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------+-------+----------+----------+ | test.t1 | check | status | OK | +---------+-------+----------+----------+ 1 row in set (0.00 sec) mysql>
[30 Apr 2006 5:37]
Igor Babaev
ChangeSet 1.2464 06/04/18 20:57:31 igor@rurik.mysql.com +3 -0 Fixed bug #19079. The bug caused a reported index corruption in the cases when key_cache_block_size was not a multiple of myisam_block_size, e.g. when key_cache_block_size=1536 while myisam_block_size=1024. This fix will appear in 4.1.19, 5.0.21 and 5.1.11
[2 May 2006 1:46]
Paul DuBois
Noted in 4.1.19, 5.0.21, 5.1.10 changelogs. Index corruption could occur in cases when <literal>key_cache_block_size</literal> was not a multiple of <literal>myisam_block_size</literal> (for example, with <literal>key_cache_block_size=1536</literal> and <literal>myisam_block_size=1024</literal>). (Bug #19079)
