Bug #14397 OPTIMIZE TABLE with an open HANDLER causes a crash
Submitted: 27 Oct 2005 15:05 Modified: 20 Nov 2005 3:23
Reporter: Mark Leith Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:4.0.16-BK/4.1 BK/5.0 BK OS:Linux (Linux, Any)
Assigned to: Ingo Strüwing CPU Architecture:Any

[27 Oct 2005 15:05] Mark Leith
Description:
When running an OPTIMIZE table against a table that has an open HANDLER statement running against it, the server crashes with a signal 11. 

How to repeat:
DROP TABLE t1;
CREATE TABLE t1 (x INT);
INSERT INTO t1 VALUES (1);
/* Run the below a number of times to generate a decent data set */
INSERT INTO T1 VALUES SELECT x FROM t1;

In two seperate clients:

/* Client 1 */

HANDLER t1 OPEN;
HANDLER t1 READ FIRST LIMIT 100000;
HANDLER t1 READ NEXT LIMIT 100000;

/* Client 2 During the HANDLER ..  READ statements */

OPTIMIZE TABLE t1;

Suggested fix:
Unknown.
[27 Oct 2005 15:14] Mark Leith
"INSERT INTO T1 VALUES SELECT x FROM t1;"  is a typo, should be:

INSERT INTO t1 (SELECT x FROM t1);
[3 Nov 2005 17:23] Ingo Strüwing
During my work of analyzing and fixing this bug, I faced three problems:

1. The cause of the bug was that we did not check the table version for
   the HANDLER ... READ commands. We did not notice when a table was
   replaced by a new one. This can happen during ALTER TABLE, REPAIR
   TABLE, and OPTIMIZE TABLE (there might be more cases). In the
   changesets I call the fix for this problem "the primary bug fix".

2. While checking the code, I noticed (by chance) that mysql_ha_flush()
   was not always called with a locked LOCK_open. Though the function
   comment clearly said it must. My first attempt was to insert the
   required locking code in all appropriate places. This revealed the
   third problem (see below). Also I became aware that this locking
   would be a waste in many cases as the use of HANDLER is quite
   exceptional. I changed the code so that the locking is only done when
   it is really required. In the changesets I call the fix for this
   problem "the secondary fix".

3. In 5.0 (not in 4.1 or 4.0) DROP TABLE had a possible deadlock flaw in
   concur with FLUSH TABLES WITH READ LOCK. In the changesets I call the
   fix for this problem "the 5.0 addendum fix".

Since the changesets for 4.0, 4.1 and 5.0 are a bit different, I commit
three of them. During the merge in our BitKeeper repositories I will see
that the result is so as if I had pushed these changesets independently.
[3 Nov 2005 17:25] 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/internals/31896
[3 Nov 2005 17:27] 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/internals/31897
[3 Nov 2005 17:31] 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/internals/31898
[15 Nov 2005 20:57] 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/internals/32291
[18 Nov 2005 14:20] Ingo Strüwing
Pushed to 4.0.27, 4.1.16, and 5.0.17.
[20 Nov 2005 3:23] Paul Dubois
Noted in 4.0.27, 4.1.16, 5.0.17 changelogs.