Bug #25856 HANDLER table OPEN in one connection lock DROP TABLE in another one
Submitted: 25 Jan 2007 21:28 Modified: 31 Aug 2007 1:51
Reporter: jocelyn fournier (Silver Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Locking Severity:S3 (Non-critical)
Version:5.0, 5.1 OS:Linux (linux)
Assigned to: Davi Arnaut CPU Architecture:Any
Tags: qc

[25 Jan 2007 21:28] jocelyn fournier
Description:
Hi,

Using HANDLER table OPEN syntax on a table with an unsupported engine has a strange side effect : it prevents from dropping the table within another connection.

Regards,
  Jocelyn Fournier
  www.mesdiscussions.net

How to repeat:
In connection 1 :

CREATE TABLE t (a int) ENGINE=MEMORY;

In connection 2 :

HANDLER t OPEN;
ERROR 1031 (HY000): Table storage engine for 't' doesn't have this option

In connection 1 :

DROP TABLE t;

=> locked

In connection 2 :

show processlist\G 

*************************** 34. row ***************************
     Id: 2
   User: root
   Host: localhost
     db: test
Command: Query
   Time: 61
  State: Waiting for table
   Info: drop table t

DROP TABLE will only be executed when the connection 2 is closed.

Suggested fix:
HANDLER should not lock the table if the storage engine is not supported.
[26 Jan 2007 9:52] Hartmut Holzgraefe
mysqltest test case

Attachment: bug25856.tgz (application/x-gtar, text), 687 bytes.

[16 Jul 2007 12:36] Davi Arnaut
Close the table if the handler open fails.

Attachment: handler-lock-leak-on-error-path.patch (, text), 9.91 KiB.

[15 Aug 2007 5:13] 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/32533

ChangeSet@1.2512, 2007-08-15 02:13:39-03:00, davi@moksha.local +3 -0
  Bug#25856 (HANDLER table OPEN in one connection lock DROP TABLE in another one)
  
  mysql_ha_open calls mysql_ha_close on the error path (unsupported) to close the (opened) table before inserting it into the tables hash list handler_tables_hash) but mysql_ha_close only closes tables which are on the hash list, causing the table to be left open and locked.
  
  This change moves the table close logic into a separate function that is always called on the error path of mysql_ha_open or on a normal handler close (mysql_ha_close).
[15 Aug 2007 8:24] Konstantin Osipov
Approved over email with a few comments.
[15 Aug 2007 15:13] 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/32565

ChangeSet@1.2512, 2007-08-15 12:13:34-03:00, davi@moksha.local +3 -0
  Bug#25856 (HANDLER table OPEN in one connection lock DROP TABLE in another one)
  
  mysql_ha_open calls mysql_ha_close on the error path (unsupported) to close the (opened) table before inserting it into the tables hash list handler_tables_hash) but mysql_ha_close only closes tables which are on the hash list, causing the table to be left open and locked.
  
  This change moves the table close logic into a separate function that is always called on the error path of mysql_ha_open or on a normal handler close (mysql_ha_close).
[16 Aug 2007 16:13] 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/32650

ChangeSet@1.2571, 2007-08-16 13:12:48-03:00, davi@moksha.local +3 -0
  Bug#25856 (HANDLER table OPEN in one connection lock DROP TABLE in another one)
  
  mysql_ha_open calls mysql_ha_close on the error path (unsupported) to close the (opened) table before inserting it into the tables hash list handler_tables_hash) but mysql_ha_close only closes tables which are on the hash list, causing the table to be left open and locked.
  
  This change moves the table close logic into a separate function that is always called on the error path of mysql_ha_open or on a normal handler close (mysql_ha_close).
[18 Aug 2007 22:23] Konstantin Osipov
Queued into 5.0 and 5.1 -runtime
[21 Aug 2007 23:21] Bugs System
Pushed into 5.1.22-beta
[21 Aug 2007 23:22] Bugs System
Pushed into 5.0.48
[27 Aug 2007 13:07] Paul Dubois
Pushed to 5.1.23, not 5.1.22.
[31 Aug 2007 1:51] Paul Dubois
Noted in 5.0.48, 5.1.23 changelogs.

Using HANDLER to open a table having a storage engine not supported
by HANDLER properly returned an error, but also improperly prevented
the table from being dropped by other connections.