| 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: | |
| 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 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)
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..."