MySQL Bugs: #23838: Incorrect key file for table

Bug #23838	Incorrect key file for table
Submitted:	1 Nov 2006 13:39	Modified:	28 Nov 2006 11:13
Reporter:	Taras Mandryk	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.26	OS:	Novell NetWare (Netware 6.5)
Assigned to:		CPU Architecture:	Any
Tags:	Incorrect key file for table, Netware

Description:
[ERROR] sys:/mysql/bin/mysqld.nlm: Incorrect key file for table '.\naudit\log.MYI'; try to repair it

with large amount of INSERTs

How to repeat:
fluctuate

Hi!

What's the output of:
 show create table log;
 show table status like 'log';
 check table log extended;
 select version();

we are having this same issue on Linux - RHEL4 64bit

we believe it to be same problem as bug #22384 and bug #22460

Perhaps it is not completely resolved in 5.0.26

we believe it to be concurrency related
master has issue, slave does not

to Shane Bester:

Hi!
the outputs are:

mysql> show create table log;

CREATE TABLE log (
  SourceIP int(11) default NULL,
  ClientTimestamp int(11) default NULL,
  ClientMS int(11) default NULL,
  ServerTimestamp int(11) default NULL,
  SessionID int(11) default NULL,
  Component varchar(255) default NULL,
  EventID int(11) default NULL,
  Severity int(11) default NULL,
  Grouping int(11) default NULL,
  Originator varchar(255) default NULL,
  OriginatorType int(11) default NULL,
  Target varchar(255) default NULL,
  TargetType int(11) default NULL,
  SubTarget varchar(255) default NULL,
  Text1 varchar(255) default NULL,
  Text2 varchar(255) default NULL,
  Text3 varchar(255) default NULL,
  Value1 int(11) default NULL,
  Value2 int(11) default NULL,
  Value3 int(11) default NULL,
  MIMEType int(11) default NULL,
  DataSize int(11) default NULL,
  Data mediumblob,
  Signature varchar(255) default NULL,
  KEY 'ClientTimestamp' ('ClientTimestamp'),
  KEY 'EventID' ('EventID')
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

mysql> show table status like 'log';
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| 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 |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| log  | MyISAM |       7 | Dynamic    |    0 |              0 |   302091264 |      4294967295 |     53100544 | 302091264 |           NULL | 2006-06-27 12:17:15 | 2007-11-02 00:06:43 | 2007-11-01 18:03:34 | latin1_swedish_ci |     NULL |                |         |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+

mysql> check table log extended;
+------------+-------+----------+----------+
| Table      | Op    | Msg_type | Msg_text |
+------------+-------+----------+----------+
| naudit.log | check | status   | OK       |
+------------+-------+----------+----------+

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.26    |
+-----------+

Taras, is this table originally from 4.0 or 4.1 installation?  I see it is version 7..   Please confirm the entire history of the table.  If it was moved (frm,myi,myd) without dump/reload, it may cause this problem.

This is so indeed:
mysql has been updated from version 4.0 (where the problem arose extremely often) to 5.0.26 (only 2 incidents).
Table was moved without rebuild.

Shane, thanks for replies.

please mysqldump and reimport any of the old tables originating in 3.23/4.0/4.1.  if you see corruptions after that, post an update here again and will should try create a testcase.

Hi, Shane!

I've dumped tables according to your advice:
"mysqldump --all-databases > [file]".
Then I've removed all tables.
After that
"mysql -u [user] -p[pass] < [file]"

It has not helped. The priblem has arisen again:
061103  5:14:52 [ERROR] sys:/mysql/bin/mysqld.nlm: Incorrect key file for table '.\naudit\log.MYI'; try to repair it

mysql> check table log extended;
+------------+-------+----------+-------------------------------+
| Table      | Op    | Msg_type | Msg_text                      |
+------------+-------+----------+-------------------------------+
| naudit.log | check | warning  | Table is marked as crashed    |
| naudit.log | check | error    | Found 1889901 keys of 1889902 |
| naudit.log | check | error    | Corrupt                       |
+------------+-------+----------+-------------------------------+

mysql> show table status like 'log';
+------+--------+---------+------------+---------+----------------+-------------
+-----------------+--------------+-----------+----------------+-----------------
----+---------------------+---------------------+-------------------+----------+
----------------+---------+
| 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 |
+------+--------+---------+------------+---------+----------------+-------------
+-----------------+--------------+-----------+----------------+-----------------
----+---------------------+---------------------+-------------------+----------+
----------------+---------+
| log  | MyISAM |      10 | Dynamic    | 2210992 |            154 |   341771868
| 281474976710655 |     75412480 |         0 |           NULL | 2007-11-02 14:24
:09 | 2006-11-03 10:18:26 | 2007-11-02 14:24:09 | latin1_swedish_ci |     NULL |
                |         |
+------+--------+---------+------------+---------+----------------+-------------
+-----------------+--------------+-----------+----------------+-----------------
----+---------------------+---------------------+-------------------+----------+
----------------+---------+

does the corruption occur immediately just by reloading that file? if yes, is it possible you can compress and upload [file] to our ftp.mysql.com/pub/mysql/secret/  ?  only mysql employees can see it, so it's not made public. then we can see what's causing this corruption.

No, the corruption has occured after about 12 hours of intensive work (large amount of INSERTs).

If the slave doesn't experience corruption it may be due to physical layout of the  rows.  For example, slave might have optimized tables with less or different fragmentation patterns of the data/indexes.  We are still trying to reproduce this corruption.

An update - I haven't yet been able to reproduce this corruption on 5.0.26 after days of continuous inserts/updates/deletes on huge tables.  Will alter the test strategy a bit and continue running.

Taras, it's very unlikely that `log` table gets corrupted keyfile.  It only has two indexes on INT columns..  Can you confirm the hardware/OS/drivers are all ok. Also, no crashes or improper shutdowns occurred to trigger such corruption?

Hi, I'm having the same problem on a Mac OSX 10.4 replication slave running 5.0.27 (problem was also present in 5.0.26).  The only two tables of many in the schema which exhibit this problem are session related and have a sustained large number of inserts and deletes happening.  All of the other tables are not often deleted from.  

Also interesting is that there is a second slave, running 5.0.27 on freebsd 6.1 which has never exhibited the problem.  The master to both is running 5.0.24 on freebsd 6.1

mysql> show table status where name like '%REQUEST_CONTEXT%' \G
*************************** 1. row ***************************
           Name: REQUEST_CONTEXT
         Engine: MyISAM
        Version: 9
     Row_format: Dynamic
           Rows: 11599
 Avg_row_length: 609
    Data_length: 7067980
Max_data_length: 4294967295
   Index_length: 224256
      Data_free: 0
 Auto_increment: 288330460
    Create_time: 2006-04-10 13:32:52
    Update_time: 2006-11-27 04:11:39
     Check_time: 2006-11-27 03:56:05
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: delay_key_write=1
        Comment: 

mysql> show table status where name like '%SESSION%' \G
*************************** 1. row ***************************
           Name: SESSION
         Engine: MyISAM
        Version: 10
     Row_format: Fixed
           Rows: 4003
 Avg_row_length: 43
    Data_length: 704942
Max_data_length: 12103423998558207
   Index_length: 493568
      Data_free: 532813
 Auto_increment: 79582139
    Create_time: 2006-11-03 04:39:11
    Update_time: 2006-11-27 04:11:39
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: delay_key_write=1
        Comment:

Hi again,  realized that my problem was likely correlated to a crashing server.  I've posted it as a separate report in Issue: 24615

Hi Scott,  just noting your one table has "Version: 9".  This indicates the table was moved from older version to 5.0.26/7 without dumping/reloading.  In addition to stopping the crashing, you should dump/reload that table too.

Setting status to 'cant repeat' until we see more concrete reports of this error. I doubt this bug has anything to do with the exact fix made in bug #22384 (even though the error messages may look alike - there are probably many causes)

all reporters: please confirm if you have any multi-table deletes ever occuring on the tables that become corrupt?  see bug #25048 for more details.

Is there a fix or workaround for this issue?
I am currently experiencing the exact same problem as Taras.

I am running mysql v5.0.27 on Netware 6.5 Support Pack 5.
The database is being used my the Novell Audit application and the table is the exact same
table the Taras listed earlier in this bug report.

The table was created new ~4 days ago because we were experiencing this problem yet 1-2
days later the problem arose again.  I can repair the table and it is fine for another day
or 2 but then I receive the same error "Incorrect key file for table" and it indicates the
.myi file for the table.

This application does numerous inserts each day.  Right now there are approximately 13+
million records with a data size of 1.8 GB.

Any help would be greatly appreciated.

Larry, please upgrade to the newest version of mysql and check if it fixes the problem.  There have been many MyISAM corruption bugs fixed since 5.0.27.

The problem persists even after upgrading to version 5.0.45.  Every morning I need to repair the index file now so it seems it even occurs more often than before.  
After upgrading to the new version I deleted the affected database and reloaded it new from a mysqldump from the previous version.  It didn't fix the issue.

Should I change to the InnoDB storage engine?