bk commit into 5.2 tree (davi:1.2605) BUG#25858

[Davi Arnaut]

Below is the list of changes that have just been committed into a local
5.2 repository of davi. When davi does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2007-09-05 22:37:26-03:00, davi@stripped +19 -0
  Bug#25858 Some DROP TABLE under LOCK TABLES can cause deadlocks
  
  When a client (connection) holds a lock on a table and attempts to
  drop (obtain a exclusive lock) on a second table that is already held
  by a second client and the second client then attempts to drop the table
  that is held by the first client, leads to a circular wait deadlock. This
  scenario is very similar to trying to drop (or rename) a table while
  holding read locks and are correctly forbidden.
  
  The solution is to allow a drop table operation to continue only if the
  table being dropped is write (exclusively) locked, or if the table is
  temporary, or if the client is not holding any locks. Using this scheme
  prevents the creation of a circular chain in which each client is waiting
  for one table that the next client in the chain is holding.
  
  This is incompatible change, as can be seen by number of tests cases
  that needed to be fixed, but is consistent with respect to behavior of
  the different scenarios in which the circular wait might happen.

  mysql-test/include/locktrans.inc@stripped, 2007-09-05 22:37:21-03:00, davi@stripped +1 -0
    Fix test case: lock table for write as the connection is already holding
    other locks.

  mysql-test/r/drop.result@stripped, 2007-09-05 22:37:21-03:00, davi@stripped +20 -0
    Test case result for Bug#25858

  mysql-test/r/group_by.result@stripped, 2007-09-05 22:37:21-03:00, davi@stripped +2 -1
    Fix test case result wrt drop table under lock tables -- unlock tables
    before dropping table.

  mysql-test/r/insert_update.result@stripped, 2007-09-05 22:37:21-03:00, davi@stripped +1
-0
    Fix test case result wrt drop table under lock tables -- unlock tables
    before dropping table.

  mysql-test/r/lock_multi.result@stripped, 2007-09-05 22:37:21-03:00, davi@stripped +1 -0
    Fix test case result wrt drop table under lock tables -- unlock tables
    before dropping table.

  mysql-test/r/locktrans_myisam.result@stripped, 2007-09-05 22:37:21-03:00, davi@stripped
+1 -0
    Fix test case result wrt drop table under lock tables -- grab exclusive
    lock in preparation for drop.

  mysql-test/r/myisam.result@stripped, 2007-09-05 22:37:21-03:00, davi@stripped +2 -0
    Fix test case result wrt drop table under lock tables -- unlock tables
    before dropping table.

  mysql-test/r/query_cache_notembedded.result@stripped, 2007-09-05 22:37:22-03:00,
davi@stripped +1 -0
    Fix test case result wrt drop table under lock tables -- unlock tables
    before dropping table.

  mysql-test/r/rpl_locktrans_innodb.result@stripped, 2007-09-05 22:37:22-03:00,
davi@stripped +1 -0
    Fix test case result wrt drop table under lock tables -- grab exclusive
    lock in preparation for drop.

  mysql-test/r/rpl_locktrans_myisam.result@stripped, 2007-09-05 22:37:22-03:00,
davi@stripped +1 -0
    Fix test case result wrt drop table under lock tables -- grab exclusive
    lock in preparation for drop.

  mysql-test/r/view.result@stripped, 2007-09-05 22:37:22-03:00, davi@stripped +3 -0
    Fix test case result wrt drop table under lock tables -- unlock tables
    before dropping table.

  mysql-test/t/drop.test@stripped, 2007-09-05 22:37:22-03:00, davi@stripped +33 -0
    Add test case for Bug#25858

  mysql-test/t/group_by.test@stripped, 2007-09-05 22:37:22-03:00, davi@stripped +2 -1
    Fix test case: unlock tables in preparation for a drop table. In some
    circumstances, dropping tables while holding locks leads to a deadlock.

  mysql-test/t/insert_update.test@stripped, 2007-09-05 22:37:22-03:00, davi@stripped +1 -0
    Fix test case: unlock tables in preparation for a drop table. In some
    circumstances, dropping tables while holding locks leads to a deadlock.

  mysql-test/t/lock_multi.test@stripped, 2007-09-05 22:37:22-03:00, davi@stripped +1 -0
    Fix test case: unlock tables in preparation for a drop table. In some
    circumstances, dropping tables while holding locks leads to a deadlock.

  mysql-test/t/myisam.test@stripped, 2007-09-05 22:37:22-03:00, davi@stripped +2 -0
    Fix test case: unlock tables in preparation for a drop table. In some
    circumstances, dropping tables while holding locks leads to a deadlock.

  mysql-test/t/query_cache_notembedded.test@stripped, 2007-09-05 22:37:22-03:00,
