More DBUG statements

Replaced COND_refresh with COND_global_read_lock becasue of a bug in NTPL threads when using different mutexes as arguments to pthread_cond_wait()
The original code caused a hang in FLUSH TABLES WITH READ LOCK in some circumstances because pthread_cond_broadcast() was not delivered to other threads.
This fixes:
Bug#16986: Deadlock condition with MyISAM tables
Bug#20048: FLUSH TABLES WITH READ LOCK causes a deadlock


mysql-test/r/flush.result:
  Added test case for deadlock with FLUSH TABLES WITH READ LOCK
mysql-test/r/lock_multi.result:
  Test for bug in LOCK TABLE + optimize table
mysql-test/t/flush.test:
  Added test case for deadlock with FLUSH TABLES WITH READ LOCK
mysql-test/t/lock_multi.test:
  Test for bug in LOCK TABLE + optimize table
sql/lock.cc:
  Replaced COND_refresh with COND_global_read_lock becasue of a bug in NTPL threads when using different mutexes as arguments to pthread_cond_wait()
  The original code caused a hang in FLUSH TABLES WITH READ LOCK in some circumstances because pthread_cond_broadcast() was not delivered to other threads
sql/mysql_priv.h:
  Added COND_global_read_lock
sql/mysqld.cc:
  Added COND_global_read_lock
sql/sql_base.cc:
  More DBUG statements
  Added a broadcast in remove_table_from_cache() to release any threads waiting in open

2 files changed, 72 insertions, 0 deletions

diff --git a/mysql-test/t/flush.test b/mysql-test/t/flush.test
index f5fd9fcadf2..95ba633fefd 100644
--- a/mysql-test/t/flush.test
+++ b/mysql-test/t/flush.test
@@ -102,3 +102,43 @@ unlock tables;
 drop table t1, t2, t3;
 
 # End of 4.1 tests
+
+#
+# Test of deadlock problem when doing FLUSH TABLE with read lock
+# (Bug was in NTPL threads in Linux when using different mutex while
+#  waiting for a condtion variable)
+
+create table t1 (c1 int);
+create table t2 (c1 int);
+
+connect (con1,localhost,root,,);
+connect (con3,localhost,root,,);
+
+connection con1;
+lock table t1 write;
+
+connection con2;
+send flush tables with read lock;
+--sleep 1
+
+connection con3;
+send insert into t2 values(1);
+--sleep 1
+
+connection con1;
+unlock tables;
+disconnect con1;
+
+connection con2;
+reap;
+disconnect con2;
+
+connection con3;
+# It hangs here (insert into t2 does not end).
+reap;
+disconnect con3;
+
+connection default;
+drop table t1, t2;
+
+# End of 5.0 tests
diff --git a/mysql-test/t/lock_multi.test b/mysql-test/t/lock_multi.test
index 0d2266fc2ae..3c829848bf3 100644
--- a/mysql-test/t/lock_multi.test
+++ b/mysql-test/t/lock_multi.test
@@ -107,3 +107,35 @@ show columns from t1;
 connection locker;
 unlock tables;
 drop table t1;
+
+#
+# Bug#16986 - Deadlock condition with MyISAM tables
+#
+connection locker;
+use mysql;
+LOCK TABLES columns_priv WRITE, db WRITE, host WRITE, user WRITE;
+FLUSH TABLES;
+--sleep 1
+#
+connection reader;
+use mysql;
+#NOTE:  This must be a multi-table select, otherwise the deadlock will not occur
+send SELECT user.Select_priv FROM user, db WHERE user.user = db.user LIMIT 1;
+--sleep 1
+#
+connection locker;
+# Make test case independent from earlier grants.
+--replace_result "Table is already up to date" "OK"
+OPTIMIZE TABLES columns_priv, db, host, user;
+UNLOCK TABLES;
+#
+connection reader;
+reap;
+use test;
+#
+connection locker;
+use test;
+#
+connection default;
+
+# End of 5.0 tests