Bug #64814 Inconsistent error when concurrently dropping table and trigger
Submitted: 30 Mar 2012 12:26 Modified: 31 Mar 2012 9:17
Reporter: Pedro Fonseca Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Locking Severity:S3 (Non-critical)
Version:5.5.19 OS:Any
Assigned to: CPU Architecture:Any
Triage: Needs Triage: D2 (Serious)

[30 Mar 2012 12:26] Pedro Fonseca
Description:
We're using mysqld version 5.5.19 built from source. We found a
concurrency bug in MyISAM storage engine that leads to inconsistent errors. The bug is triggered when a drop table request is concurrently executed, by a different client, with a drop trigger request. Usually the drop trigger succeeds or gives an error indicating that the trigger does not exist. However, sometimes it gives an error stating that the table does not exist.

How to repeat:
This is a non-deterministic bug that is triggered only in a very small
number of executions. We used the following server command line options
to enable MyISAM and to disable InnoDB storage engine:
  --default-storage-engine=myisam --innodb=OFF

The following requests trigger the bug under some executions:

Client A: 
       create database new_db;
       use new_db;
       create table t1 (x1col char);
       create trigger tx1 before insert on t1 for each row set new.x1col = 'x';

Client B: 
       use new_db;

Client A (concurrently with Client B):
       drop trigger tx1

Client B (concurrently with Client A):
       drop table t1;

The usual behavior is for client A to either:
    a) Successfully drop the trigger
    b) Or get the error "ERROR 1360 (HY000) at line 5: Trigger does not exist"

However we found that in a small fraction of the executions client A gets a different error. This error, instead of being about the trigger, is about the *table* not existing:
   "ERROR 1146 (42S02) at line 5: Table 'new_db.t1' doesn't exist"

This inconsistency in the error messages breaks the isolation that programmers would expect from the database and ends up revealing that the "drop table" operation is in progress.
[31 Mar 2012 9:17] Sveta Smirnova
Thank you for the report.

Verified as described.

To repeat:

1. Modify 5.5 sources as follow:

[sveta@delly mysql-5.5]$ bzr diff
=== modified file 'sql/sql_handler.cc'
--- sql/sql_handler.cc	2011-06-30 15:46:53 +0000
+++ sql/sql_handler.cc	2012-03-31 09:08:36 +0000
@@ -862,6 +862,7 @@
     my_hash_delete(&thd->handler_tables_hash, (uchar*) hash_tables);
     hash_tables= next;
   }
+sleep(2);
 
   /*
     Mark MDL_context as no longer breaking protocol if we have

2. Run following MTR test:

create table t1 (x1col char);
create trigger tx1 before insert on t1 for each row set new.x1col = 'x';

--send drop table t1;

sleep 1;

connect(addconroot, localhost, root,,);
connection addconroot;

drop trigger tx1;
[31 Mar 2012 9:27] Sveta Smirnova
This is not MyISAM issue: inconsistency happens before any storage engine involved. Error can be easily repeated if use InnoDB storage engine.
[31 Mar 2012 9:29] Sveta Smirnova
I think this can be considered as a bug only in those versions which have metadata locking (5.5 and up).