davi@stripped +1 -0
    Fix test case: unlock tables in preparation for a drop table. In some
    circumstances, dropping tables while holding locks leads to a deadlock.

  mysql-test/t/view.test@stripped, 2007-09-05 22:37:22-03:00, davi@stripped +3 -0
    Fix test case: unlock tables in preparation for a drop table. In some
    circumstances, dropping tables while holding locks leads to a deadlock.

  sql/sql_table.cc@stripped, 2007-09-05 22:37:22-03:00, davi@stripped +71 -4
    Scan the locked tables list to verify if the tables being dropped are
    properly exclusively locked, but only if we are holding any locks.
    
    As a minor cleanup, remove an unneeded assigment of the error variable
    and else branch (if the first branch succeeds, the function returns).

diff -Nrup a/mysql-test/include/locktrans.inc b/mysql-test/include/locktrans.inc
--- a/mysql-test/include/locktrans.inc	2007-05-30 04:40:39 -03:00
+++ b/mysql-test/include/locktrans.inc	2007-09-05 22:37:21 -03:00
@@ -77,6 +77,7 @@ LOCK TABLE information_schema.tables WRI
 --echo #
 --echo # The new keywords EXCLUSIVE and NOWAIT are not reserved words.
 eval CREATE TABLE t4 (exclusive INT, nowait INT) ENGINE=$engine_type;
+LOCK TABLE t4 WRITE;
 DROP TABLE t4;
 #
 --echo #
diff -Nrup a/mysql-test/r/drop.result b/mysql-test/r/drop.result
--- a/mysql-test/r/drop.result	2006-11-21 18:42:11 -02:00
+++ b/mysql-test/r/drop.result	2007-09-05 22:37:21 -03:00
@@ -84,4 +84,24 @@ select 1;
 1
 1
 unlock tables;
+drop table if exists t1,t2;
+create table t1 (a int);
+create table t2 (a int);
+lock table t1 read;
+drop table t2;
+ERROR HY000: Table 't2' was not locked with LOCK TABLES
+drop table t1;
+ERROR HY000: Table 't1' was locked with a READ lock and can't be updated
+unlock tables;
+drop table t1,t2;
+create table t1 (i int);
+create table t2 (i int);
+lock tables t1 read;
+lock tables t2 read;
+drop table t1;
+ERROR HY000: Table 't1' was not locked with LOCK TABLES
+drop table t1,t2;
+ERROR HY000: Table 't1' was locked with a READ lock and can't be updated
+unlock tables;
+drop table t1,t2;
 End of 5.0 tests
