MySQL Bugs: #33258: Misleading Error when DROP TABLE operation while a transaction is open

Bug #33258	Misleading Error when DROP TABLE operation while a transaction is open
Submitted:	15 Dec 2007 9:06	Modified:	26 May 2010 17:49
Reporter:	Giuseppe Maxia	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S3 (Non-critical)
Version:	6.0.5	OS:	Any
Assigned to:	Sergey Vojtovich	CPU Architecture:	Any
Tags:	alter, autocommit, Drop, error handling, F_HANDLER

Description:
(Tested on ChangeSet@1.2765, 2007-12-14 18:06:05+01:00)

While a transaction is active, Falcon does not allow a DROP TABLE operation from a concurrent session.

#session 1
set autocommit=0;
create table t1 (id int) engine= falcon;
insert into t1 values (1);

#session 2
show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             | 
+----------------+
drop table t1;
ERROR 1051 (42S02): Unknown table 't1'

alter table t1 engine=myisam;
ERROR 1015 (HY000): Can't lock file (errno: 212)

Notice that using ENGINE=InnoDB, the DROP TABLE succeds, while the ALTER TABLE waits for the end of the transaction.

How to repeat:
--source include/have_falcon.inc
--disable_warnings
drop table if exists t1;
--enable_warnings

connect (conn1,localhost,root,,);
connection default;
set autocommit=0;
create table t1 (id int) engine = falcon;
insert into t1 values (1);

connection conn1;
show tables;
drop table t1;

Falcon does not permit a table to be dropped with uncommitted records.  To do so break repeatable read on outstanding transactions.

According to the manual:

http://dev.mysql.com/doc/refman/6.0/en/drop-table.html
"DROP TABLE automatically commits the current active transaction, unless you use the TEMPORARY keyword."
There is no mention of rejecting DROP TABLE requests.

Either the documentation is wrong, or Falcon behavior is wrong.
If Falcon behavior is right, and the documentation is updated to that extent, then InnoDB's behavior should be consider wrong, and thus a bug.

At the very minimum, Falcon must give an informative error message when it is going to reject a DROP TABLE or ALTER TABLE request.

The documentation says nothing about what happens to other concurrent transactions.  While the server may indeed to an automatic commit for the user doing a drop table, there is nothing that says what the effect on other transactions, if any, may be.  Frankly, preventing a table actively in use is more likely to be seen error prevention than a bug.  Imagine what the effect would be on air travel if an airline could drop an airplane in flight!

I agree that the server message is incorrect and misleading, but this is a server, not Falcon, issue.  I suggest you post a bug against the server.

Reopened as a General Server issue.

This should be fixed somehow.

* There must be a better error message;
* The documentation must note the different behavior of Falcon in this case.

See also Bug#12347 InnoDB: DROP TABLE ignores locks

Thank you for the bug report.

C:\dbs>6.0t\bin\mysql -uroot --prompt="conn1>" test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 6.0.5-alpha-team-tree-nt Source distribution

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

conn1>set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

conn1>create table t1 (id int) engine= falcon;
Query OK, 0 rows affected (0.38 sec)

conn1>insert into t1 values (1);
Query OK, 1 row affected (0.14 sec)

c:\dbs>6.0t\bin\mysql -uroot --prompt="conn2>" test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 6.0.5-alpha-team-tree-nt Source distribution

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

conn2>show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
+----------------+
1 row in set (0.00 sec)

conn2>drop table t1;
ERROR 1051 (42S02): Unknown table 't1'
conn2>alter table t1 engine=myisam;
ERROR 1015 (HY000): Can't lock file (errno: 212)
conn2>

I think the priority of this should be kicked up a bit. I ran into a situation with heavy updates where I could not get an ALTER TABLE through no matter how many times I tried.

Sometimes ALTER TABLE ADD KEY would be the only way to relieve a loaded server, and if the administrator is unable to issue one, it can be extremly frustrating.

I think ALTER TABLE should block until the table becomes available in a manner that allows it to eventually complete and never be starved forever.

I am going to try to summarize what this bug needs;

It is similar to Bug#12347 where Peter says; "Since a DROP implicitly deletes all rows, anything that blocks a DELETE should block a DROP."  InnoDB does not block the DROP TABLE (#12347), but Falcon does (which is correct).  

This bug is against Falcon because the error message returned to the user is "Unknown table 't1'" which is misleading.  Also, Giuseppe would like the documentation to point out that only InnoDB allows a table with an active transaction to be dropped, while Falcon works correctly and prevents it.

I am putting this bug back to the Falcon Storage Engine so that we can fix the error message. 

Fixing InnoDB to block is Bug#12347.

Patch proposal

Attachment: patch.txt (text/plain), 769 bytes.

Kevin, we can't fix  that in Falcon, as we already return ER_LOCK_OR_ACTIVE_TRANSACTION on drop in this case,server just needs to handle it correctly. Could you assign this bug to the correct team?
I also attached a patch proposal for it.

Sergey,  Can you take a look at this?  Vlad has a suggested fix.

FYI, in an MVCC engine, DROP is not equivalent to deleting all rows in
the affected table.  DELETE leaves back versions that maintain the 
consistent snapshot for running transactions.  DROP does not.