Fix for Bug#43015 and Bug#43065

   Details for Bug#43015 main.lock_multi: Weak code (sleeps etc.)
   -------------------------------------------------------------
   - The fix for bug 42003 already removed a lot of the weaknesses mentioned.
   - Tests showed that there are unfortunately no improvements of this tests
     in MySQL 5.1 which could be ported back to 5.0.
   - Remove a superfluous "--sleep 1" around line 195

   Details for Bug#43065 main.lock_multi: This test is too big if the disk is slow
   -------------------------------------------------------------------------------
   - move the subtests for the bugs 38499 and 36691 into separate scripts
   - runtime under excessive parallel I/O load after applying the fix
     lock_multi           [ pass ]          22887
     lock_multi_bug38499  [ pass ]         536926
     lock_multi_bug38691  [ pass ]         258498

1 files changed, 120 insertions, 357 deletions

diff --git a/mysql-test/t/lock_multi.test b/mysql-test/t/lock_multi.test
index 089a60edb3d..d5c7beb5104 100644
--- a/mysql-test/t/lock_multi.test
+++ b/mysql-test/t/lock_multi.test
@@ -1,4 +1,8 @@
 -- source include/not_embedded.inc
+
+# Save the initial number of concurrent sessions
+--source include/count_sessions.inc
+
 --disable_warnings
 drop table if exists t1,t2;
 --enable_warnings
@@ -14,12 +18,23 @@ create table t1(n int);
 insert into t1 values (1);
 lock tables t1 write;
 connection writer;
-send update low_priority t1 set n = 4;
+send
+update low_priority t1 set n = 4;
 connection reader;
---sleep 2
-send select n from t1;
+# Sleep a bit till the update of connection writer is in work and hangs
+let $wait_timeout= 5;
+let $show_statement= SHOW PROCESSLIST;
+let $field= State;
+let $condition= = 'Locked';
+--source include/wait_show_condition.inc
+send
+select n from t1;
 connection locker;
---sleep 2
+# Sleep a bit till the select of connection reader is in work and hangs
+# Here we cannot use include/wait_show_condition.inc because this routine
+# cannot count the number of 'Locked' sessions or access two columns within
+# the same query_get_value call.
+--sleep 3
 unlock tables;
 connection writer;
 reap;
@@ -32,12 +47,23 @@ create table t1(n int);
 insert into t1 values (1);
 lock tables t1 read;
 connection writer;
-send update low_priority t1 set n = 4;
+send
+update low_priority t1 set n = 4;
 connection reader;
---sleep 2
-send select n from t1;
+# Sleep a bit till the update of connection writer is in work and hangs
+let $wait_timeout= 5;
+let $show_statement= SHOW PROCESSLIST;
+let $field= State;
+let $condition= = 'Locked';
+--source include/wait_show_condition.inc
+#
+send
+select n from t1;
 connection locker;
---sleep 2
+# Sleep a bit till the select of connection reader is in work and hangs
+# Here we cannot use include/wait_show_condition.inc.
+--sleep 3
+#
 unlock tables;
 connection writer;
 reap;
@@ -58,10 +84,13 @@ insert into t1 values(2,2);
 insert into t2 values(1,2);
 lock table t1 read;
 connection writer;
---sleep 2
-send update t1,t2 set c=a where b=d;
+send
+update t1,t2 set c=a where b=d;
 connection reader;
---sleep 2
+# Sleep a bit till the update of connection writer is finished
+# Here we cannot use include/wait_show_condition.inc.
+--sleep 3
+#
 select c from t2;
 connection writer;
 reap;
@@ -70,7 +99,7 @@ drop table t1;
 drop table t2;
 
 #
-# Test problem when using locks on many tables and droping a table that
+# Test problem when using locks on many tables and dropping a table that
 # is to-be-locked by another thread
 #
 
@@ -79,11 +108,18 @@ create table t1 (a int);
 create table t2 (a int);
 lock table t1 write, t2 write;
 connection reader;
