Bug #25853 key_cache_block_size=4MB causes table corruptions + duplicate key errors
Submitted: 25 Jan 2007 16:20 Modified: 20 Jun 2007 1:01
Reporter: Edmond Hui Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S1 (Critical)
Version:5.0.27,5.0.40BK, 5.1.18BK OS:Linux (Linux Fedora Core 5)
Assigned to: Ingo Strüwing
Tags: corruption, key_cache_block_size, myisam

[25 Jan 2007 16:20] Edmond Hui
Description:
MySQL insert operation seem to corrupt table with error 1062 duplicate key sometimes (once every few days).

I am running many web applications (i.e. NucleusCMS and Gregarius) on a AMD64 X2 3800+ PC running Fedora Core 6 x86_64 version.

error often like this:

Failed to execute the SQL query

insert into item (cid, added, title, url, enclosure, description, author,
unread, pubdate, md5sum, guid) values (30, now(), '既然中国没有审查互联网,那么
唠点闲嗑', 'http://blog.cnblog.org/archives/2007/01/post_21.html', '', '关于
Tor,大家都比较熟悉了,现在为您介绍另外一款用于突破GFW(也许你需要首先配置好YF,
才能访问这个链接)的软件:Your Freedom,简称YF。 自称为“The Internet\'s most
sophisticated Proxy tunneling solution”的YF安装简便,支持二级代理,且可用于在局
域网用户间共享(根据现行服务条款,Free package不包含Relaying)。最重要的,在海缆
故障期间,欧洲通道优于北美通道,而YF的服务器多位于欧洲,因此YF的速度比Tor要快不
少。 下面开始正题: 1、在YF的网站上注册一个用户,并通过电子邮件验证; 2、下载并
安装YF,YF构建得很勤,现在的版本是20070112; 3、运行,使用向导建立最基本的配置,
重要的是选择Freedom server时要根据实际的测试结果选一个最佳的,而不一定是使用默认
的第一行; 4、配置你的客户端程序,socks端口是1080,http端口是8080。 后记,与伟大
的金盾工程相比,虽然SSH/VPN Tunneling技术让内容过滤变得困难,但是毕竟路由节点是
有限的,即使是戴了套套也无济于事,所以只是还没到Net Nanny发脾气的时候。同时,听
说已经有人民警察放出了psiphon服务器的诱饵,就等你上钩了。...', 'hanson', 1,
FROM_UNIXTIME(1168923240), 'c2cdfc07847c6ce9e6c98e8f6817f5ea', '')

Error 1062: Duplicate entry '565' for key 1

In this case, Gregarius (a PHP based RSS reader) trying to insert a new item to
a item table during feed update. I suspect this happened when multiple insert queries execute at the same time. The reason for saying this is that I notice Gregarius is configured to do 3 RSS feeds updating at the same time, and once in a while this corruption resulted. In my NucleusCMS blog case, the corrupted table is to store a validation key that generate during on each web page loading, which multiple access can happened at the same time.

I have since configured Gregarius to update one feed at a time, and so far has not seem the error (in least than 24 hours)

How to repeat:
unknown, the error happen in a seemingly random fashion
[25 Jan 2007 19:15] Valerii Kravchuk
Thank you for a problem report. Please, send the results of 

SHOW CREATE TABLE item\G
[25 Jan 2007 19:30] Edmond Hui
Hi,

Please see below:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 42585 to server version: 5.0.27

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SHOW CREATE TABLE item;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table               |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| item  | CREATE TABLE `item` (
  `id` bigint(16) NOT NULL auto_increment,
  `cid` bigint(11) NOT NULL default '0',
  `md5sum` varchar(32) default NULL,
  `guid` text,
  `added` datetime NOT NULL default '0000-00-00 00:00:00',
  `title` varchar(255) default NULL,
  `url` varchar(255) default NULL,
  `enclosure` varchar(255) default NULL,
  `description` text,
  `unread` tinyint(4) default '1',
  `pubdate` datetime default NULL,
  `author` varchar(255) default NULL,
  PRIMARY KEY  (`id`),
  KEY `url` (`url`),
  KEY `guid` (`guid`(10)),
  KEY `cid` (`cid`),
  KEY `author` (`author`)
) ENGINE=MyISAM AUTO_INCREMENT=6176 DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
[25 Jan 2007 19:35] Valerii Kravchuk
Please, send the results of 

