Bug #48173 Unable to drop index while innodb table is locked.
Submitted: 20 Oct 2009 0:25 Modified: 20 Oct 2009 19:51
Reporter: Dave Juntgen Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:Ver 14.12 Distrib 5.0.79, 5.0.85 OS:Linux
Assigned to: Paul DuBois CPU Architecture:Any
Tags: innodb

[20 Oct 2009 0:25] Dave Juntgen
Description:
Create a table with engine=innodb, add two indexes.
lock the table, drop the first and then the second index.  the second index will fail stating that table `t` was not locked.

mysql> create table t (a int, b int, key idx1(a,b), key idx2(b)) engine=innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t values(1,1),(1,2),(2,1),(2,2);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

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

mysql> alter table t drop index idx1;
Query OK, 4 rows affected (1.04 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> alter table t drop index idx2;
ERROR 1100 (HY000): Table 't' was not locked with LOCK TABLES

mysql> alter table t add primary key (a,b);
ERROR 1100 (HY000): Table 't' was not locked with LOCK TABLES

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

How to repeat:
create table t (a int, b int, key idx1(a,b), key idx2(b)) engine=innodb;
insert into t values(1,1),(1,2),(2,1),(2,2);
lock table t write;
alter table t drop index idx1;
alter table t drop index idx2;
alter table t add primary key (a,b);
unlock tables;
[20 Oct 2009 6:23] Valeriy Kravchuk
I can confirm this on newer 5.x versions also:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 35
Server version: 5.0.85-community-nt MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop table t;
ERROR 1051 (42S02): Unknown table 't'
mysql> create table t (a int, b int, key idx1(a,b), key idx2(b)) engine=innodb;
Query OK, 0 rows affected (0.28 sec)

mysql> insert into t values(1,1),(1,2),(2,1),(2,2);
Query OK, 4 rows affected (0.08 sec)
Records: 4  Duplicates: 0  Warnings: 0

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

mysql> alter table t drop index idx1;
Query OK, 4 rows affected (0.50 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> alter table t drop index idx2;
ERROR 1100 (HY000): Table 't' was not locked with LOCK TABLES
mysql> alter table t add primary key (a,b);
ERROR 1100 (HY000): Table 't' was not locked with LOCK TABLES
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

But I think this is actually intended and expected. The first ALTER TABLE does implicit COMMIT and remove table lock at InnoDB level. But table lock at server level remains. Next ALTER checks that there is no lock of that table at InnoDB level (while there were no UNLOCK TABLES) and returns error message.

Still, I'd like to see this explicitly documented and explained with example in the manual, http://dev.mysql.com/doc/refman/5.0/en/lock-tables-and-transactions.html.
[20 Oct 2009 15:39] Dave Juntgen
Agreed - but the error from MySQL seems confusing to me.  I now understand why its happening, but maybe a more appropriate error from MySQL is the fix.

Something like: "Lock on table `t` was implicitly release because of trx engine on table."
[20 Oct 2009 19:44] Paul DuBois
http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html says:

"Note
If you use ALTER TABLE on a locked table, it may become unlocked."

If you re-lock the table after each ALTER TABLE, the errors go away. But I agree that the error message is confusing. I will add something to the note pointing out the error that occurs and that you can re-lock the table to handle the situation.
[20 Oct 2009 19:51] Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.