BUG#16580366- MTR TESTS FAILING SPORADICALLY ON PB2 (5.5, 5.6 AND 5.7)

              DURING INNODB RECOVERY

Problem:
=======
The connection 'master' is dropped by mysqltest after
rpl_end.inc. At this point, dropping temporary tables
at the connection 'master' are not synced at slave.
So, the temporary tables replicated from master remain
on slave leading to an inconsistent close of the test.
The following test thus complains about the presence of
temporary table(s) left over from the previous test.

Fix:
===
- Put explicit drop commands in replication tests so
  that the temporary tables are dropped at slave as well.
- Added the check for Slave_open_temp_tables in
  mtr_check.sql to warn about the remaining temporary
  table, if any, at the close of a test.

18 files changed, 47 insertions, 5 deletions

diff --git a/mysql-test/extra/rpl_tests/rpl_drop_create_temp_table.inc b/mysql-test/extra/rpl_tests/rpl_drop_create_temp_table.inc
index 094e84537db..983851adfcd 100644
--- a/mysql-test/extra/rpl_tests/rpl_drop_create_temp_table.inc
+++ b/mysql-test/extra/rpl_tests/rpl_drop_create_temp_table.inc
@@ -150,10 +150,9 @@ if (`SELECT HEX(@commands) = HEX('configure')`)
 }
 
 #
-# Drops tables and synchronizes master and slave. Note that temporary
-# tables are not explitcily dropped as they will be dropped while
-# closing the connection.
+# Drops tables and synchronizes master and slave.
 #
