Bug #43932 myisam index corruption with large index and large key_buffer_size
Submitted: 28 Mar 2009 18:04 Modified: 15 May 2009 23:55
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S1 (Critical)
Version:5.1.32 OS:Windows (64-bit)
Assigned to: Vladislav Vaintroub CPU Architecture:Any
Tags: corruption

[28 Mar 2009 18:04] Shane Bester
Description:
large key buffer on windows causes myisam index file corruption after a few operations.

mysql> create table `t1`(
    -> `id` bigint not null auto_increment primary key,
    -> `a` varchar(1000),
    -> key(`a`),key(`a`),key(`a`),key(`a`),key(`a`),key(`a`),
    -> key(`a`),key(`a`),key(`a`),key(`a`),key(`a`),key(`a`),
    -> key(`a`),key(`a`),key(`a`),key(`a`)
    -> ) engine=myisam delay_key_write=1 pack_keys=0 charset=latin1;
Query OK, 0 rows affected (0.05 sec)

mysql>
mysql> alter table `t1` disable keys;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into `t1` set `a`=repeat(rand(),20);
Query OK, 1 row affected (0.08 sec)

mysql> insert into `t1` set `a`=repeat(rand(),20);
Query OK, 1 row affected (0.00 sec)

.... <cut>

mysql> insert into `t1`(`a`) select `a` from `t1`;
Query OK, 6291456 rows affected (5 min 25.16 sec)
Records: 6291456  Duplicates: 0  Warnings: 0

mysql>
mysql> alter table `t1` enable keys;
Query OK, 0 rows affected (50 min 38.38 sec)

mysql> load index into cache t1;
+---------+--------------+----------+----------+
| Table   | Op           | Msg_type | Msg_text |
+---------+--------------+----------+----------+
| test.t1 | preload_keys | status   | OK       |
+---------+--------------+----------+----------+
1 row in set (46 min 9.08 sec)

mysql> delete quick from t1 order by rand() limit 10000000000;
ERROR 126 (HY000): Incorrect key file for table '.\test\t1.MYI'; try to repair it
mysql> check table t1;
+---------+-------+----------+---------------------------------+
| Table   | Op    | Msg_type | Msg_text                        |
+---------+-------+----------+---------------------------------+
| test.t1 | check | warning  | Table is marked as crashed      |
| test.t1 | check | error    | Found 12582911 keys of 12582912 |
| test.t1 | check | error    | Corrupt                         |
+---------+-------+----------+---------------------------------+
3 rows in set (1 min 3.66 sec)

How to repeat:
start server like this:

mysqld --key_buffer_size=4200M --key_cache_block_size=16k --delay-key_write=all --myisam_max_sort_file_size=900G --myisam_sort_buffer_size=300M 

Run the following SQL:

drop table if exists `t1`;
create table `t1`(
`id` bigint not null auto_increment primary key,
`a` varchar(1000),
key(`a`),key(`a`),key(`a`),key(`a`),key(`a`),key(`a`),
key(`a`),key(`a`),key(`a`),key(`a`),key(`a`),key(`a`),
key(`a`),key(`a`),key(`a`),key(`a`)
) engine=myisam delay_key_write=1 pack_keys=0 charset=latin1;
alter table `t1` disable keys;
insert into `t1` set `a`=repeat(rand(),20);
insert into `t1` set `a`=repeat(rand(),20);
insert into `t1` set `a`=repeat(rand(),20);
insert into `t1`(`a`) select rand() from `t1`;
insert into `t1`(`a`) select rand() from `t1`;
insert into `t1`(`a`) select rand() from `t1`;
insert into `t1`(`a`) select rand() from `t1`;
insert into `t1`(`a`) select rand() from `t1`;
insert into `t1`(`a`) select `a` from `t1`;
insert into `t1`(`a`) select `a` from `t1`;
insert into `t1`(`a`) select `a` from `t1`;
insert into `t1`(`a`) select `a` from `t1`;
insert into `t1`(`a`) select `a` from `t1`;
insert into `t1`(`a`) select `a` from `t1`;
insert into `t1`(`a`) select `a` from `t1`;
insert into `t1`(`a`) select `a` from `t1`;
insert into `t1`(`a`) select `a` from `t1`;
insert into `t1`(`a`) select `a` from `t1`;
insert into `t1`(`a`) select `a` from `t1`;
insert into `t1`(`a`) select `a` from `t1`;
insert into `t1`(`a`) select `a` from `t1`;
insert into `t1`(`a`) select `a` from `t1`;
insert into `t1`(`a`) select `a` from `t1`;
insert into `t1`(`a`) select `a` from `t1`;
insert into `t1`(`a`) select `a` from `t1`;
alter table `t1` enable keys;
load index into cache t1;
delete quick from t1 order by rand() limit 10000000000;

