MySQL Bugs: #25940: Using multitable delete syntax for single table delete causes incorrect key file

Bug #25940	Using multitable delete syntax for single table delete causes incorrect key file
Submitted:	30 Jan 2007 11:57	Modified:	31 Jan 2007 13:20
Reporter:	Eric de Ruiter	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.30	OS:	Linux (linux 2.6)
Assigned to:		CPU Architecture:	Any
Tags:	crash, keyfile, myisam, table

Description:
My first report of this bug can be found in: http://bugs.mysql.com/bug.php?id=22384. I assumed the bug was the same, but that bug should be fixed in 5.0.26 and I can reproduce my bug in 5.0.30 .. so this is probably a new bug .. hence this new bugreport.

The problem is that when I use the multitable delete syntax for a single table delete for a large (I could only reproduce it with >= 1000 rows) table with multiple indexes I get the error: Incorrect key file for table.

This leads to mysql marking the table as crashed (and somehow this sometimes leads to completely locking the table (phpmyadmin shows the table as 'in use') but most of the times the table is still usable (but check table shows it is marked as crashed)).

The multitable delete syntax is used by our own database layer, for now we can work around it by using the single table delete syntax when we have no joins ... but we can't detect if queries like: DELETE relation FROM relation LEFT JOIN media ON media.id = relation.child_id WHERE relation.child_type = 'Media' AND media.id=2 are optimized to use only 1 table, so in the end this is a showstopper for us.

How to repeat:
Step 1)

Download the testcase sql file from: http://nitrate.nl/eric/testcase.sql

Step 2)

Execute one of the following queries:

- DELETE FROM relation USING relation WHERE ( 1 =1 ) AND (`child_type` = 'Media' AND `child_id` = '2')
- DELETE relation FROM relation WHERE ( 1 =1 ) AND (`child_type` = 'Media' AND `child_id` = '2')
- DELETE relation FROM relation LEFT JOIN media ON media.id = relation.child_id WHERE relation.child_type = 'Media' AND media.id=2

Each of these queries will result in the following error: 

[126]: Incorrect key file for table './testcase/relation.MYI'; try to repair it

But if you use the following queries the deletes will work without the incorrect key file error.
- DELETE FROM relation WHERE (1=1) AND (`child_type`='Media' AND `child_id`='2')
- DELETE relation FROM relation LEFT JOIN media ON media.id = relation.child_id - WHERE relation.child_type = 'Media' AND media.name = 'default'

Thank you for the report.

I can not repeat it using current BK sources.

Please try version 5.0.33 and say result.

Tnx for the quick reply. I tested with 5.0.34 and indeed the problem does not occur there.

I found the following commit (included in 5.0.34): http://lists.mysql.com/commits/17386 (fixes bug: http://bugs.mysql.com/bug.php?id=25048). 
This seems to fix the exact problem I was having, but you reported no success in reproducing this bug with 5.0.33 (which doesn't include the commit I mentioned above) .. so my fear is that this bug can be hidden (just like bug: http://bugs.mysql.com/bug.php?id=22384 which didn't show up in 4.0/4.1 at first)

Before upgrading our production servers from 4.1 to 5.0 I really want to be sure this bug is fixed and not hidden.

Hi Eric, I reported no success with sources modified today available from BK repository. You can read about our Development Source Tree at http://dev.mysql.com/doc/refman/5.0/en/installing-source-tree.html I asked you to try 5.0.33, because this version is last generally available Community Edition and won't force you to install additional software to download development sources from BitKeeper repository. Sorry for confusing.

As you can not repeat it with version 5.0.34 too, I close this report as "Can't repeat"