MySQL Bugs: #64057: Concurrent show tables request violates isolation of multi-view drop request

Bug #64057	Concurrent show tables request violates isolation of multi-view drop request
Submitted:	18 Jan 2012 13:58	Modified:	19 Jan 2012 17:17
Reporter:	Pedro Fonseca	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S4 (Feature request)
Version:	5.5.19	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
We're using mysqld version 5.5.19 built from source. We found a concurrency bug in MyISAM storage engine that violates the isolation of a multi-view drop request. The bug is triggered when the drop view request is concurrently executed with a show tables request. We've also seen this bug in MySQL version 5.5.5-m3.

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

This is an example of a sequence of requests that can, under some thread interleavings, expose the bug:

Client A:
  create database new_db;
  use new_db;
  create table t1 (a int);
  insert into t1 values (289), (298), (234), (456), (789);
  create definer = CURRENT_USER view v1 as select * from t1;
  create definer = CURRENT_USER view v2 as select * from t1;
  create definer = CURRENT_USER view v3 as select * from t1;
  create definer = CURRENT_USER view v4 as select * from t1;

Client B:
  use new_db;

Client A (concurrently with Client B):
  drop view v1, v2, v3, v4;

Client B (concurrently with Client A):
  show tables;

When triggered, the bug manifests itself to Client B by having the show tables request return a list that is missing some of the views but not all. An example of a buggy output is:
    Tables_in_new_db
    t1
    v2
    v3
    v4

While the expected output would be either:
    Tables_in_new_db
    t1
    v1
    v2
    v3
    v4

Or the other alternative would be:
    Tables_in_new_db
    t1

Suggested fix:

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

You issue query drop view v1, v2, v3, v4; This is not atomic operation and views are getting dropped one-by-one. So this is expected you could see v1 is dropped while others are not yet.

Are you sure the MyISAM's Drop View request is not supposed to be an atomic operation? 

If I understand correctly, the official documentation in section "1.8.5.3. Transaction and Atomic Operation Differences" seems to inform that in MyISAM statements are in general atomic operations. While the documentation section specific to the Drop View request ("12.1.31. DROP VIEW Syntax" - http://dev.mysql.com/doc/refman/5.5/en/drop-view.html) does not inform users that such requests are not atomic.

But perhaps I'm missing something. If this is the case could you please specify where in the documentation users are informed about the non-atomicity of the Drop View requests?

Thank you for the feedback.

Storage engines are not related to this query. I agree user can expect DROP VIEW for multiple views will provide consistency, but now this query just drops views one-by-one and you can see it in parallel connection. Verifying as feature request.

To repeat.

Modify sources as follow:

=== modified file 'sql/sql_view.cc'
--- sql/sql_view.cc	2011-10-06 10:49:58 +0000
+++ sql/sql_view.cc	2012-01-19 17:06:23 +0000
@@ -1693,6 +1693,7 @@
 
   for (view= views; view; view= view->next_local)
   {
+sleep(5);
     frm_type_enum type= FRMTYPE_ERROR;
     build_table_filename(path, sizeof(path) - 1,
                          view->db, view->table_name, reg_ext, 0);

Run MTR test case:

 create table t1 (a int);
  insert into t1 values (289), (298), (234), (456), (789);
  create definer = CURRENT_USER view v1 as select * from t1;
  create definer = CURRENT_USER view v2 as select * from t1;
  create definer = CURRENT_USER view v3 as select * from t1;
  create definer = CURRENT_USER view v4 as select * from t1;

--send drop view v1, v2, v3, v4

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

sleep 7;

show tables;