diff -Nrup a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result
--- a/mysql-test/r/group_by.result	2007-08-26 03:11:29 -03:00
+++ b/mysql-test/r/group_by.result	2007-09-05 22:37:21 -03:00
@@ -107,8 +107,9 @@ SELECT cid, CONCAT(firstname, ' ', surna
 cid	CONCAT(firstname, ' ', surname)	COUNT(call_id)
 SELECT HIGH_PRIORITY cid, CONCAT(firstname, ' ', surname), COUNT(call_id) FROM t1 LEFT
JOIN t2 ON cid=contact_id WHERE firstname like '%foo%' GROUP BY cid ORDER BY surname,
firstname;
 cid	CONCAT(firstname, ' ', surname)	COUNT(call_id)
-drop table t1,t2;
+drop table t2;
 unlock tables;
+drop table t1;
 CREATE TABLE t1 (
 bug_id mediumint(9) NOT NULL auto_increment,
 groupset bigint(20) DEFAULT '0' NOT NULL,
diff -Nrup a/mysql-test/r/insert_update.result b/mysql-test/r/insert_update.result
--- a/mysql-test/r/insert_update.result	2007-08-30 16:19:21 -03:00
+++ b/mysql-test/r/insert_update.result	2007-09-05 22:37:21 -03:00
@@ -424,5 +424,6 @@ a	b
 connection: default
 select * from t1;
 a	b
+unlock tables;
 drop table t1;
 set low_priority_updates=default;
diff -Nrup a/mysql-test/r/lock_multi.result b/mysql-test/r/lock_multi.result
--- a/mysql-test/r/lock_multi.result	2007-08-17 11:34:08 -03:00
+++ b/mysql-test/r/lock_multi.result	2007-09-05 22:37:21 -03:00
@@ -27,6 +27,7 @@ update t1,t2 set c=a where b=d;
 select c from t2;
 c
 2
+unlock tables;
 drop table t1;
 drop table t2;
 create table t1 (a int);
diff -Nrup a/mysql-test/r/locktrans_myisam.result b/mysql-test/r/locktrans_myisam.result
--- a/mysql-test/r/locktrans_myisam.result	2007-08-26 04:55:00 -03:00
+++ b/mysql-test/r/locktrans_myisam.result	2007-09-05 22:37:21 -03:00
@@ -57,6 +57,7 @@ ERROR 42000: Access denied for user 'roo
 #
 # The new keywords EXCLUSIVE and NOWAIT are not reserved words.
 CREATE TABLE t4 (exclusive INT, nowait INT) ENGINE=MyISAM;
+LOCK TABLE t4 WRITE;
 DROP TABLE t4;
 #
 # Syntax errors for misspelled modes or left out symbols.
diff -Nrup a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result
--- a/mysql-test/r/myisam.result	2007-06-27 14:47:51 -03:00
+++ b/mysql-test/r/myisam.result	2007-09-05 22:37:21 -03:00
@@ -529,6 +529,7 @@ select straight_join * from t1,t2 force 
 a	a	b
 1	1	1
 2	2	1
+unlock tables;
 drop table t1,t2;
 CREATE TABLE t1 (c1 varchar(250) NOT NULL);
 CREATE TABLE t2 (c1 varchar(250) NOT NULL, PRIMARY KEY (c1));
@@ -542,6 +543,7 @@ INSERT INTO t2 VALUES ('test000001'), ('
 SELECT t1.c1 AS t1c1, t2.c1 AS t2c1 FROM t1, t2
 WHERE t1.c1 = t2.c1 HAVING t1c1 != t2c1;
 t1c1	t2c1
+UNLOCK TABLES;
 DROP TABLE t1,t2;
 CREATE TABLE t1 (`a` int(11) NOT NULL default '0', `b` int(11) NOT NULL default '0',
UNIQUE KEY `a` USING RTREE (`a`,`b`)) ENGINE=MyISAM;
 Got one of the listed errors
diff -Nrup a/mysql-test/r/query_cache_notembedded.result
b/mysql-test/r/query_cache_notembedded.result
--- a/mysql-test/r/query_cache_notembedded.result	2006-11-17 18:28:43 -02:00
+++ b/mysql-test/r/query_cache_notembedded.result	2007-09-05 22:37:22 -03:00
@@ -93,6 +93,7 @@ a
 3
 SELECT * FROM t1;
 a
+UNLOCK TABLES;
 drop table t1;
 flush query cache;
 reset query cache;
diff -Nrup a/mysql-test/r/rpl_locktrans_innodb.result
b/mysql-test/r/rpl_locktrans_innodb.result
--- a/mysql-test/r/rpl_locktrans_innodb.result	2007-06-30 18:25:05 -03:00
+++ b/mysql-test/r/rpl_locktrans_innodb.result	2007-09-05 22:37:22 -03:00
@@ -63,6 +63,7 @@ ERROR 42000: Access denied for user 'roo
 #
 # The new keywords EXCLUSIVE and NOWAIT are not reserved words.
 CREATE TABLE t4 (exclusive INT, nowait INT) ENGINE=InnoDB;
+LOCK TABLE t4 WRITE;
 DROP TABLE t4;
 #
 # Syntax errors for misspelled modes or left out symbols.
diff -Nrup a/mysql-test/r/rpl_locktrans_myisam.result
b/mysql-test/r/rpl_locktrans_myisam.result
--- a/mysql-test/r/rpl_locktrans_myisam.result	2007-08-26 04:55:00 -03:00
+++ b/mysql-test/r/rpl_locktrans_myisam.result	2007-09-05 22:37:22 -03:00
@@ -63,6 +63,7 @@ ERROR 42000: Access denied for user 'roo
 #
 # The new keywords EXCLUSIVE and NOWAIT are not reserved words.
 CREATE TABLE t4 (exclusive INT, nowait INT) ENGINE=MyISAM;
+LOCK TABLE t4 WRITE;
 DROP TABLE t4;
 #
 # Syntax errors for misspelled modes or left out symbols.
diff -Nrup a/mysql-test/r/view.result b/mysql-test/r/view.result
--- a/mysql-test/r/view.result	2007-08-26 02:51:28 -03:00
+++ b/mysql-test/r/view.result	2007-09-05 22:37:22 -03:00
@@ -1104,6 +1104,9 @@ select * from t2;
 ERROR HY000: Table 't2' was not locked with LOCK TABLES
 drop view v1;
 drop table t1, t2;
+ERROR HY000: Table 't1' was locked with a READ lock and can't be updated
+unlock tables;
+drop table t1, t2;
 create table t1 (a int);
 create view v1 as select * from t1 where a < 2 with check option;
 insert into v1 values(1);
diff -Nrup a/mysql-test/t/drop.test b/mysql-test/t/drop.test
--- a/mysql-test/t/drop.test	2006-08-21 05:18:56 -03:00
+++ b/mysql-test/t/drop.test	2007-09-05 22:37:22 -03:00
@@ -121,4 +121,37 @@ disconnect addconroot1;
 disconnect addconroot2;
 connection default;
 
+#
+# Bug#25858 Some DROP TABLE under LOCK TABLES can cause deadlocks
+#
+
+--disable_warnings
+drop table if exists t1,t2;
+--enable_warnings
+create table t1 (a int);
+create table t2 (a int);
+lock table t1 read;
+--error ER_TABLE_NOT_LOCKED
+drop table t2;
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
+drop table t1;
+unlock tables;
+drop table t1,t2;
+connect (addconroot, localhost, root,,);
+connection default;
+create table t1 (i int);
+create table t2 (i int);
+lock tables t1 read;
+connection addconroot;
+lock tables t2 read;
+--error ER_TABLE_NOT_LOCKED
+drop table t1;
+connection default;
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
+drop table t1,t2;
+disconnect addconroot;
+connection default;
+unlock tables;
+drop table t1,t2;
+
 --echo End of 5.0 tests
diff -Nrup a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test
--- a/mysql-test/t/group_by.test	2007-07-31 03:12:19 -03:00
+++ b/mysql-test/t/group_by.test	2007-09-05 22:37:22 -03:00
@@ -120,8 +120,9 @@ SELECT cid, CONCAT(firstname, ' ', surna
 SELECT cid, CONCAT(firstname, ' ', surname), COUNT(call_id) FROM t1 LEFT JOIN t2 ON
cid=contact_id WHERE firstname like '%foo%' GROUP BY cid ORDER BY NULL;
 SELECT HIGH_PRIORITY cid, CONCAT(firstname, ' ', surname), COUNT(call_id) FROM t1 LEFT
JOIN t2 ON cid=contact_id WHERE firstname like '%foo%' GROUP BY cid ORDER BY surname,
firstname;
 
-drop table t1,t2;
+drop table t2;
 unlock tables;
+drop table t1;
 
 #
 # Test of group by bug in bugzilla
diff -Nrup a/mysql-test/t/insert_update.test b/mysql-test/t/insert_update.test
--- a/mysql-test/t/insert_update.test	2007-08-30 16:19:21 -03:00
+++ b/mysql-test/t/insert_update.test	2007-09-05 22:37:22 -03:00
@@ -339,5 +339,6 @@ select * from t1;
 connection default;
 disconnect update;
 disconnect select;
+unlock tables;
 drop table t1;
 set low_priority_updates=default;
diff -Nrup a/mysql-test/t/lock_multi.test b/mysql-test/t/lock_multi.test
--- a/mysql-test/t/lock_multi.test	2007-08-17 11:34:08 -03:00
+++ b/mysql-test/t/lock_multi.test	2007-09-05 22:37:22 -03:00
@@ -68,6 +68,7 @@ update t1,t2 set c=a where b=d;
 connection reader;
 select c from t2;
 connection locker;
+unlock tables;
 drop table t1;
 drop table t2;
 
diff -Nrup a/mysql-test/t/myisam.test b/mysql-test/t/myisam.test
--- a/mysql-test/t/myisam.test	2007-08-29 09:46:27 -03:00
+++ b/mysql-test/t/myisam.test	2007-09-05 22:37:22 -03:00
@@ -503,6 +503,7 @@ insert into t2 values(2,0);
 disconnect root;
 connection default;
 select straight_join * from t1,t2 force index (primary) where t1.a=t2.a;
+unlock tables;
 drop table t1,t2;
 #
 # Full key.
@@ -520,6 +521,7 @@ disconnect con1;
 connection default;
 SELECT t1.c1 AS t1c1, t2.c1 AS t2c1 FROM t1, t2
   WHERE t1.c1 = t2.c1 HAVING t1c1 != t2c1;
+UNLOCK TABLES;
 DROP TABLE t1,t2;
 
 # End of 4.0 tests
diff -Nrup a/mysql-test/t/query_cache_notembedded.test
b/mysql-test/t/query_cache_notembedded.test
--- a/mysql-test/t/query_cache_notembedded.test	2007-01-22 13:37:24 -02:00
+++ b/mysql-test/t/query_cache_notembedded.test	2007-09-05 22:37:22 -03:00
@@ -95,6 +95,7 @@ connection root2;
 SELECT * FROM t1;
 connection root;
 SELECT * FROM t1;
+UNLOCK TABLES;
 drop table t1;
 
 #
diff -Nrup a/mysql-test/t/view.test b/mysql-test/t/view.test
--- a/mysql-test/t/view.test	2007-07-29 06:41:37 -03:00
+++ b/mysql-test/t/view.test	2007-09-05 22:37:22 -03:00
@@ -1017,6 +1017,9 @@ select * from v1;
 -- error 1100
 select * from t2;
 drop view v1;
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
+drop table t1, t2;
+unlock tables;
 drop table t1, t2;
 
 #
diff -Nrup a/sql/sql_table.cc b/sql/sql_table.cc
--- a/sql/sql_table.cc	2007-08-26 19:38:35 -03:00
+++ b/sql/sql_table.cc	2007-09-05 22:37:22 -03:00
@@ -1407,6 +1407,70 @@ void write_bin_log(THD *thd, bool clear_
 }
 
 
+/**
+  Test if a list of tables are droppable wrt locks.
+
+  @param thd        The current thread handler
+  @param table_list Table container containing the tables to check
+
+  @return FALSE if successful, TRUE FALSE otherwise.
+*/
+
+static bool mysql_rm_table_lock_check(THD *thd, TABLE_LIST *table_list)
+{
+  TABLE *table;
+  TABLE_LIST *tbl;
+  uint i, key_length;
+  char key[MAX_DBKEY_LENGTH];
+  MYSQL_LOCK *lock_data= thd->locked_tables;
+  DBUG_ENTER("mysql_rm_table_lock_check");
+
+  if (! lock_data->table_count)
+    DBUG_RETURN(FALSE);
+
+  for (tbl= table_list; tbl; tbl= tbl->next_local)
+  {
+    table= find_temporary_table(thd, tbl->db, tbl->table_name);
+
+    /* A temporary table is visible only to the current connection */
+    if (table)
+      continue;
+
+    key_length= create_table_def_key(thd, key, table_list, 0);
+
+    /* Find out if this table is locked */
+    for (i= 0; i < lock_data->table_count; i++)
+    {
+      table= lock_data->table[i];
+
+      if (table->s->tmp_table)
+        key_length+= TMP_TABLE_KEY_EXTRA;
+
+      if (table->s->table_cache_key.length == key_length &&
+          !memcmp(table->s->table_cache_key.str, key, key_length))
+        break;
+
+      table= NULL;
+    }
+
+    /* We are under LOCK TABLES, but this table is not locked at all */
+    if (! table)
+    {
+      my_error(ER_TABLE_NOT_LOCKED, MYF(0), tbl->alias);
+      DBUG_RETURN(TRUE);
+    }
+    /* We must hold a write lock on this table */
+    else if (table->reginfo.lock_type < TL_WRITE)
+    {
+      my_error(ER_TABLE_NOT_LOCKED_FOR_WRITE, MYF(0), tbl->alias);
+      DBUG_RETURN(TRUE);
+    }
+  }
+
+  DBUG_RETURN(FALSE);
+}
+
+
 /*
  delete (drop) tables.
 
@@ -1433,20 +1497,23 @@ void write_bin_log(THD *thd, bool clear_
 bool mysql_rm_table(THD *thd,TABLE_LIST *tables, my_bool if_exists,
                     my_bool drop_temporary)
 {
-  bool error= FALSE, need_start_waiters= FALSE;
+  bool error, need_start_waiters= FALSE;
   DBUG_ENTER("mysql_rm_table");
 
   /* mark for close and remove all cached entries */
 
   if (!drop_temporary)
   {
-    if ((error= wait_if_global_read_lock(thd, 0, 1)))
+    if (thd->locked_tables && mysql_rm_table_lock_check(thd, tables))
+      DBUG_RETURN(TRUE);
+
+    if (wait_if_global_read_lock(thd, 0, 1))
     {
       my_error(ER_TABLE_NOT_LOCKED_FOR_WRITE, MYF(0), tables->table_name);
       DBUG_RETURN(TRUE);
     }
-    else
-      need_start_waiters= TRUE;
+
+    need_start_waiters= TRUE;
   }
 
   /*