Bug #58927 LOCK TABLES > CREATE TABLE [LIKE] behavior in 5.5 to be made configurable

• Submitted: 14 Dec 2010 20:03
• Reporter: Roel Van de Paar
• Status: Verified
• Category: MySQL Server: Locking
• Severity: S4 (Feature request)
• Version: 5.5.7rc
• OS: Any
• CPU Architecture: Any

[14 Dec 2010 20:03] Roel Van de Paar

Description:
The following incompatible change was introduced in 5.5:

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

mysql> CREATE TABLE t2(id int);
ERROR 1100 (HY000): Table 't2' was not locked with LOCK TABLES

The same succeeds in 5.1 fine.

Reason:

http://lists.mysql.com/commits/79765
 The patch prohibits CREATE TABLE and CREATE TABLE LIKE under
 LOCK TABLES. Note that this is an incompatible change and must
 be reflected in the documentation. Affected test cases have been
 updated.

Which was the result of a fix for bug #42546

This was now added to the manual (see bug #58909 and bug #42546).

However, many may run into this incompatible change.

How to repeat:
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
CREATE TABLE t1 (id int);
LOCK TABLES t1 WRITE;
CREATE TABLE t2(id int);
UNLOCK TABLES;

Suggested fix:
This is a feature request to make the locking behavior configurable in this case so that one can select either the old 5.1 or the new 5.5 behavior.

Workaround:

CREATE TABLE to_be_renamed (id int);
LOCK TABLES to_be_renamed WRITE;
ALTER TABLE to_be_renamed RENAME final_name;
UNLOCK TABLES;

[14 Dec 2010 20:21] Roel Van de Paar

More elaborate workaround example with two tables:

CREATE TABLE t1 (id int);
CREATE TABLE to_be_renamed (final_columns int);
LOCK TABLES t1 WRITE, to_be_renamed WRITE;
ALTER TABLE to_be_renamed RENAME final_name;
UNLOCK TABLES;

[14 Dec 2010 20:24] MySQL Verification Team

Startup variable makes sense, but it would have to be global-only and NOT on-line, as too many mutexes and too much code would be affected .....

[7 Feb 2011 21:12] Sveta Smirnova

See bug #59999 for real-life problem with this behavior.