+
 if (`SELECT HEX(@commands) = HEX('clean')`)
 {
   connection master;
@@ -162,10 +161,15 @@ if (`SELECT HEX(@commands) = HEX('clean')`)
 
   DROP TABLE IF EXISTS nt_xx_1;
 
+  DROP TEMPORARY TABLE IF EXISTS tt_tmp_xx_1;
+  DROP TEMPORARY TABLE IF EXISTS nt_tmp_xx_1;
+
   --let $n= $tot_table
   while ($n)
   { 
     --eval DROP TABLE IF EXISTS nt_$n
+    --eval DROP TEMPORARY TABLE IF EXISTS tt_tmp_$n
+    --eval DROP TEMPORARY TABLE IF EXISTS nt_tmp_$n
     --dec $n
   }
 
diff --git a/mysql-test/extra/rpl_tests/rpl_innodb.test b/mysql-test/extra/rpl_tests/rpl_innodb.test
index e590bd70c88..11aa58f730f 100644
--- a/mysql-test/extra/rpl_tests/rpl_innodb.test
+++ b/mysql-test/extra/rpl_tests/rpl_innodb.test
@@ -112,7 +112,7 @@ FLUSH LOGS;
 --echo -------- switch to master --------
 connection master;
 FLUSH LOGS;
-
+DROP TEMPORARY TABLE IF EXISTS mysqltest1.tmp2;
 DROP DATABASE mysqltest1;
 
 --echo End of 5.1 tests
diff --git a/mysql-test/extra/rpl_tests/rpl_reset_slave.test b/mysql-test/extra/rpl_tests/rpl_reset_slave.test
index 0d94f04ca44..44bd650682d 100644
--- a/mysql-test/extra/rpl_tests/rpl_reset_slave.test
+++ b/mysql-test/extra/rpl_tests/rpl_reset_slave.test
@@ -38,6 +38,9 @@ reset slave;
 source include/start_slave.inc;
 sync_with_master;
 show status like 'slave_open_temp_tables';
+connection master;
+drop temporary table if exists t1;
+sync_slave_with_master;
 
 #
 #Bug#34654  	RESET SLAVE does not clear LAST_IO_Err* 
diff --git a/mysql-test/include/mtr_check.sql b/mysql-test/include/mtr_check.sql
index fbe5df632c6..9c51b333ec6 100644
--- a/mysql-test/include/mtr_check.sql
+++ b/mysql-test/include/mtr_check.sql
@@ -72,6 +72,8 @@ BEGIN
     mysql.time_zone_transition_type,
     mysql.user;
 
+ show status like 'slave_open_temp_tables';
+
 END||
 
 --
diff --git a/mysql-test/suite/rpl/r/rpl_create_tmp_table_if_not_exists.result b/mysql-test/suite/rpl/r/rpl_create_tmp_table_if_not_exists.result
index 6bafbb32897..40a38ee8592 100644
--- a/mysql-test/suite/rpl/r/rpl_create_tmp_table_if_not_exists.result
+++ b/mysql-test/suite/rpl/r/rpl_create_tmp_table_if_not_exists.result
@@ -15,4 +15,7 @@ master-bin.000001	#	Query	#	#	use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS t
 master-bin.000001	#	Query	#	#	use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS tmp1 LIKE tmp
 master-bin.000001	#	Query	#	#	use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS tmp1 LIKE tmp
 master-bin.000001	#	Query	#	#	use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS tmp2 SELECT * FROM tmp
+DROP TEMPORARY TABLE tmp;
+DROP TEMPORARY TABLE tmp1;
+DROP TEMPORARY TABLE tmp2;
 include/rpl_end.inc
diff --git a/mysql-test/suite/rpl/r/rpl_mixed_ddl_dml.result b/mysql-test/suite/rpl/r/rpl_mixed_ddl_dml.result
index 99c5846cea0..55c74b0b533 100644
--- a/mysql-test/suite/rpl/r/rpl_mixed_ddl_dml.result
+++ b/mysql-test/suite/rpl/r/rpl_mixed_ddl_dml.result
@@ -42,4 +42,5 @@ t5	CREATE TABLE `t5` (
   `created` datetime DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t2,t3,t5;
+drop temporary table t4;
 include/rpl_end.inc
diff --git a/mysql-test/suite/rpl/r/rpl_mixed_drop_create_temp_table.result b/mysql-test/suite/rpl/r/rpl_mixed_drop_create_temp_table.result
index 761ff2f8e98..57c947c9333 100644
--- a/mysql-test/suite/rpl/r/rpl_mixed_drop_create_temp_table.result
+++ b/mysql-test/suite/rpl/r/rpl_mixed_drop_create_temp_table.result
@@ -1554,8 +1554,14 @@ master-bin.000001	#	Query	#	#	COMMIT
 SET @commands= 'clean';
 DROP TABLE IF EXISTS tt_xx_1;
 DROP TABLE IF EXISTS nt_xx_1;
+DROP TEMPORARY TABLE IF EXISTS tt_tmp_xx_1;
+DROP TEMPORARY TABLE IF EXISTS nt_tmp_xx_1;
 DROP TABLE IF EXISTS nt_2;
+DROP TEMPORARY TABLE IF EXISTS tt_tmp_2;
+DROP TEMPORARY TABLE IF EXISTS nt_tmp_2;
 DROP TABLE IF EXISTS nt_1;
+DROP TEMPORARY TABLE IF EXISTS tt_tmp_1;
+DROP TEMPORARY TABLE IF EXISTS nt_tmp_1;
 DROP TABLE IF EXISTS tt_2;
 DROP TABLE IF EXISTS tt_1;
 SET @commands= '';
diff --git a/mysql-test/suite/rpl/r/rpl_rotate_logs.result b/mysql-test/suite/rpl/r/rpl_rotate_logs.result
index 256ed4eaad2..9459361cb30 100644
--- a/mysql-test/suite/rpl/r/rpl_rotate_logs.result
+++ b/mysql-test/suite/rpl/r/rpl_rotate_logs.result
@@ -85,6 +85,7 @@ lock tables t3 read;
 include/assert.inc [Table t3 should contain 100 lines on the slave]
 unlock tables;
 drop table if exists t1,t2,t3,t4;
+drop temporary table temp_table;
 End of 4.1 tests
 show binlog events in 'non existing_binlog_file';
 ERROR HY000: Error when executing command SHOW BINLOG EVENTS: Could not find target log
diff --git a/mysql-test/suite/rpl/r/rpl_row_drop_create_temp_table.result b/mysql-test/suite/rpl/r/rpl_row_drop_create_temp_table.result
index 88754e2cf8c..a55c8e2a2bc 100644
--- a/mysql-test/suite/rpl/r/rpl_row_drop_create_temp_table.result
+++ b/mysql-test/suite/rpl/r/rpl_row_drop_create_temp_table.result
@@ -1619,8 +1619,14 @@ master-bin.000001	#	Query	#	#	COMMIT
 SET @commands= 'clean';
 DROP TABLE IF EXISTS tt_xx_1;
 DROP TABLE IF EXISTS nt_xx_1;
+DROP TEMPORARY TABLE IF EXISTS tt_tmp_xx_1;
+DROP TEMPORARY TABLE IF EXISTS nt_tmp_xx_1;
 DROP TABLE IF EXISTS nt_2;
+DROP TEMPORARY TABLE IF EXISTS tt_tmp_2;
+DROP TEMPORARY TABLE IF EXISTS nt_tmp_2;
 DROP TABLE IF EXISTS nt_1;
+DROP TEMPORARY TABLE IF EXISTS tt_tmp_1;
+DROP TEMPORARY TABLE IF EXISTS nt_tmp_1;
 DROP TABLE IF EXISTS tt_2;
 DROP TABLE IF EXISTS tt_1;
 SET @commands= '';
diff --git a/mysql-test/suite/rpl/r/rpl_row_reset_slave.result b/mysql-test/suite/rpl/r/rpl_row_reset_slave.result
index 41fe0b1a9f3..1cf70ba7e67 100644
--- a/mysql-test/suite/rpl/r/rpl_row_reset_slave.result
+++ b/mysql-test/suite/rpl/r/rpl_row_reset_slave.result
@@ -23,6 +23,7 @@ include/start_slave.inc
 show status like 'slave_open_temp_tables';
 Variable_name	Value
 Slave_open_temp_tables	0
+drop temporary table if exists t1;
 include/stop_slave.inc
 reset slave;
 include/check_slave_no_error.inc
diff --git a/mysql-test/suite/rpl/r/rpl_stm_000001.result b/mysql-test/suite/rpl/r/rpl_stm_000001.result
index 9493013a283..7aeff0d3d46 100644
--- a/mysql-test/suite/rpl/r/rpl_stm_000001.result
+++ b/mysql-test/suite/rpl/r/rpl_stm_000001.result
@@ -48,6 +48,7 @@ select (@id := id) - id from t2;
 0
 kill @id;
 drop table t2;
+drop temporary table t3;
 Got one of the listed errors
 include/wait_for_slave_sql_error_and_skip.inc [errno=1053]
 select count(*) from t1;
diff --git a/mysql-test/suite/rpl/r/rpl_stm_drop_create_temp_table.result b/mysql-test/suite/rpl/r/rpl_stm_drop_create_temp_table.result
index 42f16a4c175..08b318fbb43 100644
--- a/mysql-test/suite/rpl/r/rpl_stm_drop_create_temp_table.result
+++ b/mysql-test/suite/rpl/r/rpl_stm_drop_create_temp_table.result
@@ -1604,8 +1604,14 @@ master-bin.000001	#	Query	#	#	ROLLBACK
 SET @commands= 'clean';
 DROP TABLE IF EXISTS tt_xx_1;
 DROP TABLE IF EXISTS nt_xx_1;
+DROP TEMPORARY TABLE IF EXISTS tt_tmp_xx_1;
+DROP TEMPORARY TABLE IF EXISTS nt_tmp_xx_1;
 DROP TABLE IF EXISTS nt_2;
+DROP TEMPORARY TABLE IF EXISTS tt_tmp_2;
+DROP TEMPORARY TABLE IF EXISTS nt_tmp_2;
 DROP TABLE IF EXISTS nt_1;
+DROP TEMPORARY TABLE IF EXISTS tt_tmp_1;
+DROP TEMPORARY TABLE IF EXISTS nt_tmp_1;
 DROP TABLE IF EXISTS tt_2;
 DROP TABLE IF EXISTS tt_1;
 SET @commands= '';
diff --git a/mysql-test/suite/rpl/r/rpl_stm_innodb.result b/mysql-test/suite/rpl/r/rpl_stm_innodb.result
index 0e9531317b9..6f54b232e71 100644
--- a/mysql-test/suite/rpl/r/rpl_stm_innodb.result
+++ b/mysql-test/suite/rpl/r/rpl_stm_innodb.result
@@ -79,6 +79,7 @@ COUNT(*)
 FLUSH LOGS;
 -------- switch to master --------
 FLUSH LOGS;
+DROP TEMPORARY TABLE IF EXISTS mysqltest1.tmp2;
 DROP DATABASE mysqltest1;
 End of 5.1 tests
 #
diff --git a/mysql-test/suite/rpl/r/rpl_stm_reset_slave.result b/mysql-test/suite/rpl/r/rpl_stm_reset_slave.result
index b1473c937a1..e5870cec2c9 100644
--- a/mysql-test/suite/rpl/r/rpl_stm_reset_slave.result
+++ b/mysql-test/suite/rpl/r/rpl_stm_reset_slave.result
@@ -23,6 +23,7 @@ include/start_slave.inc
 show status like 'slave_open_temp_tables';
 Variable_name	Value
 Slave_open_temp_tables	1
+drop temporary table if exists t1;
 include/stop_slave.inc
 reset slave;
 include/check_slave_no_error.inc
diff --git a/mysql-test/suite/rpl/t/rpl_create_tmp_table_if_not_exists.test b/mysql-test/suite/rpl/t/rpl_create_tmp_table_if_not_exists.test
index daf6b7e9ad8..bf2c5442d40 100644
--- a/mysql-test/suite/rpl/t/rpl_create_tmp_table_if_not_exists.test
+++ b/mysql-test/suite/rpl/t/rpl_create_tmp_table_if_not_exists.test
@@ -38,4 +38,8 @@ CREATE TEMPORARY TABLE IF NOT EXISTS tmp2 SELECT * FROM tmp;
 CREATE TEMPORARY TABLE IF NOT EXISTS tmp2 SELECT * FROM tmp;
 source include/show_binlog_events.inc;
 
+DROP TEMPORARY TABLE tmp;
+DROP TEMPORARY TABLE tmp1;
+DROP TEMPORARY TABLE tmp2;
+
 --source include/rpl_end.inc
diff --git a/mysql-test/suite/rpl/t/rpl_mixed_ddl_dml.test b/mysql-test/suite/rpl/t/rpl_mixed_ddl_dml.test
index cbd7e9c2558..4309dc3f9b1 100644
--- a/mysql-test/suite/rpl/t/rpl_mixed_ddl_dml.test
+++ b/mysql-test/suite/rpl/t/rpl_mixed_ddl_dml.test
@@ -43,6 +43,7 @@ show create table t3;
 show create table t5;
 connection master;
 drop table t2,t3,t5;
+drop temporary table t4;
 sync_slave_with_master;
 
 # End of 4.1 tests
diff --git a/mysql-test/suite/rpl/t/rpl_rotate_logs.test b/mysql-test/suite/rpl/t/rpl_rotate_logs.test
index dc158d7ef92..4d0c93f46bd 100644
--- a/mysql-test/suite/rpl/t/rpl_rotate_logs.test
+++ b/mysql-test/suite/rpl/t/rpl_rotate_logs.test
@@ -218,6 +218,7 @@ unlock tables;
 #clean up
 connection master;
 drop table if exists t1,t2,t3,t4;
+drop temporary table temp_table;
 sync_slave_with_master;
 
 --echo End of 4.1 tests
diff --git a/mysql-test/suite/rpl/t/rpl_stm_000001.test b/mysql-test/suite/rpl/t/rpl_stm_000001.test
index 268a10ad1fa..e7237af57c3 100644
--- a/mysql-test/suite/rpl/t/rpl_stm_000001.test
+++ b/mysql-test/suite/rpl/t/rpl_stm_000001.test
@@ -91,8 +91,8 @@ connection master1;
 sleep 3;
 select (@id := id) - id from t2;
 kill @id;
-# We don't drop t3 as this is a temporary table
 drop table t2;
+drop temporary table t3;
 connection master;
 # The get_lock function causes warning for unsafe statement.
 --disable_warnings