MySQL Bugs: #32791: Incorrect key file for table './nica

Bug #32791	Incorrect key file for table './nica_search/test.MYI'; try to repair it
Submitted:	27 Nov 2007 20:26	Modified:	25 Nov 2011 19:52
Reporter:	renaud houver	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: FULLTEXT search	Severity:	S2 (Serious)
Version:	5.0.50, 5.0, 4.1, 5.1 BK	OS:	Linux (LRed Hat Enterprise Linux Server release 5 (Tikanga))
Assigned to:		CPU Architecture:	Any
Tags:	corruption

Description:
I have a table with several full text index. I believe the problem is related to the biggest fulltext index which is on a TEXT column.
When deleting a row or updating this column on a row, I get the error Incorrect key file for table... but apparently only if the content of the column is quite long. Deleting a row with a small content on column works fine.
Pls. note that the row is deleted despite the error. 
If I uncomment the ft_stopword_file parameter in my.cnf, I don't get the error.
I have seen a similar bug if tempdir is too small. There should be plenty of space in /tmp (35GB).
The problem occurs on a test environment. 
The tricky part is that I cannot reproduce the error on our production machine, which is quite similar, just bigger disk.
That might not be a bug but do you have a clue what the reason could be.

How to repeat:
I can upload test script but you might not be able to reproduce on your environment.

mysql conf file

Attachment: my.cnf (application/octet-stream, text), 2.98 KiB.

stopwords

Attachment: mysql.stopwords (application/octet-stream, text), 581 bytes.

OS details updated

Thank you for a problem report. Please, send SHOW CREATE TABLE and SHOW TABLE STATUS results for the problematic table.

show create table test;

| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |

