MySQL Bugs: #24918: drop table and lock / inconsistent between perm and temp tables

Bug #24918	drop table and lock / inconsistent between perm and temp tables
Submitted:	8 Dec 2006 17:20	Modified:	3 Aug 2007 1:40
Reporter:	Martin Friebe (Gold Quality Contributor) (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S1 (Critical)
Version:	4.1.22, 4.1.23-BK, 5.0.27	OS:	Linux (Linux, freebsd)
Assigned to:	Konstantin Osipov	CPU Architecture:	Any
Tags:	drop table, lock, temporary table

Description:
A drop table on a normal 9myisam) table releases any lock the client may have to it.

This does not apply to temporary tables, droping a temp table leaves the lock in place.

See how to repeat.

In the 1st case, after droping the temp table, mysql will return an error "table was not locked" for both selects (on l1 and l2).
The error for select on the non existing table l2, is even wrong.

In the 2nd case droping the normal table l2, mysql releases the lock

In the 3rd case droping a real table (but out of 2 locked tables) mysql keeps the lock.
And again selecting from a non existing table returns the wrong error

How to repeat:
drop table if exists l1;
drop table if exists l2;

create table l1 (a int);

create temporary table l2 (a int);
lock table l2 write;
select * from l2;
drop temporary table l2;
select * from l2;
select * from l1;
unlock tables;
select * from l1;

create table l2 (a int);
lock table l2 write;
select * from l2;
drop table l2;
select * from l1;
unlock tables;

Suggested fix:
this sbehaviour is not consistent:

should mysql drop locks, with the tables?, if so should it drop all locks, or just the ones you drop tables ?

Thank you for a bug report. Verified just as described with 4.1.23-BK on Linux:

mysql> select version();
+--------------+
| version()    |
+--------------+
| 4.1.23-debug |
+--------------+
1 row in set (0.02 sec)

mysql> drop table if exists l1;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> drop table if exists l2;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table l1 (a int);
Query OK, 0 rows affected (0.02 sec)

mysql> create temporary table l2 (a int);
lQuery OK, 0 rows affected (0.01 sec)

mysql> lock table l2 write;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from l2;
Empty set (0.00 sec)

mysql> drop temporary table l2;
Query OK, 0 rows affected (0.00 sec)

smysql> select * from l2;
ERROR 1100 (HY000): Table 'l2' was not locked with LOCK TABLES
mysql> select * from l1;
ERROR 1100 (HY000): Table 'l1' was not locked with LOCK TABLES
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

smysql> select * from l1;
Empty set (0.00 sec)

mysql> create table l2 (a int);
loQuery OK, 0 rows affected (0.02 sec)

mysql> lock table l2 write;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from l2;
Empty set (0.00 sec)

mysql> drop table l2;
Query OK, 0 rows affected (0.00 sec)

smysql> select * from l1;
Empty set (0.00 sec)

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

Here is another test case:

mysql> create temporary table t7 (s1 char(5)) engine=innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> lock tables t7 read;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table t7;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t7 (s1 char(5)) engine=innodb;
ERROR 1005 (HY000): Can't create table 'd10.t7' (errno: 121)

mysql> lock tables t7 read;
ERROR 2013 (HY000): Lost connection to MySQL server during query

As suggested by Peter's test case, it is possible to crash the server using a very simple series of create/lock statements.

SQL test case:

create temporary table t7 (s1 char(5)) engine=innodb;
lock tables t7 read;
drop table t7;
create table t7 (s1 char(5)) engine=innodb;
lock tables t7 read;

See also Bug#21431 "Incomplete support of temporary tables"

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/31370

ChangeSet@1.2477, 2007-07-22 18:41:27+04:00, kostja@bodhi.(none) +9 -0
  A fix and a test case for Bug#24918 drop table and lock / inconsistent
  between perm and temp tables
  
  The original bug report complains that if we locked a temporary table
  with LOCK TABLES statement, we would not leave LOCK TABLES mode
  if this temporary table is dropped.
  
  Additionally, the bug was escalated when it was discovered than
  when a temporary transactional table that was previously
  locked with LOCK TABLES statement was dropped, futher actions with
  this table, such as UNLOCK TABLES, would lead to a crash.
  
  The problem originates from incomplete support of transactional temporary
  tables. When we added calls to handler::store_lock()/handler::external_lock()
  to operations that work with such tables, we only covered the normal
  server code flow and did not cover LOCK TABLES mode. 
  In LOCK TABLES mode, ::external_lock(LOCK) would sometimes be called without
  matching ::external_lock(UNLOCK), e.g. when a transactional temporary table
  was dropped. Additionally, this table would be left in the list of LOCKed 
  TABLES.
  
  The patch aims to address this inadequacy. Now, whenever an instance
  of 'handler' is destroyed, we assert that it was priorly
  external_lock(UNLOCK)-ed. All the places that violate this assert
  were fixed.
  
  This patch introduces no changes in behavior -- the discrepancy in
  behavior will be fixed when we start calling ::store_lock()/::external_lock()
  for all tables, regardless whether they are transactional or not, 
  temporary or not.

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/31416

