| 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: | |
| 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 | CPU Architecture: | Any |
| Tags: | corruption, key_cache_block_size, myisam | ||
[25 Jan 2007 16:20]
Edmond Hui
[25 Jan 2007 19:15]
Valeriy 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]
Valeriy 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]
Valeriy 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]
Valeriy 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]
MySQL Verification Team
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]
MySQL Verification Team
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]
MySQL Verification Team
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]
MySQL Verification Team
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]
MySQL Verification Team
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]
MySQL Verification Team
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.
[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)
