Bug #29693 Setting key_cache_block_size=4096 corrupts key file
Submitted: 10 Jul 2007 17:08 Modified: 13 Jul 2007 22:52
Reporter: James Blair Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S2 (Serious)
Version:5.0.41 OS:Linux
Assigned to: CPU Architecture:Any
Tags: corruption, key_cache_block_size

[10 Jul 2007 17:08] James Blair
Description:
Tested with the following:

Server version: 5.0.38-Ubuntu_0ubuntu1 Ubuntu 7.04 distribution
Server version: 5.0.30-max MySQL Community Edition - Experimental (GPL)
  (on RHEL5)

After setting key_cache_block_size=4096, inserting a single row into a table with a certain definition causes:
  Error |  126 | Incorrect key file for table './test/rejects.MYI'; try to repair it

  Incorrect key file for table './test/rejects.MYI'; try to repair it

The corruption is repeatable, even after a repair table.

An otherwise standard my.cnf is sufficient:

[client]
port		= 3306
socket		= /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket		= /var/run/mysqld/mysqld.sock

[mysqld]
user		= mysql
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
port		= 3306
basedir		= /usr
datadir		= /var/lib/mysql
tmpdir		= /tmp
language	= /usr/share/mysql/english
skip-external-locking
bind-address		= 127.0.0.1

key_cache_block_size	= 4096

How to repeat:
drop table if exists rejects;

CREATE TABLE `rejects` (
  `server` varchar(32) NOT NULL default '',
  `message_id` varchar(16) binary default NULL,
  `timestamp` bigint(20) NOT NULL default '0',
  `host_addr` varchar(15) NOT NULL default '',
  `host_rdns` varchar(255) NOT NULL default '',
  `host_ident` varchar(255) default NULL,
  `host_helo` varchar(255) default NULL,
  `mailfrom` varchar(255) default NULL,
  `rcpt` varchar(255) default NULL,
  `errmsg` varchar(255) NOT NULL default '',
  UNIQUE KEY `rejects_unique` (`server`,`timestamp`,`host_addr`,`errmsg`),
  KEY `message_id` (`message_id`),
  KEY `server` (`server`),
  KEY `timestamp` (`timestamp`),
  KEY `host_addr` (`host_addr`),
  KEY `mailfrom` (`mailfrom`),
  KEY `rcpt` (`rcpt`),
  KEY `host_dns` (`host_rdns`)
) TYPE=MyISAM;

insert into rejects (server) values ('foo');

show warnings;
[11 Jul 2007 11:14] Sveta Smirnova
Thank you for the report.

We can not repeat described behaviour on our side.

Additionally version 5.0.38 is old. Pleas upgrade to current version 5.0.44 of 5.0.41, try with it and if you are able to repeat error provide output of SHOW TABLE STATUS LIKE 'rejects'
[11 Jul 2007 16:12] James Blair
I installed the RPMS from:

http://dev.mysql.com/downloads/mysql/5.0.html#linux-rhel5-x86-64bit-rpms

on an EMT64 machine running RHEL5.  The client now displays:

Server version: 5.0.41-community MySQL Community Edition (GPL)

I executed the procedure I described previously, and got this result:

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Error
   Code: 126
Message: Incorrect key file for table './test/rejects.MYI'; try to repair it
1 row in set (0.00 sec)

mysql> SHOW TABLE STATUS LIKE
    -> 'rejects'\G
*************************** 1. row ***************************
           Name: rejects
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 281474976710655
   Index_length: 11264
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2007-07-11 09:09:40
    Update_time: 2007-07-11 09:09:40
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)
[11 Jul 2007 16:18] James Blair
my.cnf:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
key_cache_block_size    = 4096

[mysql.server]
user=mysql

[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[13 Jul 2007 13:43] Sveta Smirnova
Thank you for the feedback.

I could not repeat described behaviour with indicated binaries on RHEL5. So I close the report as "Can't repeat". If you have new idea how to repeat the problem feel free to provide it and reopen the report.
[13 Jul 2007 22:52] James Blair
Did you set key_cache_block_size=4096?

I produced the problem on three versions of MySQL, on three different machines, under two different OS distributions.  I'm convinced it's repeatable.
[15 Jul 2007 20:01] MySQL Verification Team
I also cannot repeat corruption using 5.0.46BK.
I suspect this bug in a duplicate of one of the following:

bug #19079 (key_cache_block_size set to 4K corrupts indexes)
bug #25853 (key_cache_block_size=4MB causes table corruptions + duplicate key errors)
[15 Jul 2007 21:02] MySQL Verification Team
Just wanted to confirm that 5.0.44 still has this bug.  The latest source of 5.0.46 built from BitKeeper repositories shows that it's been fixed already.

So when 5.0.46 is officially released, it will have the fix.