SHOW TABLE STATUS LIKE 'item'\G

also.
[25 Jan 2007 19:36] Edmond Hui
Here's the result:

mysql> SHOW TABLE STATUS LIKE 'item';
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time  | Update_time         | Check_time          | Collation         | Checksum | Create_options | Comment |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| item | MyISAM |      10 | Dynamic    | 6092 |           1392 |     8485700 | 281474976710655 |       693248 |         0 |           6176 | 2007-01-22 14:00:31 | 2007-01-25 13:13:22 | 2007-01-24 14:45:25 | latin1_swedish_ci |     NULL |              |         |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
1 row in set (0.00 sec)
[26 Jan 2007 14:58] Edmond Hui
correction. This is a Fedora Core 6 system.....
[7 Feb 2007 15:05] Edmond Hui
Saw some new error just now:

Failed to execute the SQL query

select  i.title,  c.title, c.id, i.unread, i.url, i.enclosure, i.author, i.description, c.icon,  unix_timestamp(ifnull(i.pubdate,i.added)) as ts,  i.pubdate is not null as ispubdate, i.id, r.rating   from  item  i  left join  rating  r on (i.id = r.iid),  channels  c,  folders  f  where  i.cid = c.id and  f.id=c.parent and  not(c.mode & 8) and  not(i.unread & 8) and i.unread & 1 and  1=1  order by  ts desc, f.position asc, c.position asc , ts desc, i.id asc limit  0, 100

Error 1030: Got error 134 from storage engine

The server is under heavy disk load at the moment copying files from one disk to another.... wonder if this is related.
[12 Mar 2007 10:08] Valerii Kravchuk
Please, try to repeat with a newer version, 5.0.37, and inform about the results. In case of the same problems, please, send your my.cnf file content.
[4 Apr 2007 18:31] Edmond Hui
Hi,

I just installed the 5.0.37 from Fedora Core test and still seeing the corrupt table error.

My my.cnf is below:

[mysqld]
datadir=/home/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
key_cache_block_size=4M

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[4 Apr 2007 18:32] Edmond Hui
Actually, the actually error I am seeing is a bit different now:

Failed to execute the SQL query

select  i.title,  c.title, c.id, i.unread, i.url, i.enclosure, i.author, i.description, c.icon,  unix_timestamp(ifnull(i.pubdate,i.added)) as ts,  i.pubdate is not null as ispubdate, i.id, r.rating   from  item  i  left join  rating  r on (i.id = r.iid),  channels  c,  folders  f  where  i.cid = c.id and  f.id=c.parent and  not(c.mode & 8) and  not(i.unread & 8) and i.unread & 1 and  1=1  order by  ts desc, f.position asc, c.position asc , ts desc, i.id asc limit  0, 100

Error 145: Table './gregarius/item' is marked as crashed and should be repaired

