MySQL Bugs: #43272: HANDLER SQL command does not work under LOCK TABLES

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

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.

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"

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.

We need to close this hole to be able to safely fix 46224 only for non-lock-tables mode.

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.

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.

Pushed to mysql-next-bugfixing (Version 5.4.4-alpha)

Pushed to mysql-next (now mysql-6.0-codebase).

Noted in 5.4.4, 6.0.14 changelogs.

HANDLER statements are now disallowed if a table lock has been
acquired with LOCK TABLES.

Pushed to mysql-next-4284 (5.6.0-beta).

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)

Pushed into mysql-next-mr (revid:alik@sun.com-20100216101208-33qkfwdr0tep3pf2) (version source revid:jon.hauglid@sun.com-20091208145606-1wvks70258d7u4ow) (pib:16)

Setting report to Need Merge pending push of Celosia into release tree.

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)

Noted in 5.5.3 changelog.