-send insert t1 select * from t2;
+send
+insert t1 select * from t2;
 connection locker;
+# Sleep a bit till the insert of connection reader is in work and hangs
+let $wait_timeout= 5;
+let $show_statement= SHOW PROCESSLIST;
+let $field= State;
+let $condition= = 'Locked';
+--source include/wait_show_condition.inc
 drop table t2;
 connection reader;
---error 1146
+--error ER_NO_SUCH_TABLE
 reap;
 connection locker;
 drop table t1;
@@ -91,7 +127,7 @@ drop table t1;
 # End of 4.1 tests
 
 #
-# BUG#9998 - MySQL client hangs on USE "database"
+# Bug#9998 MySQL client hangs on USE "database"
 #
 create table t1(a int);
 lock tables t1 write;
@@ -102,7 +138,7 @@ unlock tables;
 drop table t1;
 
 #
-# Bug#19815 - CREATE/RENAME/DROP DATABASE can deadlock on a global read lock
+# Bug#19815 CREATE/RENAME/DROP DATABASE can deadlock on a global read lock
 #
 connect (con1,localhost,root,,);
 connect (con2,localhost,root,,);
@@ -114,12 +150,18 @@ FLUSH TABLES WITH READ LOCK;
 # With bug in place: acquire LOCK_mysql_create_table and
 # wait in wait_if_global_read_lock().
 connection con2;
-send DROP DATABASE mysqltest_1;
---sleep 1
+send
+DROP DATABASE mysqltest_1;
 #
 # With bug in place: try to acquire LOCK_mysql_create_table...
 # When fixed: Reject dropping db because of the read lock.
 connection con1;
+# Wait a bit so that the session con2 is in state "Waiting for release of readlock"
+let $wait_timeout= 5;
+let $show_statement= SHOW PROCESSLIST;
+let $field= State;
+let $condition= = 'Waiting for release of readlock';
+--source include/wait_show_condition.inc
 --error ER_CANT_UPDATE_WITH_READLOCK
 DROP DATABASE mysqltest_1;
 UNLOCK TABLES;
@@ -135,26 +177,33 @@ disconnect con2;
 --error ER_DB_DROP_EXISTS
 DROP DATABASE mysqltest_1;
 
+
 #
-# Bug#16986 - Deadlock condition with MyISAM tables
+# Bug#16986 Deadlock condition with MyISAM tables
 #
 
 # Need a matching user in mysql.user for multi-table select
 --source include/add_anonymous_users.inc
 
 connection locker;
-use mysql;
+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
+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;
 #
 connection locker;
+# Sleep a bit till the select of connection reader is in work and hangs
+let $wait_timeout= 5;
+let $show_statement= SHOW PROCESSLIST;
+let $field= State;
+let $condition= = 'Locked';
+--source include/wait_show_condition.inc
 # Make test case independent from earlier grants.
 --replace_result "Table is already up to date" "OK"
 OPTIMIZE TABLES columns_priv, db, host, user;
@@ -162,7 +211,7 @@ UNLOCK TABLES;
 #
 connection reader;
 reap;
-use test;
+USE test;
 #
 connection locker;
 use test;
@@ -177,11 +226,17 @@ LOCK TABLE t1 WRITE;
 #
 # This waits until t1 is unlocked.
 connection locker;
-send FLUSH TABLES WITH READ LOCK;
---sleep 1
+send
+FLUSH TABLES WITH READ LOCK;
 #
 # This must not block.
 connection writer;
+# Sleep a bit till the flush of connection locker is in work and hangs
+let $wait_timeout= 5;
+let $show_statement= SHOW PROCESSLIST;
+let $field= State;
+let $condition= = 'Flushing tables';
+--source include/wait_show_condition.inc
 CREATE TABLE t2 (c1 int);
 UNLOCK TABLES;
 #
@@ -201,12 +256,18 @@ LOCK TABLE t1 WRITE;
 #
 # This waits until t1 is unlocked.
 connection locker;