It happened after insert.
[4 Apr 2007 18:53] Valerii Kravchuk
Please, upload your entire error log, gzipped. Have you tried to run CHECK/REPAIR for that table?
[4 Apr 2007 18:57] Edmond Hui
The log looks like this:
070404 08:26:00  mysqld started
070404  8:26:00  InnoDB: Started; log sequence number 0 43714
070404  8:26:00 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.0.37'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution
070404  9:59:11 [ERROR] /usr/libexec/mysqld: Incorrect key file for table './gregarius/item.MYI'; try to repair it
070404 10:13:53 [ERROR] /usr/libexec/mysqld: Table './gregarius/item' is marked as crashed and should be repaired
070404 10:13:53 [ERROR] /usr/libexec/mysqld: Table './gregarius/item' is marked as crashed and should be repaired
070404 10:14:06 [ERROR] /usr/libexec/mysqld: Table './gregarius/item' is marked as crashed and should be repaired
070404 10:14:06 [ERROR] /usr/libexec/mysqld: Table './gregarius/item' is marked as crashed and should be repaired
070404 10:14:06 [ERROR] /usr/libexec/mysqld: Table './gregarius/item' is marked as crashed and should be repaired
070404 10:14:06 [ERROR] /usr/libexec/mysqld: Table './gregarius/item' is marked as crashed and should be repaired
070404 10:14:06 [ERROR] /usr/libexec/mysqld: Table './gregarius/item' is marked as crashed and should be repaired
070404 10:14:06 [ERROR] /usr/libexec/mysqld: Table './gregarius/item' is marked as crashed and should be repaired
070404 10:14:06 [ERROR] /usr/libexec/mysqld: Table './gregarius/item' is marked as crashed and should be repaired
070404 10:14:06 [ERROR] /usr/libexec/mysqld: Table './gregarius/item' is marked as crashed and should be repaired
070404 10:14:06 [ERROR] /usr/libexec/mysqld: Table './gregarius/item' is marked as crashed and should be repaired
070404 10:14:06 [ERROR] /usr/libexec/mysqld: Table './gregarius/item' is marked as crashed and should be repaired
070404 11:05:06 [ERROR] /usr/libexec/mysqld: Incorrect key file for table './gregarius/item.MYI'; try to repair it
070404 12:18:23 [ERROR] /usr/libexec/mysqld: Table './gregarius/item' is marked as crashed and should be repaired
070404 12:18:23 [ERROR] /usr/libexec/mysqld: Table './gregarius/item' is marked as crashed and should be repaired
070404 12:50:35 [ERROR] /usr/libexec/mysqld: Table './gregarius/item' is marked as crashed and should be repaired
070404 12:50:35 [ERROR] /usr/libexec/mysqld: Table './gregarius/item' is marked as crashed and should be repaired
070404 14:23:13 [ERROR] /usr/libexec/mysqld: Table './gregarius/item' is marked as crashed and should be repaired
070404 14:23:13 [ERROR] /usr/libexec/mysqld: Table './gregarius/item' is marked as crashed and should be repaired
070404 14:33:10 [ERROR] /usr/libexec/mysqld: Table './gregarius/item' is marked as crashed and should be repaired
070404 14:33:10 [ERROR] /usr/libexec/mysqld: Table './gregarius/item' is marked as crashed and should be repaired
070404 14:33:10 [ERROR] /usr/libexec/mysqld: Table './gregarius/item' is marked as crashed and should be repaired
070404 14:33:10 [ERROR] /usr/libexec/mysqld: Table './gregarius/item' is marked as crashed and should be repaired
070404 14:33:10 [ERROR] /usr/libexec/mysqld: Table './gregarius/item' is marked as crashed and should be repaired
070404 14:33:10 [ERROR] /usr/libexec/mysqld: Table './gregarius/item' is marked as crashed and should be repaired
070404 14:33:10 [ERROR] /usr/libexec/mysqld: Table './gregarius/item' is marked as crashed and should be repaired
070404 14:33:10 [ERROR] /usr/libexec/mysqld: Table './gregarius/item' is marked as crashed and should be repaired
070404 14:33:10 [ERROR] /usr/libexec/mysqld: Table './gregarius/item' is marked as crashed and should be repaired
070404 14:33:10 [ERROR] /usr/libexec/mysqld: Table './gregarius/item' is marked as crashed and should be repaired

