MySQL Bugs: #14259: Incorrect key file for table: UTF-8 charset and multi-column FT indexes

Bug #14259	Incorrect key file for table: UTF-8 charset and multi-column FT indexes
Submitted:	24 Oct 2005 14:51	Modified:	14 Nov 2005 9:51
Reporter:	Andrea Gangini	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.016-BK	OS:	Linux (Linux)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
N.B. this bug is actually a duplicate of bug http://bugs.mysql.com/bug.php?id=5686, but since there are no updates on it for more than a year, and since also the new MySql 5 is affected, I'm opening a new bug report.

How to repeat:
Download this export http://62.149.226.152/bugreport.sql.gz and import in any
database you like.

Just by trying to delete a record, mysql argues about index corruption:
mysql> delete from TESTCASE where inumber=375;
ERROR 1034 (HY000): Incorrect key file for table 'TESTCASE'; try to repair it

Let's repair the table then:
mysql> repair table TESTCASE;
+----------------+--------+----------+----------+
| Table          | Op     | Msg_type | Msg_text |
+----------------+--------+----------+----------+
| PROVA.TESTCASE | repair | status   | OK       |
+----------------+--------+----------+----------+
1 row in set (1.67 sec)

Suggested fix:
Ok, now we change the default collation of the column involved in the FT index:
ALTER TABLE `TESTCASE` CHANGE `ititle` `ititle` VARCHAR( 160 ) CHARACTER SET
utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL , CHANGE `ibody` `ibody` TEXT
CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL , CHANGE `imore` `imore`
TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ;
Query OK, 900 rows affected (2.88 sec)
Records: 900  Duplicates: 0  Warnings: 0

Trying to delete one record again:
mysql> delete from TESTCASE where inumber=375;
Query OK, 1 row affected (0.01 sec)

Gotcha! It must be something involved in the default
'utf8_general_ci' collation....

But this workaround only fixes this testcase, corruption on live system still occurs by using UTF-8 tables. I have several table which *every*night* i must repair, and which during the day show random occurrencies of the "ERROR 1034 (HY000): Incorrect key file for table" while trying to update some rows.

Changing the charset to latin1 solves the problem: no more corruption.

Thank you for a bug report. I was able to repeat it on Fedora Core 1 with 5.0.16-BK (ChangeSet@1.1949, 2005-10-28 13:02:26+04:00, petr@mysql.com)
just as you described using your large dump I had downloaded.

[openxs@Fedora 5.0]$ bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.16

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

mysql> create database prova;
Query OK, 1 row affected (0.06 sec)

mysql> exit
Bye
[openxs@Fedora 5.0]$ bin/mysql -uroot prova </tmp/bugreport.sql
[openxs@Fedora 5.0]$ bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.16

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

mysql> use prova
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> delete from TESTCASE where inumber=375;
ERROR 126 (HY000): Incorrect key file for table './prova/TESTCASE.MYI'; try to repair it
mysql> repair table TESTCASE;
+----------------+--------+----------+----------+
| Table          | Op     | Msg_type | Msg_text |
+----------------+--------+----------+----------+
| prova.TESTCASE | repair | status   | OK       |
+----------------+--------+----------+----------+
1 row in set (13.06 sec)

mysql> ALTER TABLE `TESTCASE` CHANGE `ititle` `ititle` VARCHAR( 160 ) CHARACTER SET
    -> utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL , CHANGE `ibody` `ibody` TEXT
    -> CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL , CHANGE `imore` `imore`
    -> TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;
Query OK, 900 rows affected (20.05 sec)
Records: 900  Duplicates: 0  Warnings: 0

mysql> delete from TESTCASE where inumber=375;
Query OK, 1 row affected (0.06 sec)

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

Notes: 

- I was not able to repeat with the similar number of rows of autogenerated data using only Latin characters.
- Your dump was obtained on 4.1.14. Do you have any similar problems
on that version?

Export was done on a 4.1.x mysql server, but we are now using the 5.0.15 version.
When using UTF-8 tables, and of course by saving utf-8 characters in them, the result are always corrupted indexes and the impossibility of modifying determinate rows in the table.
The occurency of this error is rather frequent, even with the latest 5.0.15.
We are now using latin1 tables, and doing some character conversion at application level (in order to store only latin 1 characters), and we noticed no corruption (it's more than a week now).

So I think it's something involved in storing UTF-8 characters in a UTF-8 tables; we have also a huge database in production which have utf-8 tables but we store only latin1 characters in it and there was no corruption in months.

This is really a duplicate bug for:
http://bugs.mysql.com/bug.php?id=5686

Sergey Vojtovich sent a patch fixing this problem on 12 Nov, 2005:
Please find the patch here: http://lists.mysql.com/internals/32203

I'm closing this report a duplicate.