Bug #15977 mysqldump DISABLE KEYS/LOCK TABLES in wrong order
Submitted: 25 Dec 2005 4:59 Modified: 13 Jul 2006 19:54
Reporter: Kolbe Kegel Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: mysqldump Command-line Client Severity:S3 (Non-critical)
Version:4.1,5.0 OS:Linux (Linux)
Assigned to: Ian Greenhoe CPU Architecture:Any

[25 Dec 2005 4:59] Kolbe Kegel 
Description:
When mysqldump is executed against a MyISAM table, DISABLE KEYS is written before LOCK TABLES.

It would be possible for a query executed after keys were disabled and before tables were locked to take a very long time, as it would be executing without the benefit of indexes.

How to repeat:
Create a MyISAM table
Run mysqldump against the table

/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
LOCK TABLES `t1` WRITE;
UNLOCK TABLES;
/*!40000 ALTER TABLE `t1` ENABLE KEYS */;

Suggested fix:
LOCK TABLES should be written before DISABLE KEYS in mysqldump output.
[21 Jun 2006 7:12] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/7985
[13 Jul 2006 9:03] Ian Greenhoe 
In 4.1.21
[13 Jul 2006 19:54] Paul DuBois 
Noted in 4.1.21 changelog.

When mysqldump disabled keys and locked a MyISAM table, the lock operation happened second. If another client performed a query on the table in the interim, it could take a long time due to indexes not
being used. Now the lock operation happens first.