#The last delete will error out with "incorrect key file for..."
[28 Mar 2009 19:20] Tonci Grgin
Shane, would you mind reviewing my results and, if necessary, change synopsis.
[29 Mar 2009 9:21] MySQL Verification Team
i suspect when number of pages goes higher than 2^32 this bug is exposed
[30 Mar 2009 19:03] Tonci Grgin
...\mysql-5.1.25-rc-winx64\bin>mysqld --key_buffer_size=4200M --key_cache_block_size=16k --delay-key_write=all

mysql> insert into `t1`(`a`) select `a` from `t1`;
Query OK, 6291456 rows affected (4 min 47.95 sec)
Records: 6291456  Duplicates: 0  Warnings: 0

mysql> alter table `t1` enable keys;
Query OK, 0 rows affected (2 hours 4 min 28.18 sec)

mysql> check table t1;
+---------+-------+----------+----------+
| Table   | Op    | Msg_type | Msg_text |
+---------+-------+----------+----------+
| test.t1 | check | status   | OK       |
+---------+-------+----------+----------+
1 row in set (1 hour 45 min 32.10 sec)

So 5.1.25 is not affected although it's aprox. 3 times slower.
[31 Mar 2009 0:18] Tonci Grgin
\mysql-5.1.30-winx64\bin>mysqld --key_buffer_size=4200M --key_cache_block_size=16k --delay-key_write=all

...
mysql> insert into `t1`(`a`) select `a` from `t1`;
Query OK, 6291456 rows affected (4 min 24.67 sec)
Records: 6291456  Duplicates: 0  Warnings: 0

mysql> alter table `t1` enable keys;
Query OK, 0 rows affected (2 hours 35.58 sec)

mysql> check table t1;
+---------+-------+----------+----------+
| Table   | Op    | Msg_type | Msg_text |
+---------+-------+----------+----------+
| test.t1 | check | status   | OK       |
+---------+-------+----------+----------+
1 row in set (1 hour 46 min 15.03 sec)

mysql>

It appears problem started after 5.1.30...
[6 Apr 2009 6:23] MySQL Verification Team
Tonci, this bug is introduced since 5.1.31 because thats first version that has >4G key_buffer_size for windows.
[14 Apr 2009 9:38] Vladislav Vaintroub
Tonci, basically if you got a "check table t1" error once, would you mind repeat "check table t1" on the same table, without shutdown ?  It spits different results each time, so it looks highly suspect.

What I have seen and what looks quite strange is lack of determinism in following 3 test runs.

a) load, enable keys, check table(ok), load keys (error)
b) load, enable keys, check table(ok), shutdown, start, check table (error)
c) load, enable keys, check table(error in key file), check table (error in key file in with different offset), check table( error in key file with yet another different offset error). Check table with myisamchk - no error.
[28 Apr 2009 18:59] 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/72947

2875 Vladislav Vaintroub	2009-04-28
      Bug#43932 myisam index corruption with large index and large 
      key_buffer_size.
      
      The cause of corruption was number overflow when multiplying 
      two ulong values, number of used keycache blocks with size
      of a single block. The result of multiplication exceeded ulong 
      range (4G) and this lead to incorrectly calculated  buffer offset
      in the key cache.
      
      The fix is to use size_t for multiplication result.
      
      This patch also fixes pointless cast in safemalloc 
      (size of allocated block to uint), that creates lot of false
      alarm warnings when using big keycache (> 4GB) in debug mode.
[29 Apr 2009 11:52] 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/73012

