commit 7e68f3be971a6e47a8aa1fa110a40ae4222574ae 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 create 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 using it to decide whether a DROP should be logged or not in close_temporary_tables. At the same time fix some issues with THD:LOCK_temporary_tables critical sessions in the latter function. diff --git a/mysql-test/extra/binlog_tests/drop_temp_table.test b/mysql-test/extra/binlog_tests/drop_temp_table.test index 3736333..a921bde 100644 --- a/mysql-test/extra/binlog_tests/drop_temp_table.test +++ b/mysql-test/extra/binlog_tests/drop_temp_table.test @@ -17,10 +17,9 @@ CREATE TEMPORARY TABLE shortn2 (a INT); # BUG#46572 DROP TEMPORARY table IF EXISTS does not have a consistent behavior # in ROW mode # -# In RBR, 'DROP TEMPORARY TABLE ...' statement should never be binlogged no -# matter if the tables exist or not. In contrast, both in SBR and MBR, the -# statement should be always binlogged no matter if the tables exist or not. -# In MBR though, it will always be binlogged with IF EXISTS. +# In RBR/MIXED, 'DROP TEMPORARY TABLE ...' statement should never be binlogged +# no matter if the tables exist or not. In contrast, in SBR, the statement +# should be always binlogged no matter if the tables exist or not. ############################################################################## CREATE TEMPORARY TABLE tmp(c1 int); CREATE TEMPORARY TABLE tmp1(c1 int); @@ -35,15 +34,15 @@ DROP TEMPORARY TABLE IF EXISTS tmp; # binlogged when the table did not exist in RBR. DROP TEMPORARY TABLE IF EXISTS tmp; -# In RBR, 'DROP TEMPORARY TABLE ...' statement is never binlogged no matter if +# In RBR/MIXED, 'DROP TEMPORARY TABLE ...' statement is never binlogged no matter if # the tables exist or not. DROP TEMPORARY TABLE IF EXISTS tmp, tmp1; DROP TEMPORARY TABLE tmp3; -#In RBR, tmp2 will NOT be binlogged, because it is a temporary table. +#In RBR/MIXED, tmp2 will NOT be binlogged, because it is a temporary table. DROP TABLE IF EXISTS tmp2, t; -#In RBR, tmp2 will be binlogged, because it does not exist and master do not know +#In RBR/MIXED, tmp2 will be binlogged, because it does not exist and master do not know # whether it is a temporary table or not. DROP TABLE IF EXISTS tmp2, t; --enable_warnings @@ -68,8 +67,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`; @@ -86,27 +88,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: @@ -134,8 +169,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 8aac947..7e8e55f 100644 --- a/mysql-test/extra/binlog_tests/mix_innodb_myisam_binlog.test +++ b/mysql-test/extra/binlog_tests/mix_innodb_myisam_binlog.test @@ -241,13 +241,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 f65828c..2d5852f 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 88% 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 5173f69..6aed19f 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 @@ -107,6 +110,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 12d544d..36476c8 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'; @@ -85,8 +88,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 a7bee39..20a8965 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_mix_drop_tmp_tbl.result b/mysql-test/suite/binlog/r/binlog_mix_drop_tmp_tbl.result deleted file mode 100644 index d5a9f62..0000000 --- a/mysql-test/suite/binlog/r/binlog_mix_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 TEMPORARY TABLE IF EXISTS `tmp2` /* 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 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_row_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_row_drop_tmp_tbl.result rename to mysql-test/suite/binlog/r/binlog_row_mix_drop_tmp_tbl.result index d5a9f62..a3149fc 100644 --- a/mysql-test/suite/binlog/r/binlog_row_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 TEMPORARY TABLE IF EXISTS `tmp2` /* 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 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 2d557b7..0000000 --- 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 84251f0..29901f8 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_mix_tmp_table.test b/mysql-test/suite/binlog/t/binlog_mix_tmp_table.test deleted file mode 100644 index a91fba1..0000000 --- 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 6a18ff7..0000000 --- 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 50% 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 51c6af9..5600dd6 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,6 @@ # 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_innodb.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 0000000..c2bb59b --- /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 be726bb..0000000 --- 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 7ab0f59..c3ae445 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,7 @@ # This is a wrapper for binlog.test so that the same test case can be used +-- source include/have_innodb.inc +-- 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 91247a8..829b5ec 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 @@ -298,8 +297,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 81e2939..95805f8 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 0000000..0dca648 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_row_mix_gtid_disconnect_drop_temporary_table.result @@ -0,0 +1,70 @@ +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 +set @@global.show_compatibility_56=ON; +include/assert.inc [Slave should not have any open temporary tables.] +set @@global.show_compatibility_56=OFF; +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 +set @@global.show_compatibility_56=ON; +include/assert.inc [Slave should not have any open temporary tables.] +set @@global.show_compatibility_56=OFF; +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 +set @@global.show_compatibility_56=ON; +include/assert.inc [Slave should not have any open temporary tables.] +set @@global.show_compatibility_56=OFF; +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 +set @@global.show_compatibility_56=ON; +include/assert.inc [Slave should not have any open temporary tables.] +set @@global.show_compatibility_56=OFF; +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 9f1b066..533bb08 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 0000000..9f8a861 --- /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 0000000..8303d5a --- /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 f4ac1a1..79e1e6f 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 83768cc..d1201c1 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 @@ -38,6 +38,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 4a321af..55fdf3c 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 4d7a713..725fe31 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 @@ -34,13 +34,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 fa851e1..b39b8a5 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -1846,7 +1846,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; @@ -1867,7 +1867,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; /* @@ -7107,6 +7111,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/table.h b/sql/table.h index 9441e12..472a3ad 100644 --- a/sql/table.h +++ b/sql/table.h @@ -1518,6 +1518,20 @@ public: the next statement. */ void cleanup_gc_items(); + +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; + } };