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, 221 insertions, 0 deletions

diff --git a/mysql-test/t/lock_multi_bug38499.test b/mysql-test/t/lock_multi_bug38499.test
new file mode 100644
index 00000000000..8178987e802
--- /dev/null
+++ b/mysql-test/t/lock_multi_bug38499.test
@@ -0,0 +1,221 @@
+# Bug38499 flush tables and multitable table update with derived table cause crash
+# MySQL >= 5.0
+#
+
+# Save the initial number of concurrent sessions
+--source include/count_sessions.inc
+
+connect (locker,localhost,root,,);
+connect (writer,localhost,root,,);
+
+--connection default
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+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;
+
+--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,ER_DUP_FIELDNAME
+  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,ER_CANT_DROP_FIELD_OR_KEY
+  ALTER TABLE t1 DROP COLUMN a;
+
+--connection writer
+--error 0,ER_BAD_FIELD_ERROR # 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,ER_DUP_FIELDNAME
+  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,ER_CANT_DROP_FIELD_OR_KEY
+  ALTER TABLE t1 DROP COLUMN a;
+
+--connection writer
+--error 0,ER_BAD_FIELD_ERROR # 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,ER_DUP_FIELDNAME
+  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,ER_CANT_DROP_FIELD_OR_KEY
+  ALTER TABLE t1 DROP COLUMN a;
+
+--connection writer
+--error 0,ER_BAD_FIELD_ERROR # 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,ER_DUP_FIELDNAME
+  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,ER_CANT_DROP_FIELD_OR_KEY
+  ALTER TABLE t1 DROP COLUMN a;
+
+--connection writer
+--error 0,ER_BAD_FIELD_ERROR # Unknown column 'a' in 'field list'
+--reap
+}
+--enable_query_log
+--connection default
+DROP TABLE t1;
+
+
+# Close connections
+--disconnect locker
+--disconnect writer
+
+# End of 5.0 tests
+
+# Wait till all disconnects are completed
+--source include/wait_until_count_sessions.inc
+