# BUG#43046: mixed mode switch to row format with temp table lead to wrong # result # # NOTES # ===== # # 1. Two different connections can use the same temporary table # name without conflicting with each other or with an # existing non-TEMPORARY table of the same name. # # DESCRIPTION # =========== # # The test is implemented as follows: # 1. create regular tables # 2. create a temporary table t1_tmp: should not be logged # 3. issue an alter table: should not be logged # 4. issue statement that forces the query to be logged in Row format. # 5. create another temporary table t2_tmp: should not be logged # 6. issue alter table on t1_tmp: should not be logged # 7. drop t1_tmp and regular table on same statement: should log both in # statement format (but different statements) # 8. issue deterministic insert: logged as statement as the statement is # safe. # 9. drop t2_tmp and issue deterministic statement: should log drop and # query in statement format. # 10. in the end the slave should not have open temp tables. # --source include/set_privilege_checks_user_as_system_user.inc source include/master-slave.inc; source include/have_binlog_format_mixed.inc; source include/have_myisam.inc; -- connection master # action: setup environment 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 (); # assertion: assert that CREATE is not logged. CREATE TEMPORARY TABLE t1_tmp (i1 int); # assertion: assert that ALTER TABLE is not logged. ALTER TABLE t1_tmp ADD COLUMN b INT; # action: Log this in ROW format DELETE FROM t2; # assertion: assert that t2_tmp will not make into the binlog. CREATE TEMPORARY TABLE t2_tmp (a int); # assertion: assert that ALTER TABLE on t1_tmp will not make into the binlog ALTER TABLE t1_tmp ADD COLUMN c INT; -- echo ### assertion: assert that slave has no temp table opened. --source include/sync_slave_sql_with_master.inc SHOW STATUS LIKE 'Replica_open_temp_tables'; -- connection master # assertion: assert that both drops are logged DROP TABLE t1_tmp, t2; # assertion: assert that statement is logged as statement format. INSERT INTO t1 VALUES (1); # assertion: assert that DROP TABLE *is* logged despite CREATE is not. DROP TEMPORARY TABLE t2_tmp; # assertion: assert that statement is now logged as STMT. INSERT INTO t1 VALUES (2); --source include/sync_slave_sql_with_master.inc -- echo ### assertion: assert that slave has no temporary tables opened SHOW STATUS LIKE 'Replica_open_temp_tables'; -- connection master # action: drop remaining tables DROP TABLE t3, t1; --source include/sync_slave_sql_with_master.inc -- source include/show_binlog_events.inc --echo --echo # Bug#55478 Row events wrongly apply on the temporary table of the same name --echo # ========================================================================== connection master; let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1); let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1); --echo # The statement should be binlogged CREATE TEMPORARY TABLE t1(c1 INT) ENGINE=InnoDB; --echo --echo # Case 1: CREATE TABLE t1 ... SELECT --echo # ---------------------------------- let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1); let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1); --echo --echo # The statement generates row events on t1. And the rows events should --echo # be inserted into the base table on slave. CREATE TABLE t1 ENGINE=MyISAM SELECT rand(); source include/show_binlog_events.inc; let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1); let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1); --echo --echo # Case 2: DROP TEMPORARY TABLE in a transacation --echo # ---------------------------------------------- --echo BEGIN; DROP TEMPORARY TABLE t1; # The patch for BUG#55478 fixed the problem only on RBR. The problem on SBR # will be fixed by the patch for bug#55709. So This statement cannot be # executed until Bug#55709 is fixed # #INSERT INTO t1 VALUES(1); --echo # The rows event will binlogged before 'DROP TEMPORARY TABLE t1', --echo # as t1 is non-transactional table INSERT INTO t1 VALUES(Rand()); COMMIT; source include/show_binlog_events.inc; --source include/sync_slave_sql_with_master.inc --echo # Compare the base table. --let $diff_tables= master:t1, slave:t1 --source include/diff_tables.inc --echo connection master; DROP TABLE t1; --source include/sync_slave_sql_with_master.inc --source include/rpl_end.inc