I tried to run table repair from phpMyAdmin and the problem is fixed afterward. But each time when I run the Gregarius update (which search RSS feeds for new posts and insert to item table, the problem happened again.
[4 Apr 2007 20:04] Shane Bester
does the problem go away if you comment out key_cache_block_size=4M in my.cnf ?
[5 Apr 2007 16:01] Edmond Hui
I will keep an eye on it over the weekend and report back
[10 Apr 2007 13:54] Edmond Hui
After removing the line key_cache_block_size=4M in my.cnf, I tested for 5 days and no longer seeing the problem.... I guess that's the problem?
[10 Apr 2007 13:58] Shane Bester
thanks for testing it Edmond.  We should run some tests to try repeat the problem and upload a testcase hopefully soon.
[10 Apr 2007 14:24] Shane Bester
start mysqld with --key_cache_block_size=4M, import this file, run check table t1

Attachment: bug25853_testcase.sql (application/octet-stream, text), 86.22 KiB.

[10 Apr 2007 14:26] Shane Bester
verified on 5.0BK using attached bug25853_testcase.sql

mysql> check table t1 extended;
+---------+-------+----------+----------------------------+
| Table   | Op    | Msg_type | Msg_text                   |
+---------+-------+----------+----------------------------+
| test.t1 | check | warning  | Table is marked as crashed |
| test.t1 | check | error    | Found 472 keys of 552      |
| test.t1 | check | error    | Corrupt                    |
+---------+-------+----------+----------------------------+
3 rows in set (0.00 sec)
[10 Apr 2007 14:30] Shane Bester
5.1.18BK has same problem with key_cache_block_size, but needs a new testcase to prove it.  I'll upload that later as it's slightly longer amounts of random values.
[11 Apr 2007 13:44] Edmond Hui
Quote:"start mysqld with --key_cache_block_size=4M, import this file, run check table t1"

Can I do this from the my.cnf file?

I am a bit busy right now, but will try to get it done later this week.
[13 Apr 2007 12:06] Shane Bester
Edmond, you don't have to do anything.. that note was only for the developers who will be fixing the bug, to let them know how to repeat it.

key_cache_block_size appears to be limited to 16K anyway.
[13 Apr 2007 15:28] Ingo Strüwing
Hi, I am pretty sure that my fix for Bug#17332 does also fix this one. As soon as that one is approved and pushed, I'll test it on this bug too. Regards, Ingo
[17 May 2007 8:23] Ingo Strüwing
I was in error. The fix for Bug#17332 (changing key_buffer_size on a running server can crash under load) concentrates on concurrency issues only.
This case is more like Bug#23068 (key_cache_block_size is not set or displayes correctly) and Bug#28478 (Improper key_cache_block_size corrupts MyISAM tables).

Due to Bug#23068, --key_cache_block_size=4M is converted into 15872. (Did you ever SHOW VARIABLES LIKE 'key_cache_block_size'?)

Due to Bug#28478 a resulting block size that is not a power of 2 corrupts MyISAM tables.

When working on Bug#17332, I noticed that a key_cache_block_size != 1024 can corrupt MyISAM tables under heavy load.

Hence this bug is fixed when the other three bugs are fixed. Note that 17332 will first go into 5.1 only. After a probationary period it will go into 5.0 too.
[17 May 2007 8:24] Ingo Strüwing
I will keep this "in progress" until the other bug fixes are in the main repositories, if only in 5.1.
[21 May 2007 10:08] Ingo Strüwing
Setting this to "patch pending" to document that there exist patches which fix this bug. Please see the patches for Bug#23068 _and_ Bug#28478.
[31 May 2007 18:09] Ingo Strüwing
The patches for Bug#23068 _and_ Bug#28478 are approved.
[4 Jun 2007 10:16] Ingo Strüwing
Queued to 5.1-engines and 5.0-engines.
[18 Jun 2007 12:02] Ingo Strüwing
Pushed into 5.1.20-beta and 5.0.44.
[20 Jun 2007 1:01] Paul Dubois
Noted in 5.0.46, 5.1.20 changelogs.

The server deducted some bytes from the key_cache_block_size option
value and reduced it to the next lower 512 byte boundary. The
resulting block size was not a power of two. Setting the 
key_cache_block_size system variable to a value that is not a power
of two resulted in MyISAM table corruption.

(This description applies to Bug#23068, Bug#25853, Bug#28478)