2875 Vladislav Vaintroub	2009-04-29
      Bug#43932 myisam index corruption with large index and large 
      key_buffer_size.
      
      The cause of corruption was number overflow when multiplying 
      two ulong values, number of used keycache blocks with size
      of a single block. The result of multiplication exceeded ulong 
      range (4G) and this lead to incorrectly calculated  buffer offset
      in the key cache.
      
      The fix is to use size_t for multiplication result.
      
      This patch also fixes pointless cast in safemalloc 
      (size of allocated block to uint), that creates lot of false
      alarm warnings when using big keycache (> 4GB) in debug mode.
[29 Apr 2009 12:26] 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/73019

3238 Vladislav Vaintroub	2009-04-29 [merge]
      merge fix for Bug#43932 from 5.1-bugteam
[29 Apr 2009 12:38] Vladislav Vaintroub
pushed into 5.1-bugteam, 6.0-bugteam
[5 May 2009 19:40] Bugs System
Pushed into 5.1.35 (revid:davi.arnaut@sun.com-20090505190206-9xmh7dlc6kom8exp) (version source revid:davi.arnaut@sun.com-20090505190206-9xmh7dlc6kom8exp) (merge vers: 5.1.35) (pib:6)
[6 May 2009 14:12] Bugs System
Pushed into 6.0.12-alpha (revid:svoj@sun.com-20090506125450-yokcmvqf2g7jhujq) (version source revid:mhansson@mysql.com-20090429185530-u4nuo9ijnf0oaxh3) (merge vers: 6.0.11-alpha) (pib:6)
[15 May 2009 23:55] Paul DuBois
Noted in 5.1.35, 6.0.12 changelogs.

On 64-bit systems, a key_buffer_size value larger than 4GB could
couse MyISAM index corruption.
[15 Jun 2009 8:27] Bugs System
Pushed into 5.1.35-ndb-6.3.26 (revid:jonas@mysql.com-20090615074202-0r5r2jmi83tww6sf) (version source revid:jonas@mysql.com-20090615070837-9pccutgc7repvb4d) (merge vers: 5.1.35-ndb-6.3.26) (pib:6)
[15 Jun 2009 9:06] Bugs System
Pushed into 5.1.35-ndb-7.0.7 (revid:jonas@mysql.com-20090615074335-9hcltksp5cu5fucn) (version source revid:jonas@mysql.com-20090615072714-rmfkvrbbipd9r32c) (merge vers: 5.1.35-ndb-7.0.7) (pib:6)
[15 Jun 2009 9:47] Bugs System
Pushed into 5.1.35-ndb-6.2.19 (revid:jonas@mysql.com-20090615061520-sq7ds4yw299ggugm) (version source revid:jonas@mysql.com-20090615054654-ebgpz7elwu1xj36j) (merge vers: 5.1.35-ndb-6.2.19) (pib:6)
[10 Jul 2009 23:19] Bugs System
Pushed into 5.1.37 (revid:build@mysql.com-20090710231213-9guqdu0avc0uwdkp) (version source revid:build@mysql.com-20090710231213-9guqdu0avc0uwdkp) (merge vers: 5.1.37) (pib:11)
[23 Jul 2009 10:24] Bugs System
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090723102221-ps4uaphwbxzj8p0q) (version source revid:joerg@mysql.com-20090721145751-rqqnhv0kage18wfi) (merge vers: 5.4.4-alpha) (pib:11)
[26 Aug 2009 13:46] Bugs System
Pushed into 5.1.37-ndb-7.0.8 (revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)
[26 Aug 2009 13:46] Bugs System
Pushed into 5.1.37-ndb-6.3.27 (revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (version source revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (merge vers: 5.1.37-ndb-6.3.27) (pib:11)
[26 Aug 2009 13:48] Bugs System
Pushed into 5.1.37-ndb-6.2.19 (revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (version source revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (merge vers: 5.1.37-ndb-6.2.19) (pib:11)
[27 Aug 2009 16:33] Bugs System
Pushed into 5.1.35-ndb-7.1.0 (revid:magnus.blaudd@sun.com-20090827163030-6o3kk6r2oua159hr) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)