commit 52c83c687aafe9342758786f16942172d1fef878 Author: Laurynas Biveinis Date: Thu Dec 1 12:58:57 2016 +0200 Fix bug 83003 / 1539504 (CREATE TEMPORARY TABLE creates a transaction in binary log on read only server) Previously, a session disconnect causes DROP TEMPORARY TABLE IF EXISTS to be binlogged for all the opened temp tables in that session. Even though temporary table operation are not otherwise binlogged in row or mixed mode, this was done regardless of binary log format in use, as it was not tracked, whether a particular temp table was not created in STATEMENT mode - in which case it does need the DROP. For ROW/MIXED users, this behavior was causing spurious binlog writes and GTIDs generated on otherwise read only servers. Fix by tracking the binlog format at temporary table create time (open_table_uncached and after final decide_logging_format call for CREATE ... SELECT), and using it to decide whether a DROP should be logged or not in close_temporary_tables. diff --git a/mysql-test/extra/binlog_tests/drop_temp_table.test b/mysql-test/extra/binlog_tests/drop_temp_table.test index bc756634151..1525d873712 100644 --- a/mysql-test/extra/binlog_tests/drop_temp_table.test +++ b/mysql-test/extra/binlog_tests/drop_temp_table.test @@ -60,8 +60,11 @@ connection con2; SELECT GET_LOCK("a",10); let $VERSION=`SELECT VERSION()`; -let $wait_binlog_event=DROP /*!40005 TEMPORARY */ TABLE IF EXISTS; -source include/wait_for_binlog_event.inc; +if (`SELECT @@GLOBAL.binlog_format = 'STATEMENT'`) +{ + let $wait_binlog_event=DROP /*!40005 TEMPORARY */ TABLE IF EXISTS; + source include/wait_for_binlog_event.inc; +} source include/show_binlog_events.inc; DROP DATABASE `drop-temp+table-test`; @@ -78,27 +81,60 @@ DROP DATABASE `drop-temp+table-test`; # RESET MASTER; +if (`SELECT @@GLOBAL.binlog_format = 'STATEMENT'`) +{ + CREATE TABLE t1 ( i text ); + + --connect(con1,localhost,root,,) + CREATE TEMPORARY TABLE ttmp1 ( i text ); + SET @@session.binlog_format='ROW'; + INSERT INTO t1 VALUES ('1'); + SELECT @@session.binlog_format; + --disconnect con1 + + -- connection default + --let $wait_binlog_event= DROP + --source include/wait_for_binlog_event.inc + --let $mask_binlog_commit_events= 1 + -- source include/show_binlog_events.inc + --let $mask_binlog_commit_events= 0 + + DROP TABLE t1; + RESET MASTER; +} -CREATE TABLE t1 ( i text ); +# End of 4.1 tests ---connect(con1,localhost,root,,) -CREATE TEMPORARY TABLE ttmp1 ( i text ); -SET @@session.binlog_format='ROW'; -INSERT INTO t1 VALUES ('1'); -SELECT @@session.binlog_format; ---disconnect con1 +--echo # +--echo # Bug 83003: Using temporary tables on slaves increases GTID sequence number +--echo # --- connection default ---let $wait_binlog_event= DROP ---source include/wait_for_binlog_event.inc ---let $mask_binlog_commit_events= 1 --- source include/show_binlog_events.inc ---let $mask_binlog_commit_events= 0 -RESET MASTER; +--source include/count_sessions.inc -DROP TABLE t1; +--connect (con1,localhost,root,,) -# End of 4.1 tests +SET @saved_binlog_format= @@SESSION.binlog_format; +SET SESSION binlog_format= STATEMENT; +CREATE TEMPORARY TABLE temp_needs_logging(a INT) ENGINE=InnoDB; +SET SESSION binlog_format= @saved_binlog_format; + +# Check with both transactional and non-transactional tables as those are logged +# separately in close_temporary_tables. +CREATE TEMPORARY TABLE temp_trx(a INT) ENGINE=InnoDB; +CREATE TEMPORARY TABLE temp_non_trx(a INT) ENGINE=MyISAM; + +CREATE TEMPORARY TABLE temp_needs_logging_in_stmt(a INT) ENGINE=InnoDB; +SET SESSION sql_log_bin= 0; +CREATE TEMPORARY TABLE temp_binlog_disabled(a INT) ENGINE=InnoDB; +SET SESSION sql_log_bin= 1; + +--disconnect con1 +--connection default +--source include/wait_until_count_sessions.inc + +--source include/show_binlog_events.inc + +# End of 5.6 tests --echo # --echo # BUG#21638823: ASSERTION FAILED: @@ -126,8 +162,11 @@ XA END 'idle_at_disconnect'; --disconnect con3 --source include/wait_until_disconnected.inc --connection default ---let $wait_binlog_event= DROP ---source include/wait_for_binlog_event.inc +if (`SELECT @@GLOBAL.binlog_format = 'STATEMENT'`) +{ + --let $wait_binlog_event= DROP + --source include/wait_for_binlog_event.inc +} --echo # Dump binlog to show that, either the generated DROP comes after tx --echo # commit (stmt), or there is no trace of the XA txn and the temp --echo # table (row or mixed) diff --git a/mysql-test/extra/binlog_tests/mix_innodb_myisam_binlog.test b/mysql-test/extra/binlog_tests/mix_innodb_myisam_binlog.test index 86f0930c7b4..9e443827658 100644 --- a/mysql-test/extra/binlog_tests/mix_innodb_myisam_binlog.test +++ b/mysql-test/extra/binlog_tests/mix_innodb_myisam_binlog.test @@ -240,13 +240,16 @@ disconnect con2; # Wait until con2 disconnects --source include/wait_until_disconnected.inc connection con3; -# Bug #22084462 - MYSQL TEST BINLOG.BINLOG_ROW_MIX_INNODB_MYISAM FAILS -# Wait to ensure that drop temporary tables events -# are present in binary log. ---let $event_sequence= (Anonymous_)?Gtid # !Q(DROP.*TEMPORARY.*) # (Anonymous_)?Gtid # !Q(DROP.*TEMPORARY.*) ---let $limit= 0,4 ---let $wait_for_binlog_events= 1 ---source include/assert_binlog_events.inc +if (`SELECT @@GLOBAL.binlog_format = 'STATEMENT'`) +{ + # Bug #22084462 - MYSQL TEST BINLOG.BINLOG_ROW_MIX_INNODB_MYISAM FAILS + # Wait to ensure that drop temporary tables events + # are present in binary log. + --let $event_sequence= (Anonymous_)?Gtid # !Q(DROP.*TEMPORARY.*) # (Anonymous_)?Gtid # !Q(DROP.*TEMPORARY.*) + --let $limit= 0,4 + --let $wait_for_binlog_events= 1 + --source include/assert_binlog_events.inc +} select get_lock("lock1",60); source include/show_binlog_events.inc; do release_lock("lock1"); diff --git a/mysql-test/extra/binlog_tests/tmp_table.test b/mysql-test/extra/binlog_tests/tmp_table.test index f65828c4cbd..2d5852f1056 100644 --- a/mysql-test/extra/binlog_tests/tmp_table.test +++ b/mysql-test/extra/binlog_tests/tmp_table.test @@ -139,8 +139,11 @@ create temporary table t1(i int); -- disconnect con1 -- connection default --- let $wait_binlog_event= DROP --- source include/wait_for_binlog_event.inc +if (`SELECT @@GLOBAL.binlog_format = 'STATEMENT'`) +{ + -- let $wait_binlog_event= DROP + -- source include/wait_for_binlog_event.inc +} # action: insert in the t1. This would cause the the test to fail, # because when replaying the binlog the previous implicit drop @@ -151,8 +154,11 @@ insert into t1 values(1); -- disconnect con2 -- connection default --- let $wait_binlog_event= DROP --- source include/wait_for_binlog_event.inc +if (`SELECT @@GLOBAL.binlog_format = 'STATEMENT'`) +{ + -- let $wait_binlog_event= DROP + -- source include/wait_for_binlog_event.inc +} -- eval DROP DATABASE $dbname FLUSH LOGS; diff --git a/mysql-test/suite/rpl/t/rpl_gtid_disconnect_drop_temporary_table.test b/mysql-test/extra/rpl_tests/gtid_disconnect_drop_temporary_table.test similarity index 87% rename from mysql-test/suite/rpl/t/rpl_gtid_disconnect_drop_temporary_table.test rename to mysql-test/extra/rpl_tests/gtid_disconnect_drop_temporary_table.test index 12341d4640b..45ceb2c25f3 100644 --- a/mysql-test/suite/rpl/t/rpl_gtid_disconnect_drop_temporary_table.test +++ b/mysql-test/extra/rpl_tests/gtid_disconnect_drop_temporary_table.test @@ -92,10 +92,13 @@ while ($i <= 4) --echo # Disconnecting. --disconnect con1 - # Verify that the binlog contains the expected events. - --let $event_sequence= (Anonymous_)?Gtid # !Q(DROP.*TEMPORARY.*) - --let $wait_for_binlog_events= 1 - --source include/assert_binlog_events.inc + if (`SELECT @@GLOBAL.binlog_format = 'STATEMENT'`) + { + # Verify that the binlog contains the expected events. + --let $event_sequence= (Anonymous_)?Gtid # !Q(DROP.*TEMPORARY.*) + --let $wait_for_binlog_events= 1 + --source include/assert_binlog_events.inc + } # Sync with slave and assert slave has dropped the temp table. --source include/sync_slave_sql_with_master.inc @@ -105,6 +108,11 @@ while ($i <= 4) # Verify that one gtid was added if gtid_mode=on --connection master --let $gtid_step_gtid_mode_agnostic= 1 + --let $gtid_step_count= 0 + if (`SELECT @@GLOBAL.binlog_format = 'STATEMENT'`) + { + --let $gtid_step_count= 1 + } --source include/gtid_step_assert.inc --inc $i diff --git a/mysql-test/extra/rpl_tests/rpl_drop_temp.test b/mysql-test/extra/rpl_tests/rpl_drop_temp.test index 591c8922319..bea9aa6057a 100644 --- a/mysql-test/extra/rpl_tests/rpl_drop_temp.test +++ b/mysql-test/extra/rpl_tests/rpl_drop_temp.test @@ -24,9 +24,12 @@ disconnect con_temp; --source include/wait_until_disconnected.inc connection master; --- let $wait_binlog_event= DROP --- source include/wait_for_binlog_event.inc -sync_slave_with_master; +if (`SELECT @@SESSION.binlog_format = 'STATEMENT'`) +{ + -- let $wait_binlog_event= DROP + -- source include/wait_for_binlog_event.inc + sync_slave_with_master; +} connection slave; show status like 'Slave_open_temp_tables'; @@ -99,8 +102,11 @@ SET SESSION binlog_format='ROW'; --disconnect con1 -- connection master ---let $wait_binlog_event= DROP ---source include/wait_for_binlog_event.inc +if (`SELECT @@SESSION.binlog_format = 'STATEMENT'`) +{ + --let $wait_binlog_event= DROP + --source include/wait_for_binlog_event.inc +} --sync_slave_with_master SHOW STATUS LIKE 'Slave_open_temp_tables'; diff --git a/mysql-test/extra/rpl_tests/rpl_rewrt_db.test b/mysql-test/extra/rpl_tests/rpl_rewrt_db.test index 0d24a9ba8e2..de21440a099 100644 --- a/mysql-test/extra/rpl_tests/rpl_rewrt_db.test +++ b/mysql-test/extra/rpl_tests/rpl_rewrt_db.test @@ -86,6 +86,10 @@ set sql_log_bin= 1; # End of 4.1 tests +# Test only STATEMENT as for MIX/ROW no DROPS will be logged +if (`SELECT @@SESSION.binlog_format = 'STATEMENT'`) +{ + --echo --echo **** --echo **** Bug #46861 Auto-closing of temporary tables broken by replicate-rewrite-db @@ -249,6 +253,8 @@ DROP DATABASE database_slave_temp_02; DROP DATABASE database_slave_temp_03; SET sql_log_bin= 1; +} # if (`SELECT @@SESSION.binlog_format = 'STATEMENT') + connection master; --source include/sync_slave_sql_with_master.inc diff --git a/mysql-test/suite/binlog/r/binlog_drop_temp_table_on_disconnect_row_mix.result b/mysql-test/suite/binlog/r/binlog_drop_temp_table_on_disconnect_row_mix.result new file mode 100644 index 00000000000..944dcbd4ace --- /dev/null +++ b/mysql-test/suite/binlog/r/binlog_drop_temp_table_on_disconnect_row_mix.result @@ -0,0 +1,22 @@ +RESET MASTER; +CREATE TABLE t1(a INT) ENGINE=InnoDB; +CREATE TABLE t2(a INT) ENGINE=MyISAM; +SET @saved_binlog_format= @@SESSION.binlog_format; +SET SESSION binlog_format= 'STATEMENT'; +CREATE TEMPORARY TABLE tmp10 (a INT); +SET SESSION binlog_format= @saved_binlog_format; +CREATE TEMPORARY TABLE tmp1 ENGINE=InnoDB SELECT * FROM t1; +CREATE TEMPORARY TABLE tmp2 ENGINE=MyISAM SELECT * FROM t2; +CREATE TEMPORARY TABLE tmp3 ENGINE=MyISAM SELECT * FROM t1; +CREATE TEMPORARY TABLE tmp4 ENGINE=InnoDB SELECT * FROM t2; +CREATE TEMPORARY TABLE tmp5 (a INT) ENGINE=InnoDB; +CREATE TEMPORARY TABLE tmp6 (a INT) ENGINE=MyISAM; +CREATE TEMPORARY TABLE tmp7 LIKE t1; +CREATE TEMPORARY TABLE tmp8 LIKE t2; +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # use `test`; CREATE TABLE t1(a INT) ENGINE=InnoDB +master-bin.000001 # Query # # use `test`; CREATE TABLE t2(a INT) ENGINE=MyISAM +master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE tmp10 (a INT) +master-bin.000001 # Query # # use `test`; DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `tmp10` +DROP TABLE t1, t2; diff --git a/mysql-test/suite/binlog/r/binlog_drop_temp_table_on_disconnect_stmt.result b/mysql-test/suite/binlog/r/binlog_drop_temp_table_on_disconnect_stmt.result new file mode 100644 index 00000000000..a0432263cc3 --- /dev/null +++ b/mysql-test/suite/binlog/r/binlog_drop_temp_table_on_disconnect_stmt.result @@ -0,0 +1,28 @@ +RESET MASTER; +CREATE TABLE t1(a INT) ENGINE=InnoDB; +CREATE TABLE t2(a INT) ENGINE=MyISAM; +CREATE TEMPORARY TABLE tmp1 ENGINE=InnoDB SELECT * FROM t1; +CREATE TEMPORARY TABLE tmp2 ENGINE=MyISAM SELECT * FROM t2; +CREATE TEMPORARY TABLE tmp3 ENGINE=MyISAM SELECT * FROM t1; +CREATE TEMPORARY TABLE tmp4 ENGINE=InnoDB SELECT * FROM t2; +CREATE TEMPORARY TABLE tmp5 (a INT) ENGINE=InnoDB; +CREATE TEMPORARY TABLE tmp6 (a INT) ENGINE=MyISAM; +CREATE TEMPORARY TABLE tmp7 LIKE t1; +CREATE TEMPORARY TABLE tmp8 LIKE t2; +SET SESSION binlog_format= 'ROW'; +CREATE TEMPORARY TABLE tmp10 (a INT); +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # use `test`; CREATE TABLE t1(a INT) ENGINE=InnoDB +master-bin.000001 # Query # # use `test`; CREATE TABLE t2(a INT) ENGINE=MyISAM +master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE tmp1 ENGINE=InnoDB SELECT * FROM t1 +master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE tmp2 ENGINE=MyISAM SELECT * FROM t2 +master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE tmp3 ENGINE=MyISAM SELECT * FROM t1 +master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE tmp4 ENGINE=InnoDB SELECT * FROM t2 +master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE tmp5 (a INT) ENGINE=InnoDB +master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE tmp6 (a INT) ENGINE=MyISAM +master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE tmp7 LIKE t1 +master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE tmp8 LIKE t2 +master-bin.000001 # Query # # use `test`; DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `tmp7`,`tmp5`,`tmp4`,`tmp1` +master-bin.000001 # Query # # use `test`; DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `tmp8`,`tmp6`,`tmp3`,`tmp2` +DROP TABLE t1, t2; diff --git a/mysql-test/suite/binlog/r/binlog_row_drop_tmp_tbl.result b/mysql-test/suite/binlog/r/binlog_row_drop_tmp_tbl.result deleted file mode 100644 index a07a47b896c..00000000000 --- a/mysql-test/suite/binlog/r/binlog_row_drop_tmp_tbl.result +++ /dev/null @@ -1,92 +0,0 @@ -DROP DATABASE IF EXISTS `drop-temp+table-test`; -RESET MASTER; -CREATE DATABASE `drop-temp+table-test`; -USE `drop-temp+table-test`; -CREATE TEMPORARY TABLE shortn1 (a INT); -CREATE TEMPORARY TABLE `table:name` (a INT); -CREATE TEMPORARY TABLE shortn2 (a INT); -CREATE TEMPORARY TABLE tmp(c1 int); -CREATE TEMPORARY TABLE tmp1(c1 int); -CREATE TEMPORARY TABLE tmp2(c1 int); -CREATE TEMPORARY TABLE tmp3(c1 int); -CREATE TABLE t(c1 int); -DROP TEMPORARY TABLE IF EXISTS tmp; -DROP TEMPORARY TABLE IF EXISTS tmp; -DROP TEMPORARY TABLE IF EXISTS tmp, tmp1; -DROP TEMPORARY TABLE tmp3; -DROP TABLE IF EXISTS tmp2, t; -DROP TABLE IF EXISTS tmp2, t; -SELECT GET_LOCK("a",10); -GET_LOCK("a",10) -1 -USE test; -SELECT GET_LOCK("a",10); -GET_LOCK("a",10) -1 -include/show_binlog_events.inc -Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 # Query # # CREATE DATABASE `drop-temp+table-test` -master-bin.000001 # Query # # use `drop-temp+table-test`; CREATE TABLE t(c1 int) -master-bin.000001 # Query # # use `drop-temp+table-test`; DROP TEMPORARY TABLE IF EXISTS `tmp` /* generated by server */ -master-bin.000001 # Query # # use `drop-temp+table-test`; DROP TEMPORARY TABLE IF EXISTS `tmp` /* generated by server */ -master-bin.000001 # Query # # use `drop-temp+table-test`; DROP TEMPORARY TABLE IF EXISTS `tmp1`,`tmp` /* generated by server */ -master-bin.000001 # Query # # use `drop-temp+table-test`; DROP TEMPORARY TABLE IF EXISTS `tmp3` /* generated by server */ -master-bin.000001 # Query # # use `drop-temp+table-test`; DROP TABLE IF EXISTS `t` /* generated by server */ -master-bin.000001 # Query # # use `drop-temp+table-test`; DROP TEMPORARY TABLE IF EXISTS `tmp2` /* generated by server */ -master-bin.000001 # Query # # use `drop-temp+table-test`; DROP TABLE IF EXISTS `tmp2`,`t` /* generated by server */ -master-bin.000001 # Query # # use `drop-temp+table-test`; DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `shortn2`,`table:name`,`shortn1` -DROP DATABASE `drop-temp+table-test`; -RESET MASTER; -CREATE TABLE t1 ( i text ); -CREATE TEMPORARY TABLE ttmp1 ( i text ); -SET @@session.binlog_format='ROW'; -INSERT INTO t1 VALUES ('1'); -SELECT @@session.binlog_format; -@@session.binlog_format -ROW -include/show_binlog_events.inc -Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 # Query # # use `test`; CREATE TABLE t1 ( i text ) -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 # # use `test`; DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `ttmp1` -RESET MASTER; -DROP TABLE t1; -# -# BUG#21638823: ASSERTION FAILED: -# THD->GET_TRANSACTION()->IS_EMPTY(TRANSACTION_CTX::STMT) || THD -# -# Test case first checks that it is possible to terminate a connection -# with a temporary table (which will implicitly remove the temporary -# table) while in XA_idle. Then checks that an explicit DROP TEMPORARY -# TABLE is rejected with ER_XAER_RMFAIL as required by the XA spec. -# -# Reset master to avoid clutter when dumping binlog -RESET MASTER; -# Create separate connection -# Create temporary table -CREATE TEMPORARY TABLE temp(i int); -INSERT INTO temp VALUES (0), (1), (2), (3); -# Start XA txn and leave it in XA_idle -XA START 'idle_at_disconnect'; -XA END 'idle_at_disconnect'; -# Terminate connection to verify that the temporary table can be -# removed at disconnect even in XA_idle, and that this does not -# cause problems for replication -# Dump binlog to show that, either the generated DROP comes after tx -# commit (stmt), or there is no trace of the XA txn and the temp -# table (row or mixed) -include/show_binlog_events.inc -Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 # Query # # use `test`; DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `temp` -# -# Start XA txn and leave in XA_idle -XA START 'idle_when_drop_temp'; -XA END 'idle_when_drop_temp'; -# Verify that explicit DROP TEMPORARY TABLE is rejected in XA_idle -DROP TEMPORARY TABLE IF EXISTS t; -Got one of the listed errors -XA ROLLBACK 'idle_when_drop_temp'; -RESET MASTER; diff --git a/mysql-test/suite/binlog/r/binlog_mix_drop_tmp_tbl.result b/mysql-test/suite/binlog/r/binlog_row_mix_drop_tmp_tbl.result similarity index 81% rename from mysql-test/suite/binlog/r/binlog_mix_drop_tmp_tbl.result rename to mysql-test/suite/binlog/r/binlog_row_mix_drop_tmp_tbl.result index a07a47b896c..5b2dc903bc0 100644 --- a/mysql-test/suite/binlog/r/binlog_mix_drop_tmp_tbl.result +++ b/mysql-test/suite/binlog/r/binlog_row_mix_drop_tmp_tbl.result @@ -34,26 +34,25 @@ master-bin.000001 # Query # # use `drop-temp+table-test`; DROP TEMPORARY TABLE I master-bin.000001 # Query # # use `drop-temp+table-test`; DROP TABLE IF EXISTS `t` /* generated by server */ master-bin.000001 # Query # # use `drop-temp+table-test`; DROP TEMPORARY TABLE IF EXISTS `tmp2` /* generated by server */ master-bin.000001 # Query # # use `drop-temp+table-test`; DROP TABLE IF EXISTS `tmp2`,`t` /* generated by server */ -master-bin.000001 # Query # # use `drop-temp+table-test`; DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `shortn2`,`table:name`,`shortn1` DROP DATABASE `drop-temp+table-test`; RESET MASTER; -CREATE TABLE t1 ( i text ); -CREATE TEMPORARY TABLE ttmp1 ( i text ); -SET @@session.binlog_format='ROW'; -INSERT INTO t1 VALUES ('1'); -SELECT @@session.binlog_format; -@@session.binlog_format -ROW +# +# Bug 83003: Using temporary tables on slaves increases GTID sequence number +# +SET @saved_binlog_format= @@SESSION.binlog_format; +SET SESSION binlog_format= STATEMENT; +CREATE TEMPORARY TABLE temp_needs_logging(a INT) ENGINE=InnoDB; +SET SESSION binlog_format= @saved_binlog_format; +CREATE TEMPORARY TABLE temp_trx(a INT) ENGINE=InnoDB; +CREATE TEMPORARY TABLE temp_non_trx(a INT) ENGINE=MyISAM; +CREATE TEMPORARY TABLE temp_needs_logging_in_stmt(a INT) ENGINE=InnoDB; +SET SESSION sql_log_bin= 0; +CREATE TEMPORARY TABLE temp_binlog_disabled(a INT) ENGINE=InnoDB; +SET SESSION sql_log_bin= 1; include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 # Query # # use `test`; CREATE TABLE t1 ( i text ) -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 # # use `test`; DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `ttmp1` -RESET MASTER; -DROP TABLE t1; +master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE temp_needs_logging(a INT) ENGINE=InnoDB +master-bin.000001 # Query # # use `test`; DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `temp_needs_logging` # # BUG#21638823: ASSERTION FAILED: # THD->GET_TRANSACTION()->IS_EMPTY(TRANSACTION_CTX::STMT) || THD @@ -80,7 +79,6 @@ XA END 'idle_at_disconnect'; # table (row or mixed) include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 # Query # # use `test`; DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `temp` # # Start XA txn and leave in XA_idle XA START 'idle_when_drop_temp'; diff --git a/mysql-test/suite/binlog/r/binlog_mix_tmp_table.result b/mysql-test/suite/binlog/r/binlog_row_mix_tmp_table.result similarity index 100% rename from mysql-test/suite/binlog/r/binlog_mix_tmp_table.result rename to mysql-test/suite/binlog/r/binlog_row_mix_tmp_table.result diff --git a/mysql-test/suite/binlog/r/binlog_row_tmp_table.result b/mysql-test/suite/binlog/r/binlog_row_tmp_table.result deleted file mode 100644 index 2d557b7cfa0..00000000000 --- a/mysql-test/suite/binlog/r/binlog_row_tmp_table.result +++ /dev/null @@ -1,44 +0,0 @@ -RESET MASTER; -create table foo (a int); -flush logs; -create temporary table tmp1_foo like foo; -create temporary table tmp2_foo (a int); -insert into tmp1_foo values (1), (2), (3), (4); -replace into tmp2_foo values (1), (2), (3), (4); -update tmp1_foo set a=2*a-1; -update tmp2_foo set a=2*a; -delete from tmp1_foo where a < 5; -delete from tmp2_foo where a < 5; -insert into foo select * from tmp1_foo; -insert into foo select * from tmp2_foo; -truncate table tmp1_foo; -truncate table tmp2_foo; -flush logs; -select * from foo; -a -5 -7 -6 -8 -drop table foo; -create table foo (a int); -RESET MASTER; -select * from foo; -a -5 -7 -6 -8 -drop table foo; -RESET MASTER; -create database b51226; -use b51226; -create temporary table t1(i int); -use b51226; -create temporary table t1(i int); -create temporary table t1(i int); -ERROR 42S01: Table 't1' already exists -insert into t1 values(1); -DROP DATABASE b51226; -FLUSH LOGS; -RESET MASTER; diff --git a/mysql-test/suite/binlog/r/binlog_stm_drop_tmp_tbl.result b/mysql-test/suite/binlog/r/binlog_stm_drop_tmp_tbl.result index cd9bea28d69..35fec247fbc 100644 --- a/mysql-test/suite/binlog/r/binlog_stm_drop_tmp_tbl.result +++ b/mysql-test/suite/binlog/r/binlog_stm_drop_tmp_tbl.result @@ -60,8 +60,29 @@ 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 # # use `test`; DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `ttmp1` -RESET MASTER; DROP TABLE t1; +RESET MASTER; +# +# Bug 83003: Using temporary tables on slaves increases GTID sequence number +# +SET @saved_binlog_format= @@SESSION.binlog_format; +SET SESSION binlog_format= STATEMENT; +CREATE TEMPORARY TABLE temp_needs_logging(a INT) ENGINE=InnoDB; +SET SESSION binlog_format= @saved_binlog_format; +CREATE TEMPORARY TABLE temp_trx(a INT) ENGINE=InnoDB; +CREATE TEMPORARY TABLE temp_non_trx(a INT) ENGINE=MyISAM; +CREATE TEMPORARY TABLE temp_needs_logging_in_stmt(a INT) ENGINE=InnoDB; +SET SESSION sql_log_bin= 0; +CREATE TEMPORARY TABLE temp_binlog_disabled(a INT) ENGINE=InnoDB; +SET SESSION sql_log_bin= 1; +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE temp_needs_logging(a INT) ENGINE=InnoDB +master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE temp_trx(a INT) ENGINE=InnoDB +master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE temp_non_trx(a INT) ENGINE=MyISAM +master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE temp_needs_logging_in_stmt(a INT) ENGINE=InnoDB +master-bin.000001 # Query # # use `test`; DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `temp_needs_logging_in_stmt`,`temp_trx`,`temp_needs_logging` +master-bin.000001 # Query # # use `test`; DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `temp_non_trx` # # BUG#21638823: ASSERTION FAILED: # THD->GET_TRANSACTION()->IS_EMPTY(TRANSACTION_CTX::STMT) || THD diff --git a/mysql-test/suite/binlog/t/binlog_drop_temp_table_on_disconnect_row_mix.test b/mysql-test/suite/binlog/t/binlog_drop_temp_table_on_disconnect_row_mix.test new file mode 100644 index 00000000000..36c6a54c382 --- /dev/null +++ b/mysql-test/suite/binlog/t/binlog_drop_temp_table_on_disconnect_row_mix.test @@ -0,0 +1,36 @@ +--source include/have_log_bin.inc +--source include/have_myisam.inc +--source include/have_binlog_format_mixed_or_row.inc + +RESET MASTER; + +CREATE TABLE t1(a INT) ENGINE=InnoDB; +CREATE TABLE t2(a INT) ENGINE=MyISAM; + +--source include/count_sessions.inc + +--connect(con1,localhost,root) + +SET @saved_binlog_format= @@SESSION.binlog_format; +# A DROP for tmp10 should be binlogged because CREATE TABLE is +SET SESSION binlog_format= 'STATEMENT'; +CREATE TEMPORARY TABLE tmp10 (a INT); +SET SESSION binlog_format= @saved_binlog_format; + +# No DROP should be logged for any of the statements below +CREATE TEMPORARY TABLE tmp1 ENGINE=InnoDB SELECT * FROM t1; +CREATE TEMPORARY TABLE tmp2 ENGINE=MyISAM SELECT * FROM t2; +CREATE TEMPORARY TABLE tmp3 ENGINE=MyISAM SELECT * FROM t1; +CREATE TEMPORARY TABLE tmp4 ENGINE=InnoDB SELECT * FROM t2; +CREATE TEMPORARY TABLE tmp5 (a INT) ENGINE=InnoDB; +CREATE TEMPORARY TABLE tmp6 (a INT) ENGINE=MyISAM; +CREATE TEMPORARY TABLE tmp7 LIKE t1; +CREATE TEMPORARY TABLE tmp8 LIKE t2; + +--disconnect con1 +--connection default +--source include/wait_until_count_sessions.inc + +--source include/show_binlog_events.inc + +DROP TABLE t1, t2; diff --git a/mysql-test/suite/binlog/t/binlog_drop_temp_table_on_disconnect_stmt.test b/mysql-test/suite/binlog/t/binlog_drop_temp_table_on_disconnect_stmt.test new file mode 100644 index 00000000000..8314e2510fd --- /dev/null +++ b/mysql-test/suite/binlog/t/binlog_drop_temp_table_on_disconnect_stmt.test @@ -0,0 +1,35 @@ +--source include/have_log_bin.inc +--source include/have_myisam.inc +--source include/have_binlog_format_statement.inc + +RESET MASTER; + +CREATE TABLE t1(a INT) ENGINE=InnoDB; +CREATE TABLE t2(a INT) ENGINE=MyISAM; + +--source include/count_sessions.inc + +--connect(con1,localhost,root) + +# A DROP should be logged for the following tables because CREATE TABLE +# is logged +CREATE TEMPORARY TABLE tmp1 ENGINE=InnoDB SELECT * FROM t1; +CREATE TEMPORARY TABLE tmp2 ENGINE=MyISAM SELECT * FROM t2; +CREATE TEMPORARY TABLE tmp3 ENGINE=MyISAM SELECT * FROM t1; +CREATE TEMPORARY TABLE tmp4 ENGINE=InnoDB SELECT * FROM t2; +CREATE TEMPORARY TABLE tmp5 (a INT) ENGINE=InnoDB; +CREATE TEMPORARY TABLE tmp6 (a INT) ENGINE=MyISAM; +CREATE TEMPORARY TABLE tmp7 LIKE t1; +CREATE TEMPORARY TABLE tmp8 LIKE t2; + +SET SESSION binlog_format= 'ROW'; +# No DROP should be logged for tmp10 +CREATE TEMPORARY TABLE tmp10 (a INT); + +--disconnect con1 +--connection default +--source include/wait_until_count_sessions.inc + +--source include/show_binlog_events.inc + +DROP TABLE t1, t2; diff --git a/mysql-test/suite/binlog/t/binlog_mix_tmp_table.test b/mysql-test/suite/binlog/t/binlog_mix_tmp_table.test deleted file mode 100644 index a91fba12246..00000000000 --- a/mysql-test/suite/binlog/t/binlog_mix_tmp_table.test +++ /dev/null @@ -1,2 +0,0 @@ -source include/have_binlog_format_mixed.inc; -source extra/binlog_tests/tmp_table.test; diff --git a/mysql-test/suite/binlog/t/binlog_row_drop_tmp_tbl.test b/mysql-test/suite/binlog/t/binlog_row_drop_tmp_tbl.test deleted file mode 100644 index 6a18ff70dbc..00000000000 --- a/mysql-test/suite/binlog/t/binlog_row_drop_tmp_tbl.test +++ /dev/null @@ -1,6 +0,0 @@ - -# This is a wrapper for binlog.test so that the same test case can be used -# For both statement and row based bin logs 9/19/2005 [jbm] - --- source include/have_binlog_format_row.inc --- source extra/binlog_tests/drop_temp_table.test diff --git a/mysql-test/suite/binlog/t/binlog_mix_drop_tmp_tbl.test b/mysql-test/suite/binlog/t/binlog_row_mix_drop_tmp_tbl.test similarity index 58% rename from mysql-test/suite/binlog/t/binlog_mix_drop_tmp_tbl.test rename to mysql-test/suite/binlog/t/binlog_row_mix_drop_tmp_tbl.test index 51c6af99913..8d3441fad47 100644 --- a/mysql-test/suite/binlog/t/binlog_mix_drop_tmp_tbl.test +++ b/mysql-test/suite/binlog/t/binlog_row_mix_drop_tmp_tbl.test @@ -1,4 +1,5 @@ # This is a wrapper for binlog.test so that the same test case can be used --- source include/have_binlog_format_mixed.inc +-- source include/have_myisam.inc +-- source include/have_binlog_format_mixed_or_row.inc -- source extra/binlog_tests/drop_temp_table.test diff --git a/mysql-test/suite/binlog/t/binlog_row_mix_tmp_table.test b/mysql-test/suite/binlog/t/binlog_row_mix_tmp_table.test new file mode 100644 index 00000000000..c2bb59bc6a8 --- /dev/null +++ b/mysql-test/suite/binlog/t/binlog_row_mix_tmp_table.test @@ -0,0 +1,2 @@ +source include/have_binlog_format_mixed_or_row.inc; +source extra/binlog_tests/tmp_table.test; diff --git a/mysql-test/suite/binlog/t/binlog_row_tmp_table.test b/mysql-test/suite/binlog/t/binlog_row_tmp_table.test deleted file mode 100644 index be726bbc748..00000000000 --- a/mysql-test/suite/binlog/t/binlog_row_tmp_table.test +++ /dev/null @@ -1,2 +0,0 @@ -source include/have_binlog_format_row.inc; -source extra/binlog_tests/tmp_table.test; diff --git a/mysql-test/suite/binlog/t/binlog_stm_drop_tmp_tbl.test b/mysql-test/suite/binlog/t/binlog_stm_drop_tmp_tbl.test index 7ab0f5916c0..3a8e59ec12d 100644 --- a/mysql-test/suite/binlog/t/binlog_stm_drop_tmp_tbl.test +++ b/mysql-test/suite/binlog/t/binlog_stm_drop_tmp_tbl.test @@ -1,5 +1,5 @@ - # This is a wrapper for binlog.test so that the same test case can be used +-- source include/have_myisam.inc -- source include/have_binlog_format_statement.inc -- source extra/binlog_tests/drop_temp_table.test diff --git a/mysql-test/suite/binlog_nogtid/r/binlog_row_mix_innodb_myisam.result b/mysql-test/suite/binlog_nogtid/r/binlog_row_mix_innodb_myisam.result index 2d22ed678b1..c8fe3d98ca8 100644 --- a/mysql-test/suite/binlog_nogtid/r/binlog_row_mix_innodb_myisam.result +++ b/mysql-test/suite/binlog_nogtid/r/binlog_row_mix_innodb_myisam.result @@ -261,7 +261,6 @@ set autocommit=0; create table t2 (n int) engine=innodb; insert into t2 values (3); include/save_binlog_position.inc -include/assert_binlog_events.inc [(Anonymous_)?Gtid # !Q(DROP.*TEMPORARY.*) # (Anonymous_)?Gtid # !Q(DROP.*TEMPORARY.*)] select get_lock("lock1",60); get_lock("lock1",60) 1 @@ -299,8 +298,6 @@ master-bin.000001 # Table_map # # table_id: # (test.t0) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F master-bin.000001 # Query # # COMMIT master-bin.000001 # Query # # use `test`; create table t2 (n int) engine=innodb -master-bin.000001 # Query # # use `test`; DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `ti` -master-bin.000001 # Query # # use `test`; DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `t1` do release_lock("lock1"); drop table t0,t2; set autocommit=0; diff --git a/mysql-test/suite/rpl/r/rpl_mix_rewrt_db.result b/mysql-test/suite/rpl/r/rpl_mix_rewrt_db.result index 12d1758d4db..52ef6644702 100644 --- a/mysql-test/suite/rpl/r/rpl_mix_rewrt_db.result +++ b/mysql-test/suite/rpl/r/rpl_mix_rewrt_db.result @@ -104,105 +104,5 @@ set sql_log_bin= 1; set sql_log_bin= 0; drop table t1; set sql_log_bin= 1; - -**** -**** Bug #46861 Auto-closing of temporary tables broken by replicate-rewrite-db -**** - -**** -**** Preparing the environment -**** -SET sql_log_bin= 0; -CREATE DATABASE database_master_temp_01; -CREATE DATABASE database_master_temp_02; -CREATE DATABASE database_master_temp_03; -SET sql_log_bin= 1; -SET sql_log_bin= 0; -CREATE DATABASE database_slave_temp_01; -CREATE DATABASE database_slave_temp_02; -CREATE DATABASE database_slave_temp_03; -SET sql_log_bin= 1; - -**** -**** Creating temporary tables on different databases with different connections -**** -**** con_temp_01 --> creates -**** t_01_01_temp on database_master_temp_01 -**** -**** con_temp_02 --> creates -**** t_01_01_temp on database_master_temp_01 -**** t_02_01_temp, t_02_02_temp on database_master_temp_02 -**** -**** con_temp_03 --> creates -**** t_01_01_temp on database_master_temp_01 -**** t_02_01_temp, t_02_02_temp on database_master_temp_02 -**** t_03_01_temp, t_03_02_temp, t_03_03_temp on database_master_temp_03 -**** - -con_temp_01 - -USE database_master_temp_01; -CREATE TEMPORARY TABLE t_01_01_temp(a int); -INSERT INTO t_01_01_temp VALUES(1); - -con_temp_02 - -USE database_master_temp_01; -CREATE TEMPORARY TABLE t_01_01_temp(a int); -INSERT INTO t_01_01_temp VALUES(1); -USE database_master_temp_02; -CREATE TEMPORARY TABLE t_02_01_temp(a int); -INSERT INTO t_02_01_temp VALUES(1); -CREATE TEMPORARY TABLE t_02_02_temp(a int); -INSERT INTO t_02_02_temp VALUES(1); - -con_temp_03 - -USE database_master_temp_01; -CREATE TEMPORARY TABLE t_01_01_temp(a int); -INSERT INTO t_01_01_temp VALUES(1); -USE database_master_temp_02; -CREATE TEMPORARY TABLE t_02_01_temp(a int); -INSERT INTO t_02_01_temp VALUES(1); -CREATE TEMPORARY TABLE t_02_02_temp(a int); -INSERT INTO t_02_02_temp VALUES(1); -USE database_master_temp_03; -CREATE TEMPORARY TABLE t_03_01_temp(a int); -INSERT INTO t_03_01_temp VALUES(1); -CREATE TEMPORARY TABLE t_03_02_temp(a int); -INSERT INTO t_03_02_temp VALUES(1); -CREATE TEMPORARY TABLE t_03_03_temp(a int); -INSERT INTO t_03_03_temp VALUES(1); - -**** Dropping the connections -**** To be sure that the DROP statements have been logged, we make -**** sure that the number of open temporary tables at slave becomes -**** zero. If not,the test will time out. - -include/sync_slave_sql_with_master.inc -show status like 'Slave_open_temp_tables'; -Variable_name Value -Slave_open_temp_tables 0 -flush logs; - -**** Wait until number of open temporary tables at slave becomes zero - - -**** Check if every drop temporary table command is prepended with "use" - -include/assert.inc [The number of drop temporary table events in binlog should be 6] -**** -**** Cleaning up the test case -**** -SET sql_log_bin= 0; -DROP DATABASE database_master_temp_01; -DROP DATABASE database_master_temp_02; -DROP DATABASE database_master_temp_03; -SET sql_log_bin= 1; -SET sql_log_bin= 0; -DROP DATABASE database_slave_temp_01; -DROP DATABASE database_slave_temp_02; -DROP DATABASE database_slave_temp_03; -SET sql_log_bin= 1; include/sync_slave_sql_with_master.inc include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_row_mix_gtid_disconnect_drop_temporary_table.result b/mysql-test/suite/rpl/r/rpl_row_mix_gtid_disconnect_drop_temporary_table.result new file mode 100644 index 00000000000..9c2e506831a --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_row_mix_gtid_disconnect_drop_temporary_table.result @@ -0,0 +1,62 @@ +include/master-slave.inc +Warnings: +Note #### Sending passwords in plain text without SSL/TLS is extremely insecure. +Note #### Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. +[connection master] +CREATE TABLE t (a INT); +---- 1. Disconnect after SET GTID_NEXT ---- +include/rpl_connect.inc [creating con1] +[connection con1] +CREATE TEMPORARY TABLE tt (a INT); +SET GTID_NEXT = '#'; +include/save_binlog_position.inc +include/gtid_step_reset.inc +# Disconnecting. +include/sync_slave_sql_with_master.inc +include/assert.inc [Slave should not have any open temporary tables.] +include/gtid_step_assert.inc [count=0, only_count=0] +---- 2. Disconnect in the middle of transaction ---- +include/rpl_connect.inc [creating con1] +[connection con1] +CREATE TEMPORARY TABLE tt (a INT); +SET GTID_NEXT = '#'; +BEGIN; +INSERT INTO t VALUES (1); +include/save_binlog_position.inc +include/gtid_step_reset.inc +# Disconnecting. +include/sync_slave_sql_with_master.inc +include/assert.inc [Slave should not have any open temporary tables.] +include/gtid_step_assert.inc [count=0, only_count=0] +---- 3. Disconnect after COMMIT ---- +include/rpl_connect.inc [creating con1] +[connection con1] +CREATE TEMPORARY TABLE tt (a INT); +SET GTID_NEXT = '#'; +BEGIN; +INSERT INTO t VALUES (1); +COMMIT; +include/save_binlog_position.inc +include/gtid_step_reset.inc +# Disconnecting. +include/sync_slave_sql_with_master.inc +include/assert.inc [Slave should not have any open temporary tables.] +include/gtid_step_assert.inc [count=0, only_count=0] +---- 4. Disconnect after ROLLBACK ---- +include/rpl_connect.inc [creating con1] +[connection con1] +CREATE TEMPORARY TABLE tt (a INT); +SET GTID_NEXT = '#'; +BEGIN; +INSERT INTO t VALUES (1); +ROLLBACK; +include/save_binlog_position.inc +include/gtid_step_reset.inc +# Disconnecting. +include/sync_slave_sql_with_master.inc +include/assert.inc [Slave should not have any open temporary tables.] +include/gtid_step_assert.inc [count=0, only_count=0] +---- Clean up ---- +DROP TABLE t; +include/sync_slave_sql_with_master.inc +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_gtid_disconnect_drop_temporary_table.result b/mysql-test/suite/rpl/r/rpl_stm_gtid_disconnect_drop_temporary_table.result similarity index 100% rename from mysql-test/suite/rpl/r/rpl_gtid_disconnect_drop_temporary_table.result rename to mysql-test/suite/rpl/r/rpl_stm_gtid_disconnect_drop_temporary_table.result diff --git a/mysql-test/suite/rpl/t/rpl_000013.test b/mysql-test/suite/rpl/t/rpl_000013.test index 9f1b06665dd..533bb08857b 100644 --- a/mysql-test/suite/rpl/t/rpl_000013.test +++ b/mysql-test/suite/rpl/t/rpl_000013.test @@ -2,12 +2,12 @@ # is automatically binlogged and sent to slave # when a temp table is dropped by disconnection # of a master's conection. -# So it does not apply to row-based, where we neither need +# So it does not apply to row- and mix-based, where we neither need # nor do this automatic binlogging. And if we run this test -# in row-based, it hangs waiting for an offset which is never +# in row- or mix-based, it hangs waiting for an offset which is never # reached (the "sync_with_master 1"), logically. ---source include/have_binlog_format_mixed_or_statement.inc +--source include/have_binlog_format_statement.inc source include/master-slave.inc; --disable_query_log diff --git a/mysql-test/suite/rpl/t/rpl_row_mix_gtid_disconnect_drop_temporary_table.test b/mysql-test/suite/rpl/t/rpl_row_mix_gtid_disconnect_drop_temporary_table.test new file mode 100644 index 00000000000..9f8a8618235 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_row_mix_gtid_disconnect_drop_temporary_table.test @@ -0,0 +1,2 @@ +--source include/have_binlog_format_mixed_or_row.inc +--source extra/rpl_tests/gtid_disconnect_drop_temporary_table.test diff --git a/mysql-test/suite/rpl/t/rpl_stm_gtid_disconnect_drop_temporary_table.test b/mysql-test/suite/rpl/t/rpl_stm_gtid_disconnect_drop_temporary_table.test new file mode 100644 index 00000000000..8303d5a98c8 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_stm_gtid_disconnect_drop_temporary_table.test @@ -0,0 +1,2 @@ +--source include/have_binlog_format_statement.inc +--source extra/rpl_tests/gtid_disconnect_drop_temporary_table.test diff --git a/mysql-test/suite/rpl/t/rpl_temp_table.test b/mysql-test/suite/rpl/t/rpl_temp_table.test index f4ac1a12293..79e1e6f6a71 100644 --- a/mysql-test/suite/rpl/t/rpl_temp_table.test +++ b/mysql-test/suite/rpl/t/rpl_temp_table.test @@ -2,7 +2,7 @@ # meaningful only in statement-based: -- source include/not_group_replication_plugin.inc --- source include/have_binlog_format_mixed_or_statement.inc +-- source include/have_binlog_format_statement.inc -- source include/master-slave.inc --disable_query_log diff --git a/mysql-test/suite/rpl_nogtid/r/rpl_mix_drop_temp.result b/mysql-test/suite/rpl_nogtid/r/rpl_mix_drop_temp.result index 4d403379b67..e9d3a083a97 100644 --- a/mysql-test/suite/rpl_nogtid/r/rpl_mix_drop_temp.result +++ b/mysql-test/suite/rpl_nogtid/r/rpl_mix_drop_temp.result @@ -39,6 +39,5 @@ Slave_open_temp_tables 0 include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # use `test`; CREATE TABLE t1 ( i INT ) -master-bin.000001 # Query # # use `test`; DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `ttmp1` DROP TABLE t1; include/rpl_end.inc diff --git a/mysql-test/suite/rpl_nogtid/r/rpl_temp_table_mix_row.result b/mysql-test/suite/rpl_nogtid/r/rpl_temp_table_mix_row.result index 52f30044023..ed7d3714047 100644 --- a/mysql-test/suite/rpl_nogtid/r/rpl_temp_table_mix_row.result +++ b/mysql-test/suite/rpl_nogtid/r/rpl_temp_table_mix_row.result @@ -15,7 +15,6 @@ SHOW STATUS LIKE "Slave_open_temp_tables"; Variable_name Value Slave_open_temp_tables 0 [on master] -[on master1] [on slave] include/sync_slave_sql_with_master.inc ==== Verify results on slave ==== diff --git a/mysql-test/suite/rpl_nogtid/t/rpl_temp_table_mix_row.test b/mysql-test/suite/rpl_nogtid/t/rpl_temp_table_mix_row.test index c632c167a3c..8ebd71c1d92 100644 --- a/mysql-test/suite/rpl_nogtid/t/rpl_temp_table_mix_row.test +++ b/mysql-test/suite/rpl_nogtid/t/rpl_temp_table_mix_row.test @@ -32,13 +32,8 @@ SHOW STATUS LIKE "Slave_open_temp_tables"; --connection master disconnect master; ---echo [on master1] ---connection master1 - -# waiting DROP TEMPORARY TABLE event to be written into binlog -let $wait_binlog_event= DROP; -source include/wait_for_binlog_event.inc; +--connection master1 --echo [on slave] --source include/sync_slave_sql_with_master.inc diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 9bd3f75252f..4db1c236aaf 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -1917,7 +1917,7 @@ bool close_temporary_tables(THD *thd) /* scan sorted tmps to generate sequence of DROP */ for (table= thd->temporary_tables; table; table= next) { - if (is_user_table(table)) + if (is_user_table(table) && table->should_binlog_drop_if_temp()) { bool save_thread_specific_used= thd->thread_specific_used; my_thread_id save_pseudo_thread_id= thd->variables.pseudo_thread_id; @@ -1938,7 +1938,11 @@ bool close_temporary_tables(THD *thd) table= next) { /* Separate transactional from non-transactional temp tables */ - if (table->s->tmp_table == TRANSACTIONAL_TMP_TABLE) + if (!table->should_binlog_drop_if_temp()) + { + /* Nothing, do not binlog this one */ + } + else if (table->s->tmp_table == TRANSACTIONAL_TMP_TABLE) { found_trans_table= true; /* @@ -7105,6 +7109,8 @@ TABLE *open_table_uncached(THD *thd, const char *path, const char *db, if (add_to_temporary_tables_list) { + tmp_table->set_binlog_drop_if_temp(!thd->is_current_stmt_binlog_disabled() + && !thd->is_current_stmt_binlog_format_row()); /* growing temp list at the head */ tmp_table->next= thd->temporary_tables; if (tmp_table->next) diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc index be106bf666d..614fc4c20b8 100644 --- a/sql/sql_insert.cc +++ b/sql/sql_insert.cc @@ -2827,6 +2827,10 @@ int Query_result_create::binlog_show_create_table() if (error) DBUG_RETURN(error); + create_table->table->set_binlog_drop_if_temp( + !thd->is_current_stmt_binlog_disabled() + && !thd->is_current_stmt_binlog_format_row()); + if (!thd->is_current_stmt_binlog_format_row() || table->s->tmp_table) DBUG_RETURN(0); diff --git a/sql/table.h b/sql/table.h index 3adedc9e53c..6e2f3389a7f 100644 --- a/sql/table.h +++ b/sql/table.h @@ -1989,6 +1989,20 @@ public: to false for all such fields in this table. */ void blobs_need_not_keep_old_value(); + +private: + bool should_binlog_drop_if_temp_flag; + +public: + void set_binlog_drop_if_temp(bool should_binlog) + { + should_binlog_drop_if_temp_flag= should_binlog; + } + + bool should_binlog_drop_if_temp(void) const + { + return should_binlog_drop_if_temp_flag; + } };