-send FLUSH TABLES WITH READ LOCK;
---sleep 1
+send
+FLUSH TABLES WITH READ LOCK;
 #
 # This must not block.
 connection writer;
---error 1100
+# Sleep a bit till the flush of connection locker is in work and hangs
+let $wait_timeout= 5;
+let $show_statement= SHOW PROCESSLIST;
+let $field= State;
+let $condition= = 'Flushing tables';
+--source include/wait_show_condition.inc
+--error ER_TABLE_NOT_LOCKED
 CREATE TABLE t2 AS SELECT * FROM t1;
 UNLOCK TABLES;
 #
@@ -220,8 +281,9 @@ DROP TABLE t1;
 
 --source include/delete_anonymous_users.inc
 
+
 #
-# Bug #17264: MySQL Server freeze
+# Bug#17264 MySQL Server freeze
 #
 connection locker;
 # Disable warnings to allow test to run also without InnoDB
@@ -230,17 +292,29 @@ create table t1 (f1 int(12) unsigned not null auto_increment, primary key(f1)) e
 --enable_warnings
 lock tables t1 write;
 connection writer;
---sleep 2
+# mleich: I have doubts if the next sleep is really necessary
+#         Therefore I set it to comment but don't remove it
+#         in case it hat to be enabled again.
+# --sleep 2
 delimiter //;
-send alter table t1 auto_increment=0; alter table t1 auto_increment=0; alter table t1 auto_increment=0; alter table t1 auto_increment=0; alter table t1 auto_increment=0; //
+send
+alter table t1 auto_increment=0; alter table t1 auto_increment=0; alter table t1 auto_increment=0; alter table t1 auto_increment=0; alter table t1 auto_increment=0; //
 delimiter ;//
 connection reader;
---sleep 2
+# Wait till connection writer is blocked
+let $wait_timeout= 5;
+let $show_statement= SHOW PROCESSLIST;
+let $field= State;
+let $condition= = 'Locked';
+--source include/wait_show_condition.inc
 delimiter //;
-send alter table t1 auto_increment=0; alter table t1 auto_increment=0; alter table t1 auto_increment=0; alter table t1 auto_increment=0; alter table t1 auto_increment=0; //
+send
+alter table t1 auto_increment=0; alter table t1 auto_increment=0; alter table t1 auto_increment=0; alter table t1 auto_increment=0; alter table t1 auto_increment=0; //
 delimiter ;//
 connection locker;
---sleep 2
+# Wait till connection reader is blocked
+# Here we cannot use include/wait_show_condition.inc.
+--sleep 3
 unlock tables;
 connection writer;
 reap;
@@ -263,7 +337,7 @@ lock tables t1 read;
 --echo connection: writer
 connection writer;
 let $ID= `select connection_id()`;
---send create table t2 like t1;
+send create table t2 like t1;
 --echo connection: default
 connection default;
 let $show_type= open tables where in_use=2 and name_locked=1;
@@ -281,325 +355,14 @@ unlock tables;
 connection default;
 drop table t1;
 
