| Bug #80503 | optimize MyISAM leads to corrupted index | ||
|---|---|---|---|
| Submitted: | 25 Feb 2016 5:29 | Modified: | 25 Feb 2016 5:39 |
| Reporter: | Fungo Wang (OCA) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: MyISAM storage engine | Severity: | S3 (Non-critical) |
| Version: | 5.6.29 | OS: | Red Hat |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | myisam, Optimize | ||
[25 Feb 2016 5:39]
MySQL Verification Team
Verified as described. Testcase:
---
drop table if exists t;
create table t (k int,c char(60), key (k))engine=myisam default charset=utf8 row_format=dynamic;
set myisam_sort_buffer_size=4096;
insert into t(k,c) values(1,repeat('a',60)),(1,repeat('a',60)),
(1,repeat('a',60)),(1,repeat('a',60)),(1,repeat('a',60));
insert into t(k,c) select t.k,t.c from t,t a,t b,t c,t d;
select count(*) from t;
check table t extended;
delete from t limit 10;
optimize table t;
show warnings;
check table t extended;
----
[25 Feb 2016 5:39]
MySQL Verification Team
mysql> optimize table t; +--------+----------+----------+--------------------------------------+ | Table | Op | Msg_type | Msg_text | +--------+----------+----------+--------------------------------------+ | test.t | optimize | error | myisam_sort_buffer_size is too small | | test.t | optimize | status | OK | +--------+----------+----------+--------------------------------------+ 2 rows in set (0.01 sec) mysql> show warnings; Empty set (0.00 sec) mysql> check table t extended; +--------+-------+----------+----------------------------------------------+ | Table | Op | Msg_type | Msg_text | +--------+-------+----------+----------------------------------------------+ | test.t | check | error | Record at: 720 Can't find key for index: 1 | | test.t | check | error | Corrupt | +--------+-------+----------+----------------------------------------------+ 2 rows in set (0.00 sec)
[25 Feb 2016 5:41]
MySQL Verification Team
in mysql error log: [Warning] Warning: Optimize table got errno 0 on test.t, retrying [ERROR] Got an error from thread_id=2, ..\..\..\mysql-5.6.29\storage\myisam\ha_myisam.cc:907 [ERROR] MySQL thread id 2, OS thread handle 0xb54, query id 212 localhost ::1 root Checking table
Description: This is the count(*) of my MyISAM table t1, mysql> select count(*) from t1; +----------+ | count(*) | +----------+ | 10230 | +----------+ 1 row in set (0.00 sec) When optimize this table , I got bellow error message : mysql> optimize table t1; +---------+----------+----------+--------------------------------------+ | Table | Op | Msg_type | Msg_text | +---------+----------+----------+--------------------------------------+ | test.t1 | optimize | error | myisam_sort_buffer_size is too small | | test.t1 | optimize | status | OK | +---------+----------+----------+--------------------------------------+ 2 rows in set (0.00 sec) and the records disappeared: mysql> select count(*) from t1; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) How to repeat: CREATE TABLE `t1` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `k` int(10) unsigned NOT NULL DEFAULT '0', `c` char(120) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `k_1` (`k`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC; let $count=10; while ($count) { insert into t1(k,c,pad) values(1, '52701095647-01113022705-64449767074-80289566501-46655716003-30998054747-86581496979-04794021240-24279779991-69339916664', '42310807833-74153219204-94828745440-32456823465-40921171006'); dec $count; } insert into t1(k,c,pad) select k,c,pad from t1; insert into t1(k,c,pad) select k,c,pad from t1; insert into t1(k,c,pad) select k,c,pad from t1; insert into t1(k,c,pad) select k,c,pad from t1; insert into t1(k,c,pad) select k,c,pad from t1; insert into t1(k,c,pad) select k,c,pad from t1; insert into t1(k,c,pad) select k,c,pad from t1; insert into t1(k,c,pad) select k,c,pad from t1; insert into t1(k,c,pad) select k,c,pad from t1; insert into t1(k,c,pad) select k,c,pad from t1; select count(*) from t1; delete from t1 limit 10; set myisam_sort_buffer_size = 4096; optimize table t1; select count(*) from t1; drop table t1; Suggested fix: The MYI file is corrupted during optimize process. After some dig into the source code, the optimize include 2 round repairs, both failed, and the MYI filed is corrupted at the 2nd. For the 1st round, myisam_sort_buffer_size is too small and cause failure at _create_index_by_sort(), and indexes are dropped during this round by calling mi_drop_all_indexes(). For the 2nd round, mi_sort_index() create an new MYI file, and use this new one to replace the old, because all indexes were dropped at 1st round, the new MYI is empty. The workaround is repair the table by executing "repair table xxx" to rebuild index.