Bug #43272 HANDLER SQL command does not work under LOCK TABLES
Submitted: 27 Feb 2009 22:22 Modified: 7 Mar 2010 12:50
Reporter: Konstantin Osipov (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Errors Severity:S3 (Non-critical)
Version:3.23, 4.1, 5.0, 5.1, 6.0 bzr OS:Any
Assigned to: Jon Olav Hauglid CPU Architecture:Any

[27 Feb 2009 22:22] Konstantin Osipov
Description:
It seems you can't use HANDLER under LOCK TABLES.

Either I'm doing something wrong, or it never worked.
I tested as early as in 4.1. Since the feature could have been broken in 4.1 without anyone noticing, it should be tested in 4.0 and 3.23 whether it's a regression or not.

How to repeat:
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a int);
LOCK TABLE t1 WRITE;
HANDLER t1 OPEN;

Produces:

mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE t1 (a int);
Query OK, 0 rows affected (0.01 sec)

mysql> LOCK TABLE t1 WRITE;
Query OK, 0 rows affected (0.00 sec)

mysql> HANDLER t1 OPEN;
ERROR 1100 (HY000): Table 't1' was not locked with LOCK TABLE

Suggested fix:
If it never worked, we need to a) Produce a more meaningful error message (ER_LOCK_OR_ACTIVE_TRANSACTION), b) document the limitation.
[28 Feb 2009 16:47] Sveta Smirnova
Thank you for the report.

Version 3.23 fails with error message " 1100: Table 't1' was not locked with LOCK TABLES " as well. So bug can be verified as "please, fix the error message"
[25 Aug 2009 16:38] Konstantin Osipov
Jon Olav, what needs to be done here:
- just produce a nice error.
In SQLCOM_HANDLER_* commands, add a check for lock tables mode, and produce ER_LOCK_OR_ACTIVE_TRANSACTION if in this mode.
[25 Aug 2009 16:39] Konstantin Osipov
We need to close this hole to be able to safely fix 46224 only for non-lock-tables mode.
[26 Aug 2009 9:06] 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/81579

2877 Jon Olav Hauglid	2009-08-26
      Bug #43272 HANDLER SQL command does not work under LOCK TABLES
      
      HANDLER commands are now explicitly disallowed in LOCK TABLES mode.
      
      Before, HANDLER OPEN gave the misleading error message: "Table x was 
      not locked with LOCK TABLES". This patch changes HANDLER OPEN/READ/CLOSE 
      to give ER_LOCK_OR_ACTIVE_TRANSACTION "Can't execute the given command 
      because you have active locked tables or an active transaction" in
      LOCK TABLES mode.
      
      Test case added to lock.test.
[31 Aug 2009 14:03] 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/82015

2804 Jon Olav Hauglid	2009-08-31
      Bug #43272 HANDLER SQL command does not work under LOCK TABLES
      
      HANDLER commands are now explicitly disallowed in LOCK TABLES mode.
      
      Before, HANDLER OPEN gave the misleading error message: "Table x was 
      not locked with LOCK TABLES". This patch changes HANDLER OPEN/READ/CLOSE 
      to give ER_LOCK_OR_ACTIVE_TRANSACTION "Can't execute the given command 
      because you have active locked tables or an active transaction" in
      LOCK TABLES mode.
      
      Test case added to lock.test.
[31 Aug 2009 14:04] Jon Olav Hauglid
Pushed to mysql-next-bugfixing (Version 5.4.4-alpha)
[16 Sep 2009 11:15] Jon Olav Hauglid
Pushed to mysql-next (now mysql-6.0-codebase).
[23 Sep 2009 20:07] Paul Dubois
Noted in 5.4.4, 6.0.14 changelogs.

HANDLER statements are now disallowed if a table lock has been
acquired with LOCK TABLES.
[8 Dec 2009 14:56] Jon Olav Hauglid
Pushed to mysql-next-4284 (5.6.0-beta).
[16 Feb 2010 16:46] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100216101445-2ofzkh48aq2e0e8o) (version source revid:kostja@sun.com-20091211154405-c9yhiewr9o5d20rq) (merge vers: 6.0.14-alpha) (pib:16)
[16 Feb 2010 16:56] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100216101208-33qkfwdr0tep3pf2) (version source revid:jon.hauglid@sun.com-20091208145606-1wvks70258d7u4ow) (pib:16)
[17 Feb 2010 0:37] Paul Dubois
Setting report to Need Merge pending push of Celosia into release tree.
[6 Mar 2010 10:52] Bugs System
Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:vvaintroub@mysql.com-20100216221947-luyhph0txl2c5tc8) (merge vers: 5.5.99-m3) (pib:16)
[7 Mar 2010 12:50] Paul Dubois
Noted in 5.5.3 changelog.