Bug #31816 Dropping a table removes the 'write' lock on another table in the same database

• Submitted: 24 Oct 2007 14:51
• Modified: 24 Oct 2007 16:22
• Reporter: Himanshu Bhutani
• Status: Can't repeat
• Category: MySQL Server: MyISAM storage engine
• Severity: S2 (Serious)
• Version: 5.1.14 Beta
• OS: Any
• CPU Architecture: Any

[24 Oct 2007 14:51] Himanshu Bhutani

Description:
Running a query with union on two tables (one of which has a write lock) and then dropping one of the tables in another session causes the query to return with a 'table doesn't exist' error.  This is as expected, however, if you try to query the locked table, MyISAM engine returns the results, even though that table was never unlocked. 

How to repeat:
Connection 1:

lock table T2 write;

Connection 2:

select * from T1 union select * from T2; (Query will block since T2 is locked)

Connection 1:

drop table T1;

Connection 2:
1) Query returns an error saying "Table T1 doesn't exist"
2) Run the query "select * from T2" -> Query displayed Results

Suggested fix:
The last query mentioned above should still be blocked since table T2 was never unlocked.

[24 Oct 2007 16:22] MySQL Verification Team

Thank you for the bug report. I can't repeat with today source server please
upgrade to latest released version or wait the next release.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.1.23-beta-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql2>select * from t1 union select * from t2;
ERROR 1146 (42S02): Table 'db9.t1' doesn't exist
mysql2>select * from t2;

mysql1>drop table t1;
Query OK, 0 rows affected (0.00 sec)

mysql1>show processlist;
+----+------+-----------+------+---------+------+--------+------------------+
| Id | User | Host      | db   | Command | Time | State  | Info             |
+----+------+-----------+------+---------+------+--------+------------------+
|  5 | root | localhost | db9  | Query   |    0 | NULL   | show processlist | 
|  6 | root | localhost | db9  | Query   |  136 | Locked | select * from t2 | 
+----+------+-----------+------+---------+------+--------+------------------+
2 rows in set (0.00 sec)