From: Davi Arnaut
Date: August 27 2007 1:13pm
Subject: bk commit into 5.0 tree (davi:1.2502) BUG#25164
List-Archive: http://lists.mysql.com/commits/33150
X-Bug: 25164
Message-Id: <20070827131359.7BFAF31BD7A@moksha.local>

Below is the list of changes that have just been committed into a local
5.0 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-08-27 10:13:54-03:00, davi@stripped +3 -0
  Bug#25164 create table `a` as select * from `A` hangs
  
  The problem from a user's perspective: user creates table A, and then tries
  to CREATE TABLE a SELECT from A - and this causes a deadlock error, a hang,
  or fails with a debug assert, but only if the storage engine is InnoDB.
  
  The origin of the problem: InnoDB uses case-insensitive collation
  (system_charset_info) when looking up the internal table share, thus returning
  the same share for 'a' and 'A'.
  
  Cause of the user-visible behavior: since the same share is returned to SQL
  locking subsystem, it assumes that the same table is first locked (within the
  same session) for WRITE, and then for READ, and returns a deadlock error.
  However, the code is wrong in not properly cleaning up upon an error, leaving
  external locks in place, which leads to assertion failures and hangs.
  
  Fix that has been implemented: the SQL layer should properly propagate the
  deadlock error, cleaning up and freeing all resources.
  
  Further work towards a more complete solution: InnoDB should not use case
  insensitive collation for table share hash if table names on disk honor the case.

  mysql-test/r/innodb-deadlock.result@stripped, 2007-08-27 10:13:52-03:00, davi@stripped +11 -0
    Bug#25164 test case result

  mysql-test/t/innodb-deadlock.test@stripped, 2007-08-27 10:13:52-03:00, davi@stripped +26 -1
    Bug#25164 test case. The CREATE TABLE may fail depending on the character set
    of the system and filesystem, but it should never hang.

  sql/lock.cc@stripped, 2007-08-27 10:13:52-03:00, davi@stripped +2 -0
    Unlock the storage engine "external" table level locks, if the MySQL thr_lock
    locking subsystem detects a deadlock error.

diff -Nrup a/mysql-test/r/innodb-deadlock.result b/mysql-test/r/innodb-deadlock.result
--- a/mysql-test/r/innodb-deadlock.result	2004-03-20 07:31:13 -03:00
+++ b/mysql-test/r/innodb-deadlock.result	2007-08-27 10:13:52 -03:00
@@ -94,3 +94,14 @@ id	x
 300	300
 commit;
 drop table t1, t2;
+End of 4.1 tests
+set storage_engine=innodb;
+drop table if exists a;
+drop table if exists A;
+create table A (c int);
+insert into A (c) values (0);
+create table a as select * from A;
+drop table A;
+drop table if exists a;
+set storage_engine=default;
+End of 5.0 tests.
diff -Nrup a/mysql-test/t/innodb-deadlock.test b/mysql-test/t/innodb-deadlock.test
--- a/mysql-test/t/innodb-deadlock.test	2006-11-16 17:19:23 -02:00
+++ b/mysql-test/t/innodb-deadlock.test	2007-08-27 10:13:52 -03:00
@@ -112,4 +112,29 @@ commit;
 
 drop table t1, t2;
 
-# End of 4.1 tests
+--echo End of 4.1 tests
+
+#
+# Bug#25164 create table `a` as select * from `A` hangs
+#
+
+set storage_engine=innodb;
+
+--disable_warnings
+drop table if exists a;
+drop table if exists A;
+--enable_warnings
+
+create table A (c int);
+insert into A (c) values (0);
+--error 0,ER_LOCK_DEADLOCK,ER_UPDATE_TABLE_USED
+create table a as select * from A;
+drop table A;
+
+--disable_warnings
+drop table if exists a;
+--enable_warnings
+
+set storage_engine=default;
+
+--echo End of 5.0 tests.
diff -Nrup a/sql/lock.cc b/sql/lock.cc
--- a/sql/lock.cc	2007-08-16 14:51:30 -03:00
+++ b/sql/lock.cc	2007-08-27 10:13:52 -03:00
@@ -172,6 +172,8 @@ MYSQL_LOCK *mysql_lock_tables(THD *thd, 
                                                      thd->lock_id)];
     if (rc > 1)                                 /* a timeout or a deadlock */
     {
+      if (sql_lock->table_count)
+        VOID(unlock_external(thd, sql_lock->table, sql_lock->table_count));
       my_error(rc, MYF(0));
       my_free((gptr) sql_lock,MYF(0));
       sql_lock= 0;