| test  | CREATE TABLE `test` (
  `id_phot` int(11) NOT NULL,
  `storage_class` varchar(4) default NULL,
  `status_c` int(11) default NULL,
  `file_name` varchar(100) default NULL,
  `creation_date` date default NULL,
  `polfoto_caption` text,
  `arrival_date` date default NULL,
  `credit` varchar(32) default NULL,
  `belong_to` int(11) default NULL,
  `width` int(11) default NULL,
  `height` int(11) default NULL,
  `right` int(11) default NULL,
  `headline` varchar(256) default NULL,
  `byline_title` varchar(100) default NULL,
  `file_size` int(11) default NULL,
  `special_instr` varchar(256) default NULL,
  `byline` varchar(100) default NULL,
  `category` varchar(100) default NULL,
  PRIMARY KEY  (`id_phot`),
  FULLTEXT KEY `caption` (`polfoto_caption`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |

1 row in set (0.01 sec)

show table status;
| 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 |

 test                 | MyISAM |      10 | Dynamic    |      99 |            394 |       39224 |  281474976710655 |        47104 |       168 |           NULL | 2007-11-27 19:49:03 | 2007-11-27 19:49:16 | 2007-11-27 19:49:03 | utf8_general_ci |     NULL |

Extra information : 
As you can see in test script, the data is first loaded in table using load data infile with index disabled and index build using myisamchk -r.

raise severity.

I was able to replicate the bug on Mysql 5.0.51 linux

It seems that on mysql 5.0.51 after the bug replication the fulltext index is now broken.
A new select query on the fulltext index fullfill all the memory.
The cpu clock is always 100%, show full processlist report the query in the satte of something like copying results to tmp table.
Mysql doesn't hang, but is not ending the query. The system is unstable.

Thank you for the report.

Verified as described with a bit modified test.sh:

#!/bin/sh

DIR=`pwd`
DBBASE=/Users/apple/Applications/mysql-4.1
DBDIR=$DBBASE/data/nica_search
BINDIR=$DBBASE/bin
TMPDIR=$DIR/nica/
SOCKET=/tmp/mysql41.sock
PWD=
echo "$(date +'%d/%m/%y %H:%M:%S'): Import starting"

$BINDIR/mysql --socket=$SOCKET -u root --password=$PWD -e "create database if not exists nica_search"

rm $DBDIR/*.TMD

echo "$(date +'%d/%m/%y %H:%M:%S'): creating database..."
$BINDIR/mysql --socket=$SOCKET -u root --password=$PWD nica_search <$DIR/test_db.sql

echo "$(date +'%d/%m/%y %H:%M:%S'): De-activating keys..."
$BINDIR/myisamchk --keys-used=0 -rq --tmpdir=$TMPDIR $DBDIR/test

echo "$(date +'%d/%m/%y %H:%M:%S'): Importing test data"
$BINDIR/mysql -u root --password=$PWD --socket=$SOCKET nica_search <$DIR/test.sql

$BINDIR/myisamchk -r --tmpdir=$TMPDIR $DBDIR/test

echo "$(date +'%d/%m/%y %H:%M:%S'): Flushing tables..."
$BINDIR/mysqladmin --password=$PWD  --socket=$SOCKET -u root flush-tables

echo "$(date +'%d/%m/%y %H:%M:%S'): Import done"

$BINDIR/mysql --socket=$SOCKET -u root --password=$PWD nica_search -e "check table test"
$BINDIR/mysql --socket=$SOCKET -u root --password=$PWD nica_search -e "delete from test limit 1"
$BINDIR/mysql --socket=$SOCKET -u root --password=$PWD nica_search -e "select count(*) from test"

To repeat don't forget to add

ft_stopword_file= /path/to/mysql.stopwords

to configuration file.

When the index is broken, REPAIR TABLE seems to work not properly.

The index remains broken.

Is someone experiencing this too?

Stanza

I think I saw the same thing tonight.  I had two tables end up in this state somehow and no amount of myisamchk --recover or "repair table" would fix it.

9:16pm colo:/var/lib/mysql/s9y> myisamchk -e s9y_entrycat.MYI
Checking MyISAM file: s9y_entrycat.MYI
Data records:      47   Deleted blocks:       0
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
- check records and index references

9:16pm colo:/var/lib/mysql/s9y> myisamchk --recover s9y_entrycat.MYI
- recovering (with sort) MyISAM-table 's9y_entrycat.MYI'
Data records: 47
- Fixing index 1

9:16pm colo:/var/lib/mysql/s9y> /etc/init.d/mysql start
Starting MySQL database server: mysqld.
Checking for corrupt, not cleanly closed and upgrade needing tables..

9:16pm colo:/var/lib/mysql/s9y> mysql s9y
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 10
Server version: 5.1.23-rc-1 (Debian)

mysql> check table s9y_entrycat;
+------------------+-------+----------+---------------------------------------------------------------+
| Table            | Op    | Msg_type | Msg_text                                                      |
+------------------+-------+----------+---------------------------------------------------------------+
| s9y.s9y_entrycat | check | Error    | Incorrect key file for table 's9y_entrycat'; try to repair it | 
| s9y.s9y_entrycat | check | error    | Corrupt                                                       | 
+------------------+-------+----------+---------------------------------------------------------------+
2 rows in set (0.04 sec)

mysql> 
mysql> show status s9y_entrycat;
+--------------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| 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 |
+--------------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| s9y_entrycat | NULL  |    NULL | NULL      | NULL |          NULL |        NULL |            NULL |        NULL |      NULL |          NULL | NULL        | NULL        | NULL      | NULL      |    NULL | NULL          | Incorrect key file for table 's9y_entrycat'; try to repair it |

mysql> repair table s9y_entrycat;
+------------------+--------+----------+---------------------------------------------------------------+
| Table            | Op    | Msg_type | Msg_text                                                      |
+------------------+--------+----------+---------------------------------------------------------------+
| s9y.s9y_entrycat | repair | Error    | Incorrect key file for table 's9y_entrycat'; try to repair it |
| s9y.s9y_entrycat | repair | error    | Corrupt                                                      |
+------------------+--------+----------+---------------------------------------------------------------+

mysql> repair table s9y_entrycat USE_FRM;
+------------------+--------+----------+-------------------------------------+
| Table            | Op    | Msg_type | Msg_text                            |
+------------------+--------+----------+-------------------------------------+
| s9y.s9y_entrycat | repair | warning  | Number of rows changed from 0 to 47 |
| s9y.s9y_entrycat | repair | status  | OK                                  |

Finally!  Gah!

It is possible to have a workaround until the bugfix process completes?
I'm working every day with table corrupted..

Thank you in advice.

Stanza

this testcase could be better.  is there a pure SQL cut 'n paste testcase?

o] why is myisamchk being used at all?
o] did you tell mysqld and myisamchk to use ft_stopword_file ?

I did mistake when tested this first time: I have not specified option --ft_stopword_file=/path/to/mysql.stopwords for myisamchk command. Problem is not repeatable if do this. This is not a bug.