1include/master-slave.inc 2[connection master] 3==== Initialize ==== 4connection master; 5CREATE TABLE t1 (a CHAR(48)); 6CREATE TEMPORARY TABLE t1_tmp1(a INT); 7INSERT INTO t1 VALUES (UUID()); 8connection slave; 9==== Verify results on slave ==== 10SHOW STATUS LIKE "Slave_open_temp_tables"; 11Variable_name Value 12Slave_open_temp_tables 1 13connection master; 14disconnect master; 15connection master1; 16connection slave; 17==== Verify results on slave ==== 18SHOW STATUS LIKE "Slave_open_temp_tables"; 19Variable_name Value 20Slave_open_temp_tables 0 21==== Clean up ==== 22include/rpl_connect.inc [creating master] 23connection master; 24DROP TABLE t1; 25connection slave; 26include/rpl_reset.inc 27connection master; 28CREATE TABLE t1 (a int) engine=innodb; 29CREATE TABLE t2 ( i1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (i1) ); 30CREATE TABLE t3 ( i1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (i1) ); 31CREATE TRIGGER tr1 AFTER DELETE ON t2 FOR EACH ROW INSERT INTO t3 () VALUES (); 32CREATE TEMPORARY TABLE t1_tmp (i1 int); 33ALTER TABLE t1_tmp ADD COLUMN b INT; 34INSERT INTO t1 values(1); 35INSERT INTO t2 (i1) select * from t1; 36CREATE TEMPORARY TABLE t2_tmp (a int); 37ALTER TABLE t1_tmp ADD COLUMN c INT; 38### assertion: assert that there is one open temp table on slave 39connection slave; 40SHOW STATUS LIKE 'Slave_open_temp_tables'; 41Variable_name Value 42Slave_open_temp_tables 1 43connection master; 44DROP TABLE t1_tmp, t2; 45INSERT INTO t1 VALUES (1); 46DROP TEMPORARY TABLE t2_tmp; 47INSERT INTO t1 VALUES (2); 48connection slave; 49### assertion: assert that slave has no temporary tables opened 50SHOW STATUS LIKE 'Slave_open_temp_tables'; 51Variable_name Value 52Slave_open_temp_tables 0 53connection master; 54DROP TABLE t3, t1; 55connection slave; 56include/show_binlog_events.inc 57Log_name Pos Event_type Server_id End_log_pos Info 58slave-bin.000001 # Gtid # # GTID #-#-# 59slave-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a int) engine=innodb 60slave-bin.000001 # Gtid # # GTID #-#-# 61slave-bin.000001 # Query # # use `test`; CREATE TABLE t2 ( i1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (i1) ) 62slave-bin.000001 # Gtid # # GTID #-#-# 63slave-bin.000001 # Query # # use `test`; CREATE TABLE t3 ( i1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (i1) ) 64slave-bin.000001 # Gtid # # GTID #-#-# 65slave-bin.000001 # Query # # use `test`; CREATE DEFINER=`root`@`localhost` TRIGGER tr1 AFTER DELETE ON t2 FOR EACH ROW INSERT INTO t3 () VALUES () 66slave-bin.000001 # Gtid # # GTID #-#-# 67slave-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE t1_tmp (i1 int) 68slave-bin.000001 # Gtid # # GTID #-#-# 69slave-bin.000001 # Query # # use `test`; ALTER TABLE t1_tmp ADD COLUMN b INT 70slave-bin.000001 # Gtid # # BEGIN GTID #-#-# 71slave-bin.000001 # Query # # use `test`; INSERT INTO t1 values(1) 72slave-bin.000001 # Xid # # COMMIT /* XID */ 73slave-bin.000001 # Gtid # # BEGIN GTID #-#-# 74slave-bin.000001 # Annotate_rows # # INSERT INTO t2 (i1) select * from t1 75slave-bin.000001 # Table_map # # table_id: # (test.t2) 76slave-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F 77slave-bin.000001 # Query # # COMMIT 78slave-bin.000001 # Gtid # # GTID #-#-# 79slave-bin.000001 # Query # # DROP TEMPORARY TABLE IF EXISTS `test`.`t1_tmp` /* generated by server */ 80slave-bin.000001 # Gtid # # GTID #-#-# 81slave-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS `t2` /* generated by server */ 82slave-bin.000001 # Gtid # # BEGIN GTID #-#-# 83slave-bin.000001 # Annotate_rows # # INSERT INTO t1 VALUES (1) 84slave-bin.000001 # Table_map # # table_id: # (test.t1) 85slave-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F 86slave-bin.000001 # Xid # # COMMIT /* XID */ 87slave-bin.000001 # Gtid # # BEGIN GTID #-#-# 88slave-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (2) 89slave-bin.000001 # Xid # # COMMIT /* XID */ 90slave-bin.000001 # Gtid # # GTID #-#-# 91slave-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS `t3`,`t1` /* generated by server */ 92 93# Bug#55478 Row events wrongly apply on the temporary table of the same name 94# ========================================================================== 95connection master; 96# The statement should be binlogged 97CREATE TEMPORARY TABLE t1(c1 INT) ENGINE=InnoDB; 98 99# Case 1: CREATE TABLE t1 ... SELECT 100# ---------------------------------- 101 102# The statement generates row events on t1. And the rows events should 103# be inserted into the base table on slave. 104CREATE TABLE t1 ENGINE=MyISAM SELECT rand(); 105include/show_binlog_events.inc 106Log_name Pos Event_type Server_id End_log_pos Info 107master-bin.000001 # Gtid # # BEGIN GTID #-#-# 108master-bin.000001 # Query # # use `test`; CREATE TABLE `t1` ( 109 `rand()` double NOT NULL 110) ENGINE=MyISAM 111master-bin.000001 # Annotate_rows # # CREATE TABLE t1 ENGINE=MyISAM SELECT rand() 112master-bin.000001 # Table_map # # table_id: # (test.t1) 113master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F 114master-bin.000001 # Query # # COMMIT 115 116# Case 2: DROP TEMPORARY TABLE in a transacation 117# ---------------------------------------------- 118 119BEGIN; 120DROP TEMPORARY TABLE t1; 121# The rows event will binlogged after 'INSERT INTO t1 VALUES(1)' 122INSERT IGNORE INTO t1 VALUES(uuid()+0); 123COMMIT; 124include/show_binlog_events.inc 125Log_name Pos Event_type Server_id End_log_pos Info 126master-bin.000001 # Gtid # # BEGIN GTID #-#-# 127master-bin.000001 # Annotate_rows # # INSERT IGNORE INTO t1 VALUES(uuid()+0) 128master-bin.000001 # Table_map # # table_id: # (test.t1) 129master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F 130master-bin.000001 # Query # # COMMIT 131master-bin.000001 # Gtid # # BEGIN GTID #-#-# 132master-bin.000001 # Query # # DROP TEMPORARY TABLE IF EXISTS `test`.`t1` /* generated by server */ 133master-bin.000001 # Query # # COMMIT 134connection slave; 135# Compare the base table. 136include/diff_tables.inc [master:t1, slave:t1] 137 138connection master; 139DROP TABLE t1; 140include/rpl_end.inc 141