1# ==== Purpose ==== 2# 3# Test that temporary tables are correctly replicated after switching to ROW format in MIX mode. 4# This test case will test the condition of the bug#40013. 5# The test step is: 6# 1: create temp table on connection 'master'; 7# 2: switch to ROW format using 'INSERT INTO t1 VALUES (UUID());' 8# 3: disconnect 'master' and connect to a new connection 'master1'; 9# 4: sync to slave and check the number of temp tables on slave. 10# 11 12source include/have_binlog_format_mixed.inc; 13source include/have_innodb.inc; 14source include/master-slave.inc; 15 16--echo ==== Initialize ==== 17 18--connection master 19 20CREATE TABLE t1 (a CHAR(48)); 21CREATE TEMPORARY TABLE t1_tmp1(a INT); 22INSERT INTO t1 VALUES (UUID()); 23 24sync_slave_with_master; 25 26--echo ==== Verify results on slave ==== 27SHOW STATUS LIKE "Slave_open_temp_tables"; 28 29--connection master 30 31disconnect master; 32--connection master1 33 34# waiting DROP TEMPORARY TABLE event to be written into binlog 35let $wait_binlog_event= DROP; 36source include/wait_for_binlog_event.inc; 37 38sync_slave_with_master; 39 40--echo ==== Verify results on slave ==== 41SHOW STATUS LIKE "Slave_open_temp_tables"; 42 43--echo ==== Clean up ==== 44 45--let $rpl_connection_name= master 46--let $rpl_server_number= 1 47--source include/rpl_connect.inc 48--connection master 49DROP TABLE t1; 50 51sync_slave_with_master; 52 53# 54# BUG#43046: mixed mode switch to row format with temp table lead to wrong 55# result 56# 57# NOTES 58# ===== 59# 60# 1. Temporary tables cannot be logged using the row-based 61# format. Thus, once row-based logging is used, all subsequent 62# statements using that table are unsafe, and we approximate this 63# condition by treating all statements made by that client as 64# unsafe until the client no longer holds any temporary tables. 65# 66# 2. Two different connections can use the same temporary table 67# name without conflicting with each other or with an 68# existing non-TEMPORARY table of the same name. 69# 70# DESCRIPTION 71# =========== 72# 73# The test is implemented as follows: 74# 1. create regular tables 75# 2. create a temporary table t1_tmp: should be logged as statement 76# 3. issue an alter table: should be logged as statement 77# 4. issue statement that forces switch to RBR 78# 5. create another temporary table t2_tmp: should not be logged 79# 6. issue alter table on t1_tmp: should not be logged 80# 7. drop t1_tmp and regular table on same statement: should log both in 81# statement format (but different statements) 82# 8. issue deterministic insert: logged as row (because t2_tmp still 83# exists). 84# 9. drop t2_tmp and issue deterministic statement: should log drop and 85# query in statement format (show switch back to STATEMENT format) 86# 10. in the end the slave should not have open temp tables. 87# 88 89--source include/rpl_reset.inc 90-- connection master 91 92# action: setup environment 93CREATE TABLE t1 (a int) engine=innodb; 94CREATE TABLE t2 ( i1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (i1) ); 95CREATE TABLE t3 ( i1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (i1) ); 96CREATE TRIGGER tr1 AFTER DELETE ON t2 FOR EACH ROW INSERT INTO t3 () VALUES (); 97 98# assertion: assert that CREATE is logged as STATEMENT 99CREATE TEMPORARY TABLE t1_tmp (i1 int); 100 101# assertion: assert that ALTER TABLE is logged as STATEMENT 102ALTER TABLE t1_tmp ADD COLUMN b INT; 103 104# action: force switch to RBR 105INSERT INTO t1 values(1); 106INSERT INTO t2 (i1) select * from t1; 107 108# assertion: assert that t2_tmp will not make into the binlog (RBR logging atm) 109CREATE TEMPORARY TABLE t2_tmp (a int); 110 111# assertion: assert that ALTER TABLE on t1_tmp will not make into the binlog 112ALTER TABLE t1_tmp ADD COLUMN c INT; 113 114-- echo ### assertion: assert that there is one open temp table on slave 115-- sync_slave_with_master 116SHOW STATUS LIKE 'Slave_open_temp_tables'; 117 118-- connection master 119 120# assertion: assert that both drops are logged 121DROP TABLE t1_tmp, t2; 122 123# assertion: assert that statement is logged as row (master still has one 124# opened temporary table - t2_tmp. 125INSERT INTO t1 VALUES (1); 126 127# assertion: assert that DROP TABLE *is* logged despite CREATE is not. 128DROP TEMPORARY TABLE t2_tmp; 129 130# assertion: assert that statement is now logged as STMT (mixed mode switches 131# back to STATEMENT). 132INSERT INTO t1 VALUES (2); 133 134-- sync_slave_with_master 135 136-- echo ### assertion: assert that slave has no temporary tables opened 137SHOW STATUS LIKE 'Slave_open_temp_tables'; 138 139-- connection master 140 141# action: drop remaining tables 142DROP TABLE t3, t1; 143 144-- sync_slave_with_master 145 146-- source include/show_binlog_events.inc 147 148--echo 149--echo # Bug#55478 Row events wrongly apply on the temporary table of the same name 150--echo # ========================================================================== 151connection master; 152 153let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1); 154let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1); 155 156--echo # The statement should be binlogged 157CREATE TEMPORARY TABLE t1(c1 INT) ENGINE=InnoDB; 158 159--echo 160--echo # Case 1: CREATE TABLE t1 ... SELECT 161--echo # ---------------------------------- 162let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1); 163let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1); 164 165--echo 166--echo # The statement generates row events on t1. And the rows events should 167--echo # be inserted into the base table on slave. 168CREATE TABLE t1 ENGINE=MyISAM SELECT rand(); 169 170source include/show_binlog_events.inc; 171let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1); 172let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1); 173 174--echo 175--echo # Case 2: DROP TEMPORARY TABLE in a transacation 176--echo # ---------------------------------------------- 177--echo 178 179BEGIN; 180DROP TEMPORARY TABLE t1; 181 182# The patch for BUG#55478 fixed the problem only on RBR. The problem on SBR 183# will be fixed by the patch for bug#55709. So This statement cannot be 184# executed until Bug#55709 is fixed 185# 186# INSERT INTO t1 VALUES(1); 187 188--echo # The rows event will binlogged after 'INSERT INTO t1 VALUES(1)' 189--disable_warnings 190INSERT IGNORE INTO t1 VALUES(uuid()+0); 191--enable_warnings 192COMMIT; 193 194source include/show_binlog_events.inc; 195 196--sync_slave_with_master 197 198--echo # Compare the base table. 199--let $diff_tables= master:t1, slave:t1 200--source include/diff_tables.inc 201 202--echo 203connection master; 204DROP TABLE t1; 205--source include/rpl_end.inc 206