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:
None 
Category:MySQL Server: MyISAM storage engine Severity:S2 (Serious)
Version:5.0.19,5.0-bk,4.1-bk OS:Microsoft 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
Description:
myisam_block_size set to 4K causes the indexes to get corrupted on the very first INSERT attempt.
I exerienced this problem with a test database after having recreated the tables.

How to repeat:
I did the following to reproduce it.

MySQL freshly installed by copying the files, including the MYSQL system database (i.e. I did not use the installer)

- change myisam_block_size and restart the service
- create a test db and a table like the following
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)
);

- Insert a record in the above table.

MySQL fails the insert and reports that the indexes are corrupted. Reparing it does not solve the problem, that is, they get corrupted on the next insert attempt.
[13 Apr 2006 21:07] Shane Bester
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] Shane Bester
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] Shane Bester
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] Shane Bester
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)