revision-id: 2c6d192a6cfd7a00da3f0439c2f42c4f7b694119 (mariadb-10.1.39-106-g2c6d192a6cf) parent(s): a5e268a2931c39c7b3cc2ff2e6e3fe6b5e85a383 author: Sujatha committer: Sujatha timestamp: 2019-07-23 15:46:51 +0530 message: MDEV-20091 DROP TEMPORARY table is logged despite no CREATE was logged MDEV-5589 commit set up a policy to skip DROP TEMPORARY TABLE binary logging in case the target table has not been "CREATEed" in binlog (no CREATE Query-log-event was logged into the binary log). It turns out that 1. the rule did not cover non-existing table DROPped with IF-EXISTS clause. The logged-create knowledge for the non-existing one does not even need MDEV-5589 patch, and 2. connection close disobeys it to trigger automatic DROP-IF-EXISTS binlogging. Either 1 or 2 or even both is/are also responsible for unexpected binlog records observed in MDEV-17863, actually rendering a referred @@global.read_only irrelevant as far as the described stored procedure definition *and* the ROW binlog-format are concerned. --- mysql-test/extra/binlog_tests/drop_temp_table.test | 24 ++++--- .../rpl_tests/rpl_drop_create_temp_table.test | 4 ++ .../include/binlog_parallel_replication_marks.test | 9 ++- .../r/binlog_parallel_replication_marks_row.result | 2 - .../suite/binlog/r/binlog_row_drop_tmp_tbl.result | 8 --- .../binlog/r/binlog_row_mix_innodb_myisam.result | 2 - .../suite/binlog/r/binlog_stm_drop_tmp_tbl.result | 4 -- .../suite/rpl/r/create_or_replace_mix.result | 11 +++ .../suite/rpl/r/create_or_replace_row.result | 7 ++ .../suite/rpl/r/create_or_replace_statement.result | 11 +++ .../rpl/r/rpl_row_drop_create_temp_table.result | 27 ------- .../suite/rpl/r/rpl_row_drop_temp_table.result | 36 ++++++++++ mysql-test/suite/rpl/t/create_or_replace.inc | 13 ++++ .../suite/rpl/t/rpl_row_drop_temp_table.test | 54 ++++++++++++++ sql/sql_base.cc | 84 ++++++++++++---------- sql/sql_table.cc | 2 +- 16 files changed, 204 insertions(+), 94 deletions(-) diff --git a/mysql-test/extra/binlog_tests/drop_temp_table.test b/mysql-test/extra/binlog_tests/drop_temp_table.test index c852ee4c8a0..4241974d813 100644 --- a/mysql-test/extra/binlog_tests/drop_temp_table.test +++ b/mysql-test/extra/binlog_tests/drop_temp_table.test @@ -14,12 +14,8 @@ CREATE TEMPORARY TABLE `table:name` (a INT); 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. +# MDEV-20091: DROP TEMPORARY TABLE IF EXISTS statements will be written +# to binlog only if the corresponding temporary table exists. ############################################################################## CREATE TEMPORARY TABLE tmp(c1 int); CREATE TEMPORARY TABLE tmp1(c1 int); @@ -30,12 +26,12 @@ CREATE TABLE t(c1 int); DROP TEMPORARY TABLE IF EXISTS tmp; --disable_warnings -# Before fixing BUG#46572, 'DROP TEMPORARY TABLE IF EXISTS...' statement was -# binlogged when the table did not exist in RBR. +# Post MDEV-20091: Following DROP TEMPORARY TABLE statement should not be +# logged as the table is already dropped above. DROP TEMPORARY TABLE IF EXISTS tmp; -# In RBR, 'DROP TEMPORARY TABLE ...' statement is never binlogged no matter if -# the tables exist or not. +# Post MDEV-20091: Only DROP TEMPORARY TABLE statement should be written only +# for 'tmp1' table. DROP TEMPORARY TABLE IF EXISTS tmp, tmp1; DROP TEMPORARY TABLE tmp3; @@ -79,6 +75,12 @@ DROP DATABASE `drop-temp+table-test`; # if there are open temporary tables. As such the implicit drop # for temporary tables on session closing must be logged. # +# MDEV-20091: DROP TEMPORARY TABLE IF EXISTS statements will be written to +# binlog only if the corresponding temporary table exists. In row based +# replication temporary tables are not replicated hence their corresponding +# DROP TEMPORARY TABLE statement will be not be written to binary log upon +# session closure. +# RESET MASTER; @@ -92,8 +94,10 @@ SELECT @@session.binlog_format; --disconnect con1 -- connection default +if (!`SELECT @@BINLOG_FORMAT = 'ROW'`) { --let $wait_binlog_event= DROP --source include/wait_for_binlog_event.inc +} -- source include/show_binlog_events.inc RESET MASTER; diff --git a/mysql-test/extra/rpl_tests/rpl_drop_create_temp_table.test b/mysql-test/extra/rpl_tests/rpl_drop_create_temp_table.test index ffd7fe1a5c4..f8b521e3abf 100644 --- a/mysql-test/extra/rpl_tests/rpl_drop_create_temp_table.test +++ b/mysql-test/extra/rpl_tests/rpl_drop_create_temp_table.test @@ -32,6 +32,10 @@ # is any # Drop-Temp-TT-Temp - Drops two temporary T-tables if there is any # Drop-Temp-NN-Temp - Drops two temporary N-tables if there is any +# +# Note: MDEV-20091: DROP TEMPORARY TABLE IF EXISTS statements will be written +# to binlog only if the corresponding temporary table exists. +# # Drop-Temp-Xe-Temp - Tries to drop a temporary table that does not exist # Drop-Temp-NXe-Temp - Drops a temporary N-table if there is any and # a temporary table that does not exist diff --git a/mysql-test/include/binlog_parallel_replication_marks.test b/mysql-test/include/binlog_parallel_replication_marks.test index ff35875aece..4e673bd30c3 100644 --- a/mysql-test/include/binlog_parallel_replication_marks.test +++ b/mysql-test/include/binlog_parallel_replication_marks.test @@ -49,9 +49,16 @@ connection default; # We need to wait for the implicit DROP TEMPORARY TABLE to be logged after # tmp_con disconnect, otherwise we get sporadic test failures. +# MDEV-20091: DROP TEMPORARY TABLE IF EXISTS statements will be written to +# binlog only if the corresponding temporary table exists. In row based +# replication temporary tables are not replicated hence their corresponding +# DROP TEMPORARY TABLE statement will be not be written to binary log upon +# session closure. + +if (!`SELECT @@BINLOG_FORMAT = 'ROW'`) { --let $wait_condition= SELECT variable_value > $before_drop_pos FROM information_schema.global_status WHERE variable_name = 'binlog_snapshot_position' --source include/wait_condition.inc - +} --let $binlog_pos2=query_get_value(SHOW MASTER STATUS, Position, 1) --let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1) diff --git a/mysql-test/suite/binlog/r/binlog_parallel_replication_marks_row.result b/mysql-test/suite/binlog/r/binlog_parallel_replication_marks_row.result index e9a744a748e..1dafeee47e2 100644 --- a/mysql-test/suite/binlog/r/binlog_parallel_replication_marks_row.result +++ b/mysql-test/suite/binlog/r/binlog_parallel_replication_marks_row.result @@ -89,6 +89,4 @@ BEGIN # server id 1 end_log_pos # Table_map: `test`.`t1` mapped to number # # server id 1 end_log_pos # Write_rows: table id # flags: STMT_END_F COMMIT/*!*/; -# server id 1 end_log_pos # GTID #-#-# ddl -DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `t5` DROP TABLE t1; 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 index dce2cc1408c..01ee24b9d48 100644 --- a/mysql-test/suite/binlog/r/binlog_row_drop_tmp_tbl.result +++ b/mysql-test/suite/binlog/r/binlog_row_drop_tmp_tbl.result @@ -30,15 +30,9 @@ master-bin.000001 # Query # # CREATE DATABASE `drop-temp+table-test` master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `drop-temp+table-test`; CREATE TABLE t(c1 int) master-bin.000001 # Gtid # # GTID #-#-# -master-bin.000001 # Query # # DROP TEMPORARY TABLE IF EXISTS `drop-temp+table-test`.`tmp` /* generated by server */ -master-bin.000001 # Gtid # # GTID #-#-# -master-bin.000001 # Query # # DROP TEMPORARY TABLE IF EXISTS `drop-temp+table-test`.`tmp` /* generated by server */ -master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `drop-temp+table-test`; DROP TABLE IF EXISTS `t` /* generated by server */ master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `drop-temp+table-test`; DROP TABLE IF EXISTS `tmp2`,`t` /* generated by server */ -master-bin.000001 # Gtid # # GTID #-#-# -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 ); @@ -56,7 +50,5 @@ master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F master-bin.000001 # Query # # COMMIT -master-bin.000001 # Gtid # # GTID #-#-# -master-bin.000001 # Query # # use `test`; DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `ttmp1` RESET MASTER; DROP TABLE t1; diff --git a/mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result b/mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result index f8f4fa5ec31..37018da86a0 100644 --- a/mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result +++ b/mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result @@ -303,8 +303,6 @@ master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F master-bin.000001 # Query # # COMMIT master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `test`; create table t2 (n int) engine=innodb -master-bin.000001 # Gtid # # GTID #-#-# -master-bin.000001 # Query # # use `test`; DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `t1`,`ti` do release_lock("lock1"); drop table t0,t2; set autocommit=0; 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 127df219b02..4eeb8dd80c7 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 @@ -46,12 +46,8 @@ master-bin.000001 # Query # # use `drop-temp+table-test`; CREATE TABLE t(c1 int) master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # DROP TEMPORARY TABLE IF EXISTS `drop-temp+table-test`.`tmp` /* generated by server */ master-bin.000001 # Gtid # # GTID #-#-# -master-bin.000001 # Query # # DROP TEMPORARY TABLE IF EXISTS `drop-temp+table-test`.`tmp` /* generated by server */ -master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # DROP TEMPORARY TABLE IF EXISTS `drop-temp+table-test`.`tmp1` /* generated by server */ master-bin.000001 # Gtid # # GTID #-#-# -master-bin.000001 # Query # # DROP TEMPORARY TABLE IF EXISTS `drop-temp+table-test`.`tmp` /* generated by server */ -master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `drop-temp+table-test`; DROP TEMPORARY TABLE `tmp3` /* generated by server */ master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # DROP TEMPORARY TABLE IF EXISTS `drop-temp+table-test`.`tmp2` /* generated by server */ diff --git a/mysql-test/suite/rpl/r/create_or_replace_mix.result b/mysql-test/suite/rpl/r/create_or_replace_mix.result index c6cce358799..2c85bb3d93b 100644 --- a/mysql-test/suite/rpl/r/create_or_replace_mix.result +++ b/mysql-test/suite/rpl/r/create_or_replace_mix.result @@ -238,6 +238,13 @@ set binlog_format="STATEMENT"; ERROR HY000: Cannot switch out of the row-based binary log format when the session has open temporary tables drop temporary table t8; set @@binlog_format=@org_binlog_format; +set @@session.binlog_format=default; +drop temporary table if exists t9; +Warnings: +Note 1051 Unknown table 'test.t9' +set session binlog_format=default; +create temporary table t9 (i int); +*** Must be no DROP logged for t9 when there was no CREATE, at disconnect too *** include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Gtid # # GTID #-#-# @@ -248,5 +255,9 @@ master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `test`; create temporary table t7 (a int) master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # DROP TEMPORARY TABLE IF EXISTS `test`.`t7` /* generated by server */ +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; create temporary table t9 (i int) +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `t9` drop table t2; include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/create_or_replace_row.result b/mysql-test/suite/rpl/r/create_or_replace_row.result index 8f032107db9..cbb47cc4aba 100644 --- a/mysql-test/suite/rpl/r/create_or_replace_row.result +++ b/mysql-test/suite/rpl/r/create_or_replace_row.result @@ -260,6 +260,13 @@ set binlog_format="STATEMENT"; ERROR HY000: Cannot switch out of the row-based binary log format when the session has open temporary tables drop temporary table t8; set @@binlog_format=@org_binlog_format; +set @@session.binlog_format=default; +drop temporary table if exists t9; +Warnings: +Note 1051 Unknown table 'test.t9' +set session binlog_format=default; +create temporary table t9 (i int); +*** Must be no DROP logged for t9 when there was no CREATE, at disconnect too *** include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Gtid # # GTID #-#-# diff --git a/mysql-test/suite/rpl/r/create_or_replace_statement.result b/mysql-test/suite/rpl/r/create_or_replace_statement.result index 7bdbc64e9f2..6b47fafbe23 100644 --- a/mysql-test/suite/rpl/r/create_or_replace_statement.result +++ b/mysql-test/suite/rpl/r/create_or_replace_statement.result @@ -230,6 +230,13 @@ set binlog_format="STATEMENT"; ERROR HY000: Cannot switch out of the row-based binary log format when the session has open temporary tables drop temporary table t8; set @@binlog_format=@org_binlog_format; +set @@session.binlog_format=default; +drop temporary table if exists t9; +Warnings: +Note 1051 Unknown table 'test.t9' +set session binlog_format=default; +create temporary table t9 (i int); +*** Must be no DROP logged for t9 when there was no CREATE, at disconnect too *** include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Gtid # # GTID #-#-# @@ -240,5 +247,9 @@ master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `test`; create temporary table t7 (a int) master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # DROP TEMPORARY TABLE IF EXISTS `test`.`t7` /* generated by server */ +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; create temporary table t9 (i int) +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `t9` drop table t2; include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_row_drop_create_temp_table.result b/mysql-test/suite/rpl/r/rpl_row_drop_create_temp_table.result index 05b2c04e7ba..ec6d89551ae 100644 --- a/mysql-test/suite/rpl/r/rpl_row_drop_create_temp_table.result +++ b/mysql-test/suite/rpl/r/rpl_row_drop_create_temp_table.result @@ -83,9 +83,6 @@ Warnings: Note 1051 Unknown table 'test.tt_xx_1' -b-b-b-b-b-b-b-b-b-b-b- >> Drop-Temp-If-Xe-Temp << -b-b-b-b-b-b-b-b-b-b-b- include/show_binlog_events.inc -Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 # Gtid # # GTID #-#-# -master-bin.000001 # Query # # DROP TEMPORARY TABLE IF EXISTS `test`.`tt_xx_1` /* generated by server */ -e-e-e-e-e-e-e-e-e-e-e- >> Drop-Temp-If-Xe-Temp << -e-e-e-e-e-e-e-e-e-e-e- SET @commands= 'Drop-Temp-TXe-Temp'; @@ -101,9 +98,6 @@ Warnings: Note 1051 Unknown table 'test.tt_1' -b-b-b-b-b-b-b-b-b-b-b- >> Drop-Temp-If-TXe-Temp << -b-b-b-b-b-b-b-b-b-b-b- include/show_binlog_events.inc -Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 # Gtid # # GTID #-#-# -master-bin.000001 # Query # # DROP TEMPORARY TABLE IF EXISTS `test`.`tt_1` /* generated by server */ -e-e-e-e-e-e-e-e-e-e-e- >> Drop-Temp-If-TXe-Temp << -e-e-e-e-e-e-e-e-e-e-e- SET @commands= 'Drop-Temp-NXe-Temp'; @@ -119,9 +113,6 @@ Warnings: Note 1051 Unknown table 'test.tt_1' -b-b-b-b-b-b-b-b-b-b-b- >> Drop-Temp-If-NXe-Temp << -b-b-b-b-b-b-b-b-b-b-b- include/show_binlog_events.inc -Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 # Gtid # # GTID #-#-# -master-bin.000001 # Query # # DROP TEMPORARY TABLE IF EXISTS `test`.`tt_1` /* generated by server */ -e-e-e-e-e-e-e-e-e-e-e- >> Drop-Temp-If-NXe-Temp << -e-e-e-e-e-e-e-e-e-e-e- SET @commands= 'Drop-Temp-TN-Temp'; @@ -264,7 +255,6 @@ Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Table_map # # table_id: # (test.tt_xx_1) master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F -master-bin.000001 # Query # # DROP TEMPORARY TABLE IF EXISTS `test`.`tt_xx_1` /* generated by server */ master-bin.000001 # Xid # # COMMIT /* XID */ -e-e-e-e-e-e-e-e-e-e-e- >> B T Drop-Temp-If-Xe-Temp C << -e-e-e-e-e-e-e-e-e-e-e- @@ -289,8 +279,6 @@ master-bin.000001 # Query # # COMMIT master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Table_map # # table_id: # (test.tt_xx_1) master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F -master-bin.000001 # Query # # DROP TEMPORARY TABLE IF EXISTS `test`.`tt_xx_1` /* generated by server */ -master-bin.000001 # Query # # DROP TEMPORARY TABLE IF EXISTS `test`.`tt_xx_1` /* generated by server */ master-bin.000001 # Xid # # COMMIT /* XID */ -e-e-e-e-e-e-e-e-e-e-e- >> B T Drop-Temp-If-Xe-Temp N Drop-Temp-If-Xe-Temp C << -e-e-e-e-e-e-e-e-e-e-e- @@ -344,7 +332,6 @@ Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Table_map # # table_id: # (test.tt_xx_1) master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F -master-bin.000001 # Query # # DROP TEMPORARY TABLE IF EXISTS `test`.`tt_1` /* generated by server */ master-bin.000001 # Xid # # COMMIT /* XID */ -e-e-e-e-e-e-e-e-e-e-e- >> B T Drop-Temp-If-TXe-Temp C << -e-e-e-e-e-e-e-e-e-e-e- @@ -369,8 +356,6 @@ master-bin.000001 # Query # # COMMIT master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Table_map # # table_id: # (test.tt_xx_1) master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F -master-bin.000001 # Query # # DROP TEMPORARY TABLE IF EXISTS `test`.`tt_1` /* generated by server */ -master-bin.000001 # Query # # DROP TEMPORARY TABLE IF EXISTS `test`.`tt_1` /* generated by server */ master-bin.000001 # Xid # # COMMIT /* XID */ -e-e-e-e-e-e-e-e-e-e-e- >> B T Drop-Temp-If-TXe-Temp N Drop-Temp-If-TXe-Temp C << -e-e-e-e-e-e-e-e-e-e-e- @@ -424,7 +409,6 @@ Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Table_map # # table_id: # (test.tt_xx_1) master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F -master-bin.000001 # Query # # DROP TEMPORARY TABLE IF EXISTS `test`.`tt_1` /* generated by server */ master-bin.000001 # Xid # # COMMIT /* XID */ -e-e-e-e-e-e-e-e-e-e-e- >> B T Drop-Temp-If-NXe-Temp C << -e-e-e-e-e-e-e-e-e-e-e- @@ -449,8 +433,6 @@ master-bin.000001 # Query # # COMMIT master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Table_map # # table_id: # (test.tt_xx_1) master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F -master-bin.000001 # Query # # DROP TEMPORARY TABLE IF EXISTS `test`.`tt_1` /* generated by server */ -master-bin.000001 # Query # # DROP TEMPORARY TABLE IF EXISTS `test`.`tt_1` /* generated by server */ master-bin.000001 # Xid # # COMMIT /* XID */ -e-e-e-e-e-e-e-e-e-e-e- >> B T Drop-Temp-If-NXe-Temp N Drop-Temp-If-NXe-Temp C << -e-e-e-e-e-e-e-e-e-e-e- @@ -755,7 +737,6 @@ Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Table_map # # table_id: # (test.tt_xx_1) master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F -master-bin.000001 # Query # # DROP TEMPORARY TABLE IF EXISTS `test`.`tt_xx_1` /* generated by server */ master-bin.000001 # Query # # ROLLBACK -e-e-e-e-e-e-e-e-e-e-e- >> B T Drop-Temp-If-Xe-Temp R << -e-e-e-e-e-e-e-e-e-e-e- @@ -782,8 +763,6 @@ master-bin.000001 # Query # # COMMIT master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Table_map # # table_id: # (test.tt_xx_1) master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F -master-bin.000001 # Query # # DROP TEMPORARY TABLE IF EXISTS `test`.`tt_xx_1` /* generated by server */ -master-bin.000001 # Query # # DROP TEMPORARY TABLE IF EXISTS `test`.`tt_xx_1` /* generated by server */ master-bin.000001 # Query # # ROLLBACK -e-e-e-e-e-e-e-e-e-e-e- >> B T Drop-Temp-If-Xe-Temp N Drop-Temp-If-Xe-Temp R << -e-e-e-e-e-e-e-e-e-e-e- @@ -839,7 +818,6 @@ Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Table_map # # table_id: # (test.tt_xx_1) master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F -master-bin.000001 # Query # # DROP TEMPORARY TABLE IF EXISTS `test`.`tt_1` /* generated by server */ master-bin.000001 # Query # # ROLLBACK -e-e-e-e-e-e-e-e-e-e-e- >> B T Drop-Temp-If-TXe-Temp R << -e-e-e-e-e-e-e-e-e-e-e- @@ -866,8 +844,6 @@ master-bin.000001 # Query # # COMMIT master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Table_map # # table_id: # (test.tt_xx_1) master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F -master-bin.000001 # Query # # DROP TEMPORARY TABLE IF EXISTS `test`.`tt_1` /* generated by server */ -master-bin.000001 # Query # # DROP TEMPORARY TABLE IF EXISTS `test`.`tt_1` /* generated by server */ master-bin.000001 # Query # # ROLLBACK -e-e-e-e-e-e-e-e-e-e-e- >> B T Drop-Temp-If-TXe-Temp N Drop-Temp-If-TXe-Temp R << -e-e-e-e-e-e-e-e-e-e-e- @@ -923,7 +899,6 @@ Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Table_map # # table_id: # (test.tt_xx_1) master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F -master-bin.000001 # Query # # DROP TEMPORARY TABLE IF EXISTS `test`.`tt_1` /* generated by server */ master-bin.000001 # Query # # ROLLBACK -e-e-e-e-e-e-e-e-e-e-e- >> B T Drop-Temp-If-NXe-Temp R << -e-e-e-e-e-e-e-e-e-e-e- @@ -950,8 +925,6 @@ master-bin.000001 # Query # # COMMIT master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Table_map # # table_id: # (test.tt_xx_1) master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F -master-bin.000001 # Query # # DROP TEMPORARY TABLE IF EXISTS `test`.`tt_1` /* generated by server */ -master-bin.000001 # Query # # DROP TEMPORARY TABLE IF EXISTS `test`.`tt_1` /* generated by server */ master-bin.000001 # Query # # ROLLBACK -e-e-e-e-e-e-e-e-e-e-e- >> B T Drop-Temp-If-NXe-Temp N Drop-Temp-If-NXe-Temp R << -e-e-e-e-e-e-e-e-e-e-e- diff --git a/mysql-test/suite/rpl/r/rpl_row_drop_temp_table.result b/mysql-test/suite/rpl/r/rpl_row_drop_temp_table.result new file mode 100644 index 00000000000..15b4d396522 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_row_drop_temp_table.result @@ -0,0 +1,36 @@ +include/master-slave.inc +[connection master] +[connection slave] +SET GLOBAL read_only=1; +[connection master] +CREATE PROCEDURE testproc() +BEGIN +DROP TEMPORARY TABLE IF EXISTS t1_tmp; +DROP TEMPORARY TABLE IF EXISTS t2_tmp; +CREATE TEMPORARY TABLE IF NOT EXISTS t1_tmp ( t1 varchar(400) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8; +CREATE TEMPORARY TABLE IF NOT EXISTS t2_tmp ( t2 varchar(16) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; +END| +SET GLOBAL read_only=1; +CALL testproc(); +******** None of the above DROP TEMPORARY TABLE statement should be found in binary log ******** +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE DEFINER=`root`@`localhost` PROCEDURE `testproc`() +BEGIN +DROP TEMPORARY TABLE IF EXISTS t1_tmp; +DROP TEMPORARY TABLE IF EXISTS t2_tmp; +CREATE TEMPORARY TABLE IF NOT EXISTS t1_tmp ( t1 varchar(400) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8; +CREATE TEMPORARY TABLE IF NOT EXISTS t2_tmp ( t2 varchar(16) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; +END +SELECT @@read_only; +@@read_only +1 +======== CLEAN UP ========= +[connection master] +DROP TEMPORARY TABLE t1_tmp; +DROP TEMPORARY TABLE t2_tmp; +DROP PROCEDURE testproc; +SET GLOBAL read_only=0; +SET GLOBAL read_only=0; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/create_or_replace.inc b/mysql-test/suite/rpl/t/create_or_replace.inc index 7d0dc487061..35a6ead60ca 100644 --- a/mysql-test/suite/rpl/t/create_or_replace.inc +++ b/mysql-test/suite/rpl/t/create_or_replace.inc @@ -205,6 +205,19 @@ set binlog_format="STATEMENT"; drop temporary table t8; set @@binlog_format=@org_binlog_format; +# MDEV-20091: +# 1. No DROP should be logged for non-existing tmp table, nor +# 2. at the connection close when its creation has not been logged. +set @@session.binlog_format=default; +drop temporary table if exists t9; + +--connect(con1,localhost,root,,) +set session binlog_format=default; +create temporary table t9 (i int); +--echo *** Must be no DROP logged for t9 when there was no CREATE, at disconnect too *** +--disconnect con1 + +--connection server_1 --source include/show_binlog_events.inc # Clean up diff --git a/mysql-test/suite/rpl/t/rpl_row_drop_temp_table.test b/mysql-test/suite/rpl/t/rpl_row_drop_temp_table.test new file mode 100644 index 00000000000..5484928f383 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_row_drop_temp_table.test @@ -0,0 +1,54 @@ +# ==== Purpose ==== +# +# Test verifies that plain DROP TEMPORARY TABLE IF EXISTS statements are not +# replicated during row based replication. +# +# ==== Implementation ==== +# +# Steps: +# 0 - Have a read_only master and slave. Binlog format should be "ROW". +# 1 - Create a procedure which executes DROP TEMPORARY TABLE IF EXISTS +# statements prior to CREATE TEMPORARY TABLE. +# 2 - Execute the procedure. +# 3 - Verify that the DROP TEMPORARY TABLE IF EXISTS statements within the +# procedure are not written to the binary log. +# +# ==== References ==== +# +# MDEV-20091: DROP TEMPORARY table is logged despite no CREATE was logged +# + +--source include/have_binlog_format_row.inc +--source include/have_innodb.inc +--source include/master-slave.inc + +--source include/rpl_connection_slave.inc +SET GLOBAL read_only=1; + +--source include/rpl_connection_master.inc +DELIMITER |; +CREATE PROCEDURE testproc() +BEGIN + DROP TEMPORARY TABLE IF EXISTS t1_tmp; + DROP TEMPORARY TABLE IF EXISTS t2_tmp; + CREATE TEMPORARY TABLE IF NOT EXISTS t1_tmp ( t1 varchar(400) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8; + CREATE TEMPORARY TABLE IF NOT EXISTS t2_tmp ( t2 varchar(16) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; +END| +DELIMITER ;| +SET GLOBAL read_only=1; +CALL testproc(); +--echo ******** None of the above DROP TEMPORARY TABLE statement should be found in binary log ******** +--source include/show_binlog_events.inc +--sync_slave_with_master +SELECT @@read_only; + +--echo ======== CLEAN UP ========= +--source include/rpl_connection_master.inc +DROP TEMPORARY TABLE t1_tmp; +DROP TEMPORARY TABLE t2_tmp; +DROP PROCEDURE testproc; +SET GLOBAL read_only=0; +--sync_slave_with_master +SET GLOBAL read_only=0; + +--source include/rpl_end.inc diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 335d3a9dfce..e8bdff8b48f 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -1205,7 +1205,7 @@ bool close_temporary_tables(THD *thd) my_thread_id save_pseudo_thread_id= thd->variables.pseudo_thread_id; char db_buf[FN_REFLEN]; String db(db_buf, sizeof(db_buf), system_charset_info); - + bool at_least_one_create_logged; /* Set pseudo_thread_id to be that of the processed table */ thd->variables.pseudo_thread_id= tmpkeyval(thd, table); @@ -1217,56 +1217,62 @@ bool close_temporary_tables(THD *thd) within the sublist of common pseudo_thread_id to create single DROP query */ - for (; + for (at_least_one_create_logged= false; table && is_user_table(table) && tmpkeyval(thd, table) == thd->variables.pseudo_thread_id && table->s->db.length == db.length() && memcmp(table->s->db.str, db.ptr(), db.length()) == 0; table= next) { - /* - We are going to add ` around the table names and possible more - due to special characters - */ - append_identifier(thd, &s_query, table->s->table_name.str, - strlen(table->s->table_name.str)); - s_query.append(','); + if (table->s->table_creation_was_logged) + { + at_least_one_create_logged= true; + /* + We are going to add ` around the table names and possible more + due to special characters + */ + append_identifier(thd, &s_query, table->s->table_name.str, + strlen(table->s->table_name.str)); + s_query.append(','); + } next= table->next; mysql_lock_remove(thd, thd->lock, table); close_temporary(table, 1, 1); } - thd->clear_error(); - CHARSET_INFO *cs_save= thd->variables.character_set_client; - thd->variables.character_set_client= system_charset_info; - thd->thread_specific_used= TRUE; - Query_log_event qinfo(thd, s_query.ptr(), - s_query.length() - 1 /* to remove trailing ',' */, - FALSE, TRUE, FALSE, 0); - qinfo.db= db.ptr(); - qinfo.db_len= db.length(); - thd->variables.character_set_client= cs_save; - - thd->get_stmt_da()->set_overwrite_status(true); - thd->transaction.stmt.mark_dropped_temp_table(); - if ((error= (mysql_bin_log.write(&qinfo) || error))) + if (at_least_one_create_logged) { - /* - If we're here following THD::cleanup, thence the connection - has been closed already. So lets print a message to the - error log instead of pushing yet another error into the - stmt_da. - - Also, we keep the error flag so that we propagate the error - up in the stack. This way, if we're the SQL thread we notice - that close_temporary_tables failed. (Actually, the SQL - thread only calls close_temporary_tables while applying old - Start_log_event_v3 events.) - */ - sql_print_error("Failed to write the DROP statement for " - "temporary tables to binary log"); + thd->clear_error(); + CHARSET_INFO *cs_save= thd->variables.character_set_client; + thd->variables.character_set_client= system_charset_info; + thd->thread_specific_used= TRUE; + Query_log_event qinfo(thd, s_query.ptr(), + s_query.length() - 1 /* to remove trailing ',' */, + FALSE, TRUE, FALSE, 0); + qinfo.db= db.ptr(); + qinfo.db_len= db.length(); + thd->variables.character_set_client= cs_save; + + thd->get_stmt_da()->set_overwrite_status(true); + thd->transaction.stmt.mark_dropped_temp_table(); + if ((error= (mysql_bin_log.write(&qinfo) || error))) + { + /* + If we're here following THD::cleanup, thence the connection + has been closed already. So lets print a message to the + error log instead of pushing yet another error into the + stmt_da. + + Also, we keep the error flag so that we propagate the error + up in the stack. This way, if we're the SQL thread we notice + that close_temporary_tables failed. (Actually, the SQL + thread only calls close_temporary_tables while applying old + Start_log_event_v3 events.) + */ + sql_print_error("Failed to write the DROP statement for " + "temporary tables to binary log"); + } + thd->get_stmt_da()->set_overwrite_status(false); } - thd->get_stmt_da()->set_overwrite_status(false); - thd->variables.pseudo_thread_id= save_pseudo_thread_id; thd->thread_specific_used= save_thread_specific_used; } diff --git a/sql/sql_table.cc b/sql/sql_table.cc index 2696513d95f..25bbaf23617 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -2265,7 +2265,7 @@ int mysql_rm_table_no_locks(THD *thd, TABLE_LIST *tables, bool if_exists, for (table= tables; table; table= table->next_local) { bool is_trans= 0; - bool table_creation_was_logged= 1; + bool table_creation_was_logged= 0; char *db=table->db; size_t db_length= table->db_length; handlerton *table_type= 0;