Edit File: rpl_temp_table_mix_row.result
include/master-slave.inc [connection master] ==== Initialize ==== [on master] CREATE TABLE t1 (a CHAR(48)); CREATE TEMPORARY TABLE t1_tmp1(a INT); INSERT INTO t1 VALUES (UUID()); [on slave] ==== Verify results on slave ==== SHOW STATUS LIKE "Slave_open_temp_tables"; Variable_name Value Slave_open_temp_tables 1 [on master] [on master1] [on slave] ==== Verify results on slave ==== SHOW STATUS LIKE "Slave_open_temp_tables"; Variable_name Value Slave_open_temp_tables 0 ==== Clean up ==== [on master] include/rpl_connect.inc [creating master] DROP TABLE t1; [on slave] include/rpl_reset.inc CREATE TABLE t1 (a int); CREATE TABLE t2 ( i1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (i1) ); CREATE TABLE t3 ( i1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (i1) ); CREATE TRIGGER tr1 AFTER DELETE ON t2 FOR EACH ROW INSERT INTO t3 () VALUES (); CREATE TEMPORARY TABLE t1_tmp (i1 int); ALTER TABLE t1_tmp ADD COLUMN b INT; DELETE FROM t2; CREATE TEMPORARY TABLE t2_tmp (a int); ALTER TABLE t1_tmp ADD COLUMN c INT; ### assertion: assert that there is one open temp table on slave SHOW STATUS LIKE 'Slave_open_temp_tables'; Variable_name Value Slave_open_temp_tables 1 DROP TABLE t1_tmp, t2; INSERT INTO t1 VALUES (1); DROP TEMPORARY TABLE t2_tmp; INSERT INTO t1 VALUES (2); ### assertion: assert that slave has no temporary tables opened SHOW STATUS LIKE 'Slave_open_temp_tables'; Variable_name Value Slave_open_temp_tables 0 DROP TABLE t3, t1; show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info slave-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a int) slave-bin.000001 # Query # # use `test`; CREATE TABLE t2 ( i1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (i1) ) slave-bin.000001 # Query # # use `test`; CREATE TABLE t3 ( i1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (i1) ) slave-bin.000001 # Query # # use `test`; CREATE DEFINER=`root`@`localhost` TRIGGER tr1 AFTER DELETE ON t2 FOR EACH ROW INSERT INTO t3 () VALUES () slave-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE t1_tmp (i1 int) slave-bin.000001 # Query # # use `test`; ALTER TABLE t1_tmp ADD COLUMN b INT slave-bin.000001 # Query # # DROP TEMPORARY TABLE IF EXISTS `test`.`t1_tmp` /* generated by server */ slave-bin.000001 # Query # # use `test`; DROP TABLE `t2` /* generated by server */ slave-bin.000001 # Query # # BEGIN slave-bin.000001 # Table_map # # table_id: # (test.t1) slave-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F slave-bin.000001 # Xid # # COMMIT /* XID */ slave-bin.000001 # Query # # DROP TEMPORARY TABLE IF EXISTS `test`.`t2_tmp` /* generated by server */ slave-bin.000001 # Query # # BEGIN slave-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (2) slave-bin.000001 # Xid # # COMMIT /* XID */ slave-bin.000001 # Query # # use `test`; DROP TABLE `t3`,`t1` /* generated by server */ # Bug#55478 Row events wrongly apply on the temporary table of the same name # ========================================================================== # The statement should be binlogged CREATE TEMPORARY TABLE t1(c1 INT) ENGINE=InnoDB; # Case 1: CREATE TABLE t1 ... SELECT # ---------------------------------- # The statement generates row events on t1. And the rows events should # be inserted into the base table on slave. CREATE TABLE t1 ENGINE=MyISAM SELECT rand(); show binlog events in 'master-bin.000001' from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; CREATE TABLE `t1` ( `rand()` double NOT NULL DEFAULT '0' ) ENGINE=MyISAM master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F master-bin.000001 # Query # # COMMIT # Case 2: DROP TEMPORARY TABLE in a transacation # ---------------------------------------------- BEGIN; DROP TEMPORARY TABLE t1; # The rows event will binlogged before 'DROP TEMPORARY TABLE t1', # as t1 is non-transactional table INSERT INTO t1 VALUES(Rand()); COMMIT; show binlog events in 'master-bin.000001' from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # BEGIN master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F master-bin.000001 # Query # # COMMIT master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # DROP TEMPORARY TABLE IF EXISTS `test`.`t1` /* generated by server */ master-bin.000001 # Query # # COMMIT # Compare the base table. include/diff_tables.inc [master:t1, slave:t1] DROP TABLE t1; include/rpl_end.inc
Back to File Manager