1DROP DATABASE IF EXISTS `drop-temp+table-test`; 2RESET MASTER; 3CREATE DATABASE `drop-temp+table-test`; 4USE `drop-temp+table-test`; 5CREATE TEMPORARY TABLE shortn1 (a INT); 6CREATE TEMPORARY TABLE `table:name` (a INT); 7CREATE TEMPORARY TABLE shortn2 (a INT); 8CREATE TEMPORARY TABLE tmp(c1 int); 9CREATE TEMPORARY TABLE tmp1(c1 int); 10CREATE TEMPORARY TABLE tmp2(c1 int); 11CREATE TEMPORARY TABLE tmp3(c1 int); 12CREATE TABLE t(c1 int); 13DROP TEMPORARY TABLE IF EXISTS tmp; 14DROP TEMPORARY TABLE IF EXISTS tmp; 15DROP TEMPORARY TABLE IF EXISTS tmp, tmp1; 16DROP TEMPORARY TABLE tmp3; 17DROP TABLE IF EXISTS tmp2, t; 18DROP TABLE IF EXISTS tmp2, t; 19SELECT GET_LOCK("a",10); 20GET_LOCK("a",10) 211 22USE test; 23SELECT GET_LOCK("a",10); 24GET_LOCK("a",10) 251 26include/show_binlog_events.inc 27Log_name Pos Event_type Server_id End_log_pos Info 28master-bin.000001 # Query # # CREATE DATABASE `drop-temp+table-test` 29master-bin.000001 # Query # # use `drop-temp+table-test`; CREATE TEMPORARY TABLE shortn1 (a INT) 30master-bin.000001 # Query # # use `drop-temp+table-test`; CREATE TEMPORARY TABLE `table:name` (a INT) 31master-bin.000001 # Query # # use `drop-temp+table-test`; CREATE TEMPORARY TABLE shortn2 (a INT) 32master-bin.000001 # Query # # use `drop-temp+table-test`; CREATE TEMPORARY TABLE tmp(c1 int) 33master-bin.000001 # Query # # use `drop-temp+table-test`; CREATE TEMPORARY TABLE tmp1(c1 int) 34master-bin.000001 # Query # # use `drop-temp+table-test`; CREATE TEMPORARY TABLE tmp2(c1 int) 35master-bin.000001 # Query # # use `drop-temp+table-test`; CREATE TEMPORARY TABLE tmp3(c1 int) 36master-bin.000001 # Query # # use `drop-temp+table-test`; CREATE TABLE t(c1 int) 37master-bin.000001 # Query # # use `drop-temp+table-test`; DROP TEMPORARY TABLE IF EXISTS `tmp` /* generated by server */ 38master-bin.000001 # Query # # use `drop-temp+table-test`; DROP TEMPORARY TABLE IF EXISTS `tmp1` /* generated by server */ 39master-bin.000001 # Query # # use `drop-temp+table-test`; DROP TEMPORARY TABLE `tmp3` /* generated by server */ 40master-bin.000001 # Query # # use `drop-temp+table-test`; DROP TEMPORARY TABLE IF EXISTS `tmp2` /* generated by server */ 41master-bin.000001 # Query # # use `drop-temp+table-test`; DROP TABLE IF EXISTS `t` /* generated by server */ 42master-bin.000001 # Query # # use `drop-temp+table-test`; DROP TABLE IF EXISTS `tmp2`,`t` /* generated by server */ 43master-bin.000001 # Query # # use `drop-temp+table-test`; DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `shortn2`,`table:name`,`shortn1` 44DROP DATABASE `drop-temp+table-test`; 45RESET MASTER; 46CREATE TABLE t1 ( i text ); 47CREATE TEMPORARY TABLE ttmp1 ( i text ); 48SET @@session.binlog_format=ROW; 49INSERT INTO t1 VALUES ('1'); 50SELECT @@session.binlog_format; 51@@session.binlog_format 52ROW 53include/show_binlog_events.inc 54Log_name Pos Event_type Server_id End_log_pos Info 55master-bin.000001 # Query # # use `test`; CREATE TABLE t1 ( i text ) 56master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE ttmp1 ( i text ) 57master-bin.000001 # Query # # BEGIN 58master-bin.000001 # Table_map # # table_id: # (test.t1) 59master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F 60master-bin.000001 # Xid # # COMMIT /* XID */ 61master-bin.000001 # Query # # use `test`; DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `ttmp1` 62DROP TABLE t1; 63RESET MASTER; 64# 65# Bug 83003: Using temporary tables on slaves increases GTID sequence number 66# 67SET @saved_binlog_format= @@SESSION.binlog_format; 68SET SESSION binlog_format= STATEMENT; 69CREATE TEMPORARY TABLE temp_needs_logging(a INT) ENGINE=InnoDB; 70SET SESSION binlog_format= @saved_binlog_format; 71CREATE TEMPORARY TABLE temp_trx(a INT) ENGINE=InnoDB; 72CREATE TEMPORARY TABLE temp_non_trx(a INT) ENGINE=MyISAM; 73CREATE TEMPORARY TABLE temp_needs_logging_in_stmt(a INT) ENGINE=InnoDB; 74SET SESSION sql_log_bin= 0; 75CREATE TEMPORARY TABLE temp_binlog_disabled(a INT) ENGINE=InnoDB; 76SET SESSION sql_log_bin= 1; 77include/show_binlog_events.inc 78Log_name Pos Event_type Server_id End_log_pos Info 79master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE temp_needs_logging(a INT) ENGINE=InnoDB 80master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE temp_trx(a INT) ENGINE=InnoDB 81master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE temp_non_trx(a INT) ENGINE=MyISAM 82master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE temp_needs_logging_in_stmt(a INT) ENGINE=InnoDB 83master-bin.000001 # Query # # use `test`; DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `temp_needs_logging_in_stmt`,`temp_trx`,`temp_needs_logging` 84master-bin.000001 # Query # # use `test`; DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `temp_non_trx` 85# 86# BUG#28642318: POINT IN TIME RECOVERY USING MYSQLBINLOG BROKEN 87# WITH TEMPORARY TABLE -> ERRORS 88# Test case for DELETE query. 89RESET MASTER; 90# Set up. 91SET @save_binlog_format= @@session.binlog_format; 92SET @@session.binlog_format=STATEMENT; 93CREATE TABLE t1 (a INT) ENGINE=INNODB; 94SET @@session.binlog_format=STATEMENT; 95CREATE TEMPORARY TABLE t1 (b BLOB) ENGINE=INNODB; 96DELETE d1, d2 FROM t1 AS d1, t1 AS d2 WHERE d1.a<>d2.a; 97DROP TABLE t1; 98# DELETE query fails with table re-open error without patch. 99# Clean up. 100DROP TABLE IF EXISTS t1; 101DROP TABLE IF EXISTS t1; 102RESET MASTER; 103# Test case for DROP query. 104CREATE TABLE t1 (a INT) ENGINE=INNODB; 105CREATE TEMPORARY TABLE t1 (b BLOB) ENGINE=INNODB; 106DROP TABLE t1; 107DROP TABLE t1; 108# DROP table query fails with unknown table error without patch. 109# Clean up 110SET @@session.binlog_format= @save_binlog_format; 111RESET MASTER; 112