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
Triage: Triaged: D4 (Minor) / R3 (Medium) / E3 (Medium)

[15 Dec 2007 9:06] Giuseppe Maxia
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;
[15 Dec 2007 12:26] Jim Starkey
Falcon does not permit a table to be dropped with uncommitted records.  To do so break repeatable read on outstanding transactions.
[16 Dec 2007 19:42] Giuseppe Maxia
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.
[16 Dec 2007 20:00] Jim Starkey
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.
[16 Dec 2007 20:42] Giuseppe Maxia
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.
[17 Dec 2007 16:47] Peter Gulutzan
See also Bug#12347 InnoDB: DROP TABLE ignores locks
[17 Dec 2007 23:02] Miguel Solorzano
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>
[16 Mar 2008 17:04] Philip Stoev
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.
[17 Mar 2008 19:24] Kevin Lewis
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.
[28 Mar 2008 9:33] Vladislav Vaintroub
Patch proposal

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

[28 Mar 2008 9:38] Vladislav Vaintroub
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.
[28 Mar 2008 21:04] Kevin Lewis
Sergey,  Can you take a look at this?  Vlad has a suggested fix.
[4 Jun 2008 14:08] Ann Harrison
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.