MySQL Bugs: #14195: ERROR 126 (HY000) Incorrect key file for table ...

Bug #14195	ERROR 126 (HY000) Incorrect key file for table ...
Submitted:	20 Oct 2005 21:32	Modified:	7 Nov 2005 19:25
Reporter:	Jean-Paul Le Fèvre	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.13	OS:	Linux (Linux 2.4.20 SMP)
Assigned to:		CPU Architecture:	Any

Description:

In a backup procedure :

On the production machine :
options="-h ccmysql -n --opt"
mysqldump $options l3s_db cluster_candidates > $HOME/tmp/tables.sql

On the backup machine : (glibc 2.2)
mysql l3s_db < tables.sql

This command fails :
ERROR 126 (HY000) at line 56: Incorrect key file for table './xmm_l3s_db/cluster_candidates.MYI'; try to repair it
 
And 
myisamchk -r cluster_candidates
finds only 36 records.

How to repeat:

Run :
mysql some_db < tables.sql

But it depends on the platform ! on some environments it works.

Suggested fix:

None so far

Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Additional info:

I was not able to repeat the described behaviour with your data on Fedora Core 1 and 5.0.15:

[openxs@Fedora 5.0]$ bin/mysql -uroot test < tables.sql
[openxs@Fedora 5.0]$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

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

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

mysql> desc cluster_candidates;
+--------------------------+----------------------+------+-----+---------+-------+
| Field                    | Type                 | Null | Key | Default | Extra |
+--------------------------+----------------------+------+-----+---------+-------+
| id                       | smallint(5) unsigned | NO   | PRI | 0       | |
| xlssc                    | smallint(5) unsigned | YES  | UNI | NULL    | |
| cluster_image            | varchar(120)         | YES  | UNI | NULL    | |
| full_name                | varchar(80)          | NO   | UNI |         | |
| short_name               | varchar(20)          | YES  |     | NULL    | |
| validity                 | enum('true','false') | YES  |     | NULL    | |
| status                   | tinyint(3) unsigned  | YES  |     | NULL    | |
| class_id                 | smallint(5) unsigned | YES  |     | NULL    | |
| velocity_id              | smallint(5) unsigned | YES  |     | NULL    | |
| kept                     | tinyint(3) unsigned  | YES  |     | 0       | |
| quality                  | tinyint(3) unsigned  | YES  |     | 1       | |
| comment                  | varchar(120)         | YES  |     | NULL    | |
| alpha                    | float                | NO   |     | 0       | |
| delta                    | float                | NO   |     | 0       | |
| I_band_file              | varchar(120)         | YES  | UNI | NULL    | |
| R_band_file              | varchar(120)         | YES  | UNI | NULL    | |
| V_band_file              | varchar(120)         | YES  | UNI | NULL    | |
| merged_catalog_table     | varchar(120)         | YES  | UNI | NULL    | |
| merged_catalog_region    | varchar(120)         | YES  | UNI | NULL    | |
| optical_image            | varchar(120)         | YES  |     | NULL    | |
| rgb_finding_chart_sample | varchar(120)         | YES  | UNI | NULL    | |
| rgb_finding_chart_image  | varchar(120)         | YES  | UNI | NULL    | |
+--------------------------+----------------------+------+-----+---------+-------+
22 rows in set (0.04 sec)

mysql> select count(*) from cluster_candidates;
+----------+
| count(*) |
+----------+
|      656 |
+----------+
1 row in set (0.00 sec)

mysql> exit
Bye
[openxs@Fedora 5.0]$ uname -a
Linux Fedora 2.4.22-1.2115.nptl #1 Wed Oct 29 15:42:51 EST 2003 i686 i686 i386 GNU/Linux

Please, reopen this report if you can provide additional information on how to repeat the problem you described.

On another computer, using another database, running something like :

load data local infile 'events.csv' into table events fields terminated by ','
(event_number, run_number, time, energy, quality, x,y,z, ra, decl, fits_file_id, row_number);

I'm having the same problem : only 948 rows are loaded, error 126 is thrown on line 949.
It happens only with MyISAM table.
As usual it is not reproductible on other machines.

Looking at the various parameters in the configuration file (my.cnf) I've figured out that
the value for key_cache_block_size was 1M and that seemed to trigger the error.

I've commented out the setting of this variable on all my my.cnf, using the default value which is 1024, and magically, my various scripts started working again.

Yes, I also used the default value for the key_cache_block_size (1024 bytes) when tried to repeat the problem you described. 

According to the manual (http://dev.mysql.com/doc/refman/5.0/en/key-cache-block-size.html), it usually needs to be equal to the I/O buffer size. I see no reason for this parameter to be equal to 1M... 

Although, this does not explain the results you got.