Bug #1166 Corruption of MyISAM table
Submitted: 30 Aug 2003 5:35 Modified: 4 Sep 2003 1:43
Reporter: Vaclav Vobornik Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S1 (Critical)
Version:4.1.0-alfa OS:Linux (Linux,Intel 2.4.21)
Assigned to: Sergei Golubchik CPU Architecture:Any

[30 Aug 2003 5:35] Vaclav Vobornik
Description:
When I create MyISAM table and start to fill with data, for some time I get "Incorrect key file for table: 'items'. Try to repair it" error.

It is allways, when I update 4485th - 4490th record.
Table definition:
CREATE TABLE `items` (
  `id` bigint(20) NOT NULL auto_increment,
  `channel_id` int(10) unsigned NOT NULL default '0',
  `title` varchar(255) NOT NULL default '',
  `link` varchar(255) NOT NULL default '',
  `description` text NOT NULL,
  `image` varchar(255) default NULL,
  `author` varchar(100) NOT NULL default '',
  `pubdate` datetime NOT NULL default '0000-00-00 00:00:00',
  `active` datetime NOT NULL default '0000-00-00 00:00:00',
  `cat_id` smallint(5) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `link` (`link`),
  FULLTEXT KEY `full` (`title`,`description`)
) TYPE=MyISAM CHARSET=utf8 COMMENT='jednotlive zpravy' AUTO_INCREMENT=4491 ;

The crash sql is:

UPDATE items SET channel_id = '35',
title = 'oky',
description = 'any text',
active = now( ) WHERE link = 'http\:\/\/weblog\.oborsky\.cz\/spot83873728\.html' 

I am able to re-corrupt table, when do "repair table items;" and reuse this crash-sql command.

How to repeat:
On http://blogator.com/bug/corrupt_mysql_table.zip (480kB) is mysql-dump of this table and crash-sql command. 

On http://blogator.com/bug/variables.txt is output from "show variables;" command
[30 Aug 2003 6:00] Vaclav Vobornik
when I remove FULLTEXT index, it looks to be all OK (but cannot use fulltext search)
[30 Aug 2003 11:49] MySQL Verification Team
Tried with both latest 4.0.15 and 4.1.1 and got the same result (with same variables):

 ./client/mysql bug < tmp/items.sql.zip.sql 
[/mnt/work/mysql-4.1]$ ./client/mysql bug < tmp/crashSQL.txt 
[/mnt/work/mysql-4.1]$ ./client/mysql bug -e "show tables"   
+---------------+
| Tables_in_bug |
+---------------+
| items         |
+---------------+
[/mnt/work/mysql-4.1]$ ./client/mysql bug -e "check table items"
+-----------+-------+----------+----------+
| Table     | Op    | Msg_type | Msg_text |
+-----------+-------+----------+----------+
| bug.items | check | status   | OK       |
+-----------+-------+----------+----------+
[/mnt/work/mysql-4.1]$ ./client/mysql bug < tmp/crashSQL.txt 
[/mnt/work/mysql-4.1]$ ./client/mysql bug -e "check table items"
+-----------+-------+----------+----------+
| Table     | Op    | Msg_type | Msg_text |
+-----------+-------+----------+----------+
| bug.items | check | status   | OK       |
+-----------+-------+----------+----------+
[1 Sep 2003 3:38] Sergei Golubchik
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

4.1 fulltext index corruption was fixed several weeks ago.
Fix will come with 4.1.1
[3 Sep 2003 14:50] Vaclav Vobornik
I have installed today's Mysql version from development tree (4.1.1-alpha) and seems not help :-((

mysql> repair table items;
+----------------+--------+----------+----------+
| Table          | Op     | Msg_type | Msg_text |
+----------------+--------+----------+----------+
| blogator.items | repair | status   | OK       |
+----------------+--------+----------+----------+
1 row in set (14.51 sec)

mysql> check table items;
+----------------+-------+----------+--------------------------------------+
| Table          | Op    | Msg_type | Msg_text                             |
+----------------+-------+----------+--------------------------------------+
| blogator.items | check | error    | Key in wrong position at page 771072 |
| blogator.items | check | error    | Corrupt                              |
+----------------+-------+----------+--------------------------------------+
2 rows in set (0.24 sec)

mysql>
[4 Sep 2003 1:43] Sergei Golubchik
I cannot get the table from the above url (it says "404 file not found")