-#
-# Bug #38691: segfault/abort in ``UPDATE ...JOIN'' while
-#             ``FLUSH TABLES WITH READ LOCK''
-#
-
---connection default
-CREATE TABLE t1 (
-  a int(11) unsigned default NULL,
-  b varchar(255) default NULL,
-  UNIQUE KEY a (a),
-  KEY b (b)
-);
-
-INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3);
-CREATE TABLE t2 SELECT * FROM t1;
-CREATE TABLE t3 SELECT * FROM t1;
-
---echo # test altering of columns that multiupdate doesn't use
-
---echo # normal mode
-
---disable_query_log
-let $i = 100;
-while ($i) {
---dec $i
-
---connection writer
-  send UPDATE t2 INNER JOIN (t1 JOIN t3 USING(a)) USING(a)
-         SET a = NULL WHERE t1.b <> t2.b;
-
---connection locker
-  ALTER TABLE t2 ADD COLUMN (c INT);
-  ALTER TABLE t2 DROP COLUMN c;
-
---connection writer
---reap
-}
-
---echo # PS mode
-
---connection writer
-PREPARE stmt FROM 'UPDATE t2 INNER JOIN (t1 JOIN t3 USING(a)) USING(a)
-                     SET a = NULL WHERE t1.b <> t2.b';
-
-let $i = 100;
-while ($i) {
---dec $i
-
---connection writer
---send EXECUTE stmt
-
---connection locker
-  ALTER TABLE t2 ADD COLUMN (c INT);
-  ALTER TABLE t2 DROP COLUMN c;
-
---connection writer
---reap
-}
---enable_query_log
-
-
---echo # test altering of columns that multiupdate uses
-
---echo # normal mode
-
---connection default
-
---disable_query_log
-let $i = 100;
-while ($i) {
-  dec $i;
-
---connection locker
---error 0,1060
-  ALTER TABLE t2 ADD COLUMN a int(11) unsigned default NULL;
-  UPDATE t2 SET a=b;
-
---connection writer
---send UPDATE t2 INNER JOIN (t1 JOIN t3 USING(a)) USING(a) SET a = NULL WHERE t1.b <> t2.b
-
---connection locker
---error 0,1091
-  ALTER TABLE t2 DROP COLUMN a;
-
---connection writer
---error 0,1054
---reap
-}
---enable_query_log
-
---echo # PS mode
-
---disable_query_log
-let $i = 100;
-while ($i) {
-  dec $i;
-
---connection locker
---error 0,1060
-  ALTER TABLE t2 ADD COLUMN a int(11) unsigned default NULL;
-  UPDATE t2 SET a=b;
-
---connection writer
-  PREPARE stmt FROM 'UPDATE t2 INNER JOIN (t1 JOIN t3 USING(a)) USING(a) SET a = NULL WHERE t1.b <> t2.b';
---send EXECUTE stmt
-
---connection locker
---error 0,1091
-  ALTER TABLE t2 DROP COLUMN a;
-
---connection writer
---error 0,1054
---reap
-
-}
---enable_query_log
---connection default
-DROP TABLE t1, t2, t3;
-
-#
-# Bug#38499: flush tables and multitable table update with derived table cause 
-#            crash
-#
-
-CREATE TABLE t1( a INT, b INT );
-INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4);
-
---echo # 1. test regular tables
---echo # 1.1. test altering of columns that multiupdate doesn't use
---echo # 1.1.1. normal mode
-
---disable_query_log
-let $i = 100;
-while ($i) {
---dec $i
 
---connection writer
-  send UPDATE t1, (SELECT 1 FROM t1 t1i) d SET a = 0 WHERE 1=0;
+# Close connections used in many subtests
+--disconnect reader
+--disconnect locker
+--disconnect writer
 
---connection locker
-  ALTER TABLE t1 ADD COLUMN (c INT);
-  ALTER TABLE t1 DROP COLUMN c;
-
---connection writer
---reap
-}
-
---echo # 1.1.2. PS mode
-
---connection writer
-PREPARE stmt FROM 'UPDATE t1, (SELECT 1 FROM t1 t1i) d SET a = 0 WHERE 1=0';
-
-let $i = 100;
-while ($i) {
---dec $i
-
---connection writer
---send EXECUTE stmt
-
---connection locker
-  ALTER TABLE t1 ADD COLUMN (c INT);
-  ALTER TABLE t1 DROP COLUMN c;
-
---connection writer
---reap
-}
---enable_query_log
-
---echo # 1.2. test altering of columns that multiupdate uses
---echo # 1.2.1. normal mode
-
---connection default
-
---disable_query_log
-let $i = 100;
-while ($i) {
-  dec $i;
-
---connection locker
---error 0,1060
-  ALTER TABLE t1 ADD COLUMN a int(11) unsigned default NULL;
-  UPDATE t1 SET a=b;
-
---connection writer
---send UPDATE t1, (SELECT 1 FROM t1 t1i) d SET a = 0 WHERE 1=0;
-
---connection locker
---error 0,1091
-  ALTER TABLE t1 DROP COLUMN a;
-
---connection writer
---error 0,1054 # unknown column error
---reap
-}
---enable_query_log
-
---echo # 1.2.2. PS mode
-
---disable_query_log
-let $i = 100;
-while ($i) {
-  dec $i;
-
---connection locker
---error 0,1060
-  ALTER TABLE t1 ADD COLUMN a INT;
-  UPDATE t1 SET a=b;
-
---connection writer
-  PREPARE stmt FROM 'UPDATE t1, (SELECT 1 FROM t1 t1i) d SET a = 0 WHERE 1=0';
---send EXECUTE stmt
-
---connection locker
---error 0,1091
-  ALTER TABLE t1 DROP COLUMN a;
-
---connection writer
---error 0,1054 # Unknown column 'a' in 'field list'
---reap
-}
---enable_query_log
---connection default
-ALTER TABLE t1 ADD COLUMN a INT;
-
---echo # 2. test UNIONs
---echo # 2.1. test altering of columns that multiupdate doesn't use
---echo # 2.1.1. normal mode
-
---disable_query_log
-let $i = 100;
-while ($i) {
---dec $i
-
---connection writer
-  send UPDATE t1, ((SELECT 1 FROM t1 t1i) UNION (SELECT 2 FROM t1 t1ii)) e SET a = 0 WHERE 1=0;
-
---connection locker
-  ALTER TABLE t1 ADD COLUMN (c INT);
-  ALTER TABLE t1 DROP COLUMN c;
-
---connection writer
---reap
-}
-
---echo # 2.1.2. PS mode
-
---connection writer
-PREPARE stmt FROM 'UPDATE t1, ((SELECT 1 FROM t1 t1i) UNION (SELECT 2 FROM t1 t1ii)) e SET a = 0 WHERE 1=0';
-
-let $i = 100;
-while ($i) {
---dec $i
-
---connection writer
---send EXECUTE stmt
-
---connection locker
-  ALTER TABLE t1 ADD COLUMN (c INT);
-  ALTER TABLE t1 DROP COLUMN c;
-
---connection writer
---reap
-}
---enable_query_log
-
---echo # 2.2. test altering of columns that multiupdate uses
---echo # 2.2.1. normal mode
-
---connection default
-
---disable_query_log
-let $i = 100;
-while ($i) {
-  dec $i;
-
---connection locker
---error 0,1060
-  ALTER TABLE t1 ADD COLUMN a int(11) unsigned default NULL;
-  UPDATE t1 SET a=b;
-
---connection writer
---send UPDATE t1, ((SELECT 1 FROM t1 t1i) UNION (SELECT 2 FROM t1 t1ii)) e SET a = 0 WHERE 1=0;
-
---connection locker
---error 0,1091
-  ALTER TABLE t1 DROP COLUMN a;
-
---connection writer
---error 0,1054 # Unknown column 'a' in 'field list'
---reap
-}
---enable_query_log
-
---echo # 2.2.2. PS mode
-
---disable_query_log
-let $i = 100;
-while ($i) {
-  dec $i;
-
---connection locker
---error 0,1060
-  ALTER TABLE t1 ADD COLUMN a INT;
-  UPDATE t1 SET a=b;
-
---connection writer
-  PREPARE stmt FROM 'UPDATE t1, ((SELECT 1 FROM t1 t1i) UNION (SELECT 2 FROM t1 t1ii)) e SET a = 0 WHERE 1=0';
---send EXECUTE stmt
-
---connection locker
---error 0,1091
-  ALTER TABLE t1 DROP COLUMN a;
+# End of 5.0 tests
 
---connection writer
---error 0,1054 # Unknown column 'a' in 'field list'
---reap
-}
---enable_query_log
---connection default
-DROP TABLE t1;
+# Wait till all disconnects are completed
+--source include/wait_until_count_sessions.inc
 
-# End of 5.0 tests