MySQL Bugs: #14397: OPTIMIZE TABLE with an open HANDLER causes a crash

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

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.

"INSERT INTO T1 VALUES SELECT x FROM t1;"  is a typo, should be:

INSERT INTO t1 (SELECT x FROM t1);

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.

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

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

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

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

Pushed to 4.0.27, 4.1.16, and 5.0.17.

Noted in 4.0.27, 4.1.16, 5.0.17 changelogs.