MySQL Bugs: #4198: Incorrect key file for table

Bug #4198	Incorrect key file for table
Submitted:	18 Jun 2004 1:27	Modified:	25 Nov 2004 13:41
Reporter:	Olivia	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.2 alpha	OS:	Linux (RH9 Linux)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Delete on MyIsam table produces error: ERROR 1034 (HY000): Incorrect key file for table: 'mytable1'; try to repair it

The sql statements below (How to repeat) works fine in version 4.0.X but fail in 4.1.X (including 4.1.2 latest).

The statements below should work fine and result in 1 record being deleted in mytable1. However I get the 'Incorrect key...' error and upon checking:

mysql> check table mytable1;
+-----------------+-------+----------+----------------------------+
| Table           | Op    | Msg_type | Msg_text                   |
+-----------------+-------+----------+----------------------------+
| mydata.mytable1 | check | warning  | Table is marked as crashed |
| mydata.mytable1 | check | error    | Found 1 keys of 2          |
| mydata.mytable1 | check | error    | Corrupt                    |
+-----------------+-------+----------+----------------------------+
3 rows in set (0.00 sec)

How to repeat:
create table mytable1 (
myid int unsigned not null,
tchild tinyint unsigned default '0' not null,
lineage text not null,
primary key (myid),
key (lineage(255))
);

create table mytable2 (
myid int unsigned not null,
tchild tinyint unsigned not null,
primary key (myid));

create table mytemp (
myid int unsigned not null,
totalc tinyint unsigned not null,
primary key (myid));

insert into mytemp values (10,1);
insert into mytable2 values (16,0);
insert into mytable1 values (10,0,'/1/F/G/'),(16,1,'/1/F/G/A/');

update mytable2,mytable1 set mytable1.tchild=0 where mytable1.myid=mytable2.myid;
update mytemp,mytable1 set mytable1.tchild=mytable1.tchild-mytemp.totalc where mytable1.myid=mytemp.myid;
delete mytable1 from mytable1,mytable2 where mytable1.myid=mytable2.myid and mytable2.tchild=0;

ChangeSet
  1.1942 04/06/21 15:17:07 ram@gw.mysql.r18.ru +3 -0
  a fix (bug #4198: Incorrect key file for table).

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

This error still occurs in 4.1.3-alpha.  Since the bug report was based on 4.1.2-alpha, and the resolution was supposed to be included in the 4.1.3-alpha release, I wonder if the error still exists.  On my machine, the error only occurs EVERY OTHER time.  Please advise if the fix has been included in 4.1.3-alpha.  If so, we need to revisit it.

Thanks!
Rob

After further testing, it appears that the problem occurs with certain tables that were created with an earlier version (4.1.1-alpha) of MySQL.  I dumped the tables w/ mysqldump and recreated them in 4.1.3-alpha and the problem seems to be fixed.  Weird though!

Please reopen this bug.

On RH9, kernel 2.4.18-3, MySQL the problem still persists, and is even more weird!
Most importantly, it cannot be fixed by dumping/reloading the table in question, restarting mysql, etc.
Nor can it be fixed by "repair tables" or "myisamchk -r".

Basically I dumped all my tables from 4.0.14 (using mysqldump), installed 4.1.4, and loaded the dump file.
Now I have a table on which I can do many operations, except for "DELETE".
 
The table looks like this:
CREATE TABLE `hitlist` (
  `id` int(11) NOT NULL auto_increment,
  `time` int(11) NOT NULL default '0',
  `urlid` int(11) NOT NULL default '0',
  `host` char(40) NOT NULL default '',
  `os` enum('WinNT','Win95','Win98','Linux','Other','Unix','OS2','Win3x','Mac','WinXP','Win2k','WinME') NOT NULL default 'WinNT',
  `browser` enum('ie3','ie4','ie5','ns4','other','opera','ie6','ns3','ns5') NOT NULL default 'ie3',
  `referer` char(120) NOT NULL default '',
  PRIMARY KEY  (`id`),
  KEY `urlid` (`urlid`),
  KEY `referer` (`referer`),
  KEY `time` (`time`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

repair tables hitlist says:
+-----------------+--------+----------+----------+
| Table           | Op     | Msg_type | Msg_text |
+-----------------+--------+----------+----------+
| top1000.hitlist | repair | status   | OK       |
+-----------------+--------+----------+----------+

And I got an error message when I do this:
mysql> delete from hitlist where id < 200000;
ERROR 1034 (HY000): Incorrect key file for table 'hitlist'; try to repair it

This error is 100% reproducible in my installation.

If it "cannot be fixed by dumping/reloading" then it's a different bug.
Please report it separately, as a new bug, and don't forget a repeatable test case

Just had this happen again... got the "ERROR 1034 (HY000): Incorrect key file
for table" error when running a simple delete:

DELETE FROM table WHERE id = 10;

Upgraded from 4.1.2-alpha to 4.1.6-gamma on RH9 using rpms.  Still got the same error.  Did a CREATE TABLE table2 LIKE table;  and an INSERT INTO table2 SELECT * FROM table;  (followed by DROP and RENAME TABLE).  This fixed the problem.  Since recreating the table seems to fix the problem, a test case for this is going to be REALLY tough (since developers will need to create the table in the test case).  Any suggestions as to what to post if it occurs again?

Hi!

To fix the issue I really need a repeatable test suite.
So, please try to create it.

Thanks in advance, 
Ramil.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".