bk commit into 5.0 tree (davi:1.2502) BUG#25164

[Davi Arnaut]

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-24 15:22:41-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-24 15:22:37-03:00, davi@stripped
+13 -0
    Bug#25164 test case result

  mysql-test/t/innodb-deadlock.test@stripped, 2007-08-24 15:22:37-03:00, davi@stripped +28
-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-24 15:22:37-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-24 15:22:37 -03:00
@@ -94,3 +94,16 @@ 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;
+drop table if exists b;
+create table A (c int);
+insert into A (c) values (0);
+create table b as select * from A;
+create table a as select * from A;
+drop table A;
+drop table b;
+drop table if exists a;
+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-24 15:22:37 -03:00
@@ -112,4 +112,31 @@ 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;
+drop table if exists b;
+--enable_warnings
+
+create table A (c int);
+insert into A (c) values (0);
+create table b as select * from A;
+--error 0,1213,1093
+create table a as select * from A;
+
+drop table A;
+drop table b;
+
+--disable_warnings
+drop table if exists a;
+--enable_warnings
+
+--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-24 15:22:37 -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;