ChangeSet@1.2477, 2007-07-22 22:34:53+04:00, kostja@bodhi.(none) +9 -0
  A fix and a test case for Bug#24918 drop table and lock / inconsistent
  between perm and temp tables
  
  The original bug report complains that if we locked a temporary table
  with LOCK TABLES statement, we would not leave LOCK TABLES mode
  if this temporary table is dropped.
  
  Additionally, the bug was escalated when it was discovered than
  when a temporary transactional table that was previously
  locked with LOCK TABLES statement was dropped, futher actions with
  this table, such as UNLOCK TABLES, would lead to a crash.
  
  The problem originates from incomplete support of transactional temporary
  tables. When we added calls to handler::store_lock()/handler::external_lock()
  to operations that work with such tables, we only covered the normal
  server code flow and did not cover LOCK TABLES mode. 
  In LOCK TABLES mode, ::external_lock(LOCK) would sometimes be called without
  matching ::external_lock(UNLOCK), e.g. when a transactional temporary table
  was dropped. Additionally, this table would be left in the list of LOCKed 
  TABLES.
  
  The patch aims to address this inadequacy. Now, whenever an instance
  of 'handler' is destroyed, we assert that it was priorly
  external_lock(UNLOCK)-ed. All the places that violate this assert
  were fixed.
  
  This patch introduces no changes in behavior -- the discrepancy in
  behavior will be fixed when we start calling ::store_lock()/::external_lock()
  for all tables, regardless whether they are transactional or not, 
  temporary or not.

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/31505

ChangeSet@1.2477, 2007-07-24 22:52:18+04:00, kostja@bodhi.(none) +9 -0
  A fix and a test case for Bug#24918 drop table and lock / inconsistent
  between perm and temp tables
  
  The original bug report complains that if we locked a temporary table
  with LOCK TABLES statement, we would not leave LOCK TABLES mode
  when this temporary table is dropped.
  
  Additionally, the bug was escalated when it was discovered than
  when a temporary transactional table that was previously
  locked with LOCK TABLES statement was dropped, futher actions with
  this table, such as UNLOCK TABLES, would lead to a crash.
  
  The problem originates from incomplete support of transactional temporary
  tables. When we added calls to handler::store_lock()/handler::external_lock()
  to operations that work with such tables, we only covered the normal
  server code flow and did not cover LOCK TABLES mode. 
  In LOCK TABLES mode, ::external_lock(LOCK) would sometimes be called without
  matching ::external_lock(UNLOCK), e.g. when a transactional temporary table
  was dropped. Additionally, this table would be left in the list of LOCKed 
  TABLES.
  
  The patch aims to address this inadequacy. Now, whenever an instance
  of 'handler' is destroyed, we assert that it was priorly
  external_lock(UNLOCK)-ed. All the places that violate this assert
  were fixed.
  
  This patch introduces no changes in behavior -- the discrepancy in
  behavior will be fixed when we start calling ::store_lock()/::external_lock()
  for all tables, regardless whether they are transactional or not, 
  temporary or not.

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/31694

ChangeSet@1.2482, 2007-07-27 16:37:29+04:00, kostja@bodhi.(none) +9 -0
  A fix and a test case for Bug#24918 drop table and lock / inconsistent
  between perm and temp tables. Review fixes.
  
  The original bug report complains that if we locked a temporary table
  with LOCK TABLES statement, we would not leave LOCK TABLES mode
  when this temporary table is dropped.
  
  Additionally, the bug was escalated when it was discovered than
  when a temporary transactional table that was previously
  locked with LOCK TABLES statement was dropped, futher actions with
  this table, such as UNLOCK TABLES, would lead to a crash.
  
  The problem originates from incomplete support of transactional temporary
  tables. When we added calls to handler::store_lock()/handler::external_lock()
  to operations that work with such tables, we only covered the normal
  server code flow and did not cover LOCK TABLES mode. 
  In LOCK TABLES mode, ::external_lock(LOCK) would sometimes be called without
  matching ::external_lock(UNLOCK), e.g. when a transactional temporary table
  was dropped. Additionally, this table would be left in the list of LOCKed 
  TABLES.
  
  The patch aims to address this inadequacy. Now, whenever an instance
  of 'handler' is destroyed, we assert that it was priorly
  external_lock(UNLOCK)-ed. All the places that violate this assert
  were fixed.
  
  This patch introduces no changes in behavior -- the discrepancy in
  behavior will be fixed when we start calling ::store_lock()/::external_lock()
  for all tables, regardless whether they are transactional or not, 
  temporary or not.

The patch for the crash was queued in 5.0-runtime.
The inconsistency between temporary and premanent tables is not fixed - to be fixed when we start calling ::external_lock() for all tables.

Pushed into 5.0.48

Pushed into 5.1.21-beta

A server crash was fixed when one would drop a temporary InnoDB table locked with LOCK TABLES

Noted in 5.0.48, 5.1.21 changelogs.

Dropping a temporary InnoDB table that had been locked with LOCK 
TABLES caused a server crash.