Hi Sujatha! On Thu, Aug 1, 2019 at 4:16 PM sujatha <sujatha.sivaku...@mariadb.com> wrote: > > revision-id: 1e58c579f5b61644a74045a0470d2c62b343eb75 > (mariadb-10.1.41-3-g1e58c579f5b) > parent(s): 0bb8f33b55cc016d0ede86b97990a3ee30dcb069 > author: Sujatha > committer: Sujatha > timestamp: 2019-08-01 16:16:05 +0530 > message: > > MDEV-18930: Failed CREATE OR REPLACE TEMPORARY not written into binary log > makes data on master and slave diverge > > Problem: > ======= > Failed CREATE OR REPLACE TEMPORARY TABLE statement which dropped the table but > failed at a later stage of creation of temporary table is not written to > binarylog in row based replication. This causes the slave to diverge. > > Analysis: > ======== > CREATE OR REPLACE statements work as shown below. > > CREATE OR REPLACE TABLE table_name (a int); > is basically the same as: > > DROP TABLE IF EXISTS table_name; > CREATE TABLE table_name (a int); > > Hence every CREATE OR REPLACE TABLE command which dropped the table should be > written to binary log, even when following CREATE TABLE part fails. In order > to achieve this, during the execution of CREATE OR REPLACE command, when a > table is dropped 'thd->log_current_statement' flag is set. When table creation > results in an error within 'mysql_create_table' code, the error handling part > looks for this flag. If it is set the failed CREATE OR REPLACE statement is > written into the binary log inspite of error. This ensure that slave doesn't > diverge from the master. In case of row based replication the error handling > code returns very early, if the table is of type temporary. This is done based > on the assumption that temporary tables are not replicated in row based > replication. > > It fails to handle the cases where a temporary table was created as part of > statement based replication at an earlier stage and the binary log format was > changed to row because of an unsafe statement. In this case when a CREATE OR > REPLACE statement is executed on this temporary table it will dropped but the > query will not be written to binary log. Hence slave diverges. > > Fix: > === > In error handling code check the return status of create table operation. If > it is successful and replication mode is row based and table is of type > temporary then return. Other wise proceed further to the code which checks for > thd->log_current_statement flag and does appropriate logging. > > --- > .../suite/rpl/r/rpl_create_or_replace_fail.result | 18 +++++++ > .../suite/rpl/t/rpl_create_or_replace_fail.test | 56 > ++++++++++++++++++++++ > sql/sql_table.cc | 2 +- > 3 files changed, 75 insertions(+), 1 deletion(-) > > diff --git a/mysql-test/suite/rpl/r/rpl_create_or_replace_fail.result > b/mysql-test/suite/rpl/r/rpl_create_or_replace_fail.result > new file mode 100644 > index 00000000000..57178f0efbe > --- /dev/null > +++ b/mysql-test/suite/rpl/r/rpl_create_or_replace_fail.result > @@ -0,0 +1,18 @@ > +include/master-slave.inc > +[connection master] > +CREATE TEMPORARY TABLE t1 (a INT NOT NULL); > +LOAD DATA INFILE 'x' INTO TABLE x; > +ERROR 42S02: Table 'test.x' doesn't exist > +CREATE OR REPLACE TEMPORARY TABLE t1 (x INT) PARTITION BY HASH(x); > +ERROR HY000: Cannot create temporary table with partitions > +"************** DROP TEMPORARY TABLE Should be present 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 > TEMPORARY TABLE t1 (a INT NOT NULL) > +master-bin.000001 # Gtid # # GTID #-#-# > +master-bin.000001 # Query # # use `test`; CREATE OR > REPLACE TEMPORARY TABLE t1 (x INT) PARTITION BY HASH(x) > +CREATE TABLE t1 (b INT); > +INSERT INTO t1 VALUES (NULL); > +DROP TABLE t1; > +include/rpl_end.inc > diff --git a/mysql-test/suite/rpl/t/rpl_create_or_replace_fail.test > b/mysql-test/suite/rpl/t/rpl_create_or_replace_fail.test > new file mode 100644 > index 00000000000..e75f34b0b56 > --- /dev/null > +++ b/mysql-test/suite/rpl/t/rpl_create_or_replace_fail.test > @@ -0,0 +1,56 @@ > +# ==== Purpose ==== > +# > +# Test verifies that failed CREATE OR REPLACE TEMPORARY TABLE statement which > +# dropped the table but failed at a later stage of creation of temporary > table > +# is written to binarylog in row based replication. > +# > +# ==== Implementation ==== > +# > +# Steps: > +# 0 - Have mixed based replication mode. > +# 1 - Create a temporary table. It will be replicated as mixed replication > +# mode is in use. > +# 2 - Execute an unsafe statement which will switch current statement > +# binlog format to 'ROW'. i.e If binlog_format=MIXED, there are open > +# temporary tables, and an unsafe statement is executed, then > subsequent > +# statements are logged in row format. Why not just set binlog format to row ? > +# 3 - Execute a CREATE OR REPLACE TEMPORARY TABLE statement which tries to > +# create partitions on temporary table. Since it is not supported it > will > +# fail. > +# 4 - Check the binary log output to ensure that the failed statement is > +# written to the binary log. > +# 5 - Slave should be up and running and in sync with master. > +# > +# ==== References ==== > +# > +# MDEV-18930: Failed CREATE OR REPLACE TEMPORARY not written into binary log > +# makes data on master and slave diverge > +# > + > +--source include/have_partition.inc > +--source include/have_binlog_format_mixed.inc > +--source include/master-slave.inc > + > +CREATE TEMPORARY TABLE t1 (a INT NOT NULL); > + > +# Execute an unsafe statement which switches replication mode internally from > +# "STATEMENT" to "ROW". > +--error ER_NO_SUCH_TABLE > +LOAD DATA INFILE 'x' INTO TABLE x; > + > +--error ER_PARTITION_NO_TEMPORARY > +CREATE OR REPLACE TEMPORARY TABLE t1 (x INT) PARTITION BY HASH(x); > + > +--echo "************** DROP TEMPORARY TABLE Should be present in Binary log > **************" > +--source include/show_binlog_events.inc > + > +CREATE TABLE t1 (b INT); > +INSERT INTO t1 VALUES (NULL); > +--sync_slave_with_master > + > +# Cleanup > +--connection master > +DROP TABLE t1; > + > +--source include/rpl_end.inc > + > diff --git a/sql/sql_table.cc b/sql/sql_table.cc > index 656834c7852..fe923d73200 100644 > --- a/sql/sql_table.cc > +++ b/sql/sql_table.cc > @@ -5092,7 +5092,7 @@ bool mysql_create_table(THD *thd, TABLE_LIST > *create_table, > > err: > /* In RBR we don't need to log CREATE TEMPORARY TABLE */ > - if (thd->is_current_stmt_binlog_format_row() && create_info->tmp_table()) > + if (!result && thd->is_current_stmt_binlog_format_row() && > create_info->tmp_table()) > DBUG_RETURN(result); Can we only binary log only the drop part ? not the create part. > > if (create_info->tmp_table()) > _______________________________________________ > commits mailing list > comm...@mariadb.org > https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits
-- Regards Sachin Setiya Software Engineer at MariaDB _______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp