revision-id: 3013e205da9bb8c0e176962faf01a59d62fbec4a (mariadb-10.0.35-1-g3013e205da9) parent(s): 42fac3241368ad72f8cfef2b8521269e6c173558 author: sachin committer: sachin timestamp: 2018-05-28 15:25:04 +0530 message: MDEV-9266 Creating index on temporaray table breaks replication Problem:- Create index was logged into binlog. Goal:- Operation on temporary table should not be binlog when binlog format is row. Solution:- 1st- We should add CF_FORCE_ORIGINAL_BINLOG_FORMAT when there is ddl on temp table. 2nd- For optimize, analyze and repair we dont check if binlog format is row and this is tmp table, we dont need to log that. --- .../suite/binlog/include/check_binlog_size.inc | 31 +++++++++++ .../suite/binlog/r/binlog_tmp_table_row.result | 41 +++++++++++++++ .../suite/binlog/t/binlog_tmp_table_row.test | 60 ++++++++++++++++++++++ sql/sql_admin.cc | 15 ++++-- sql/sql_parse.cc | 35 +++++++++---- 5 files changed, 170 insertions(+), 12 deletions(-) diff --git a/mysql-test/suite/binlog/include/check_binlog_size.inc b/mysql-test/suite/binlog/include/check_binlog_size.inc new file mode 100644 index 00000000000..9df161ec843 --- /dev/null +++ b/mysql-test/suite/binlog/include/check_binlog_size.inc @@ -0,0 +1,31 @@ +# This file runs the query and checks +# whether the size of binlog is increased or not +# If size is changed it issue die command +# Parameters +# $sql_query = query to run + +#Only last row of show binlog events matter +--let $tmp= 0 +--let $counter= 1 +while ($tmp != "No such row") +{ + --let $initial_binlog_size= $tmp + --let $tmp= query_get_value(show binary logs, File_size, $counter) + --inc $counter +} + +--eval $sql_query + +--let $tmp= 0 +--let $counter= 1 +while ($tmp != "No such row") +{ + --let $current_binlog_size= $tmp + --let $tmp= query_get_value(show binary logs, File_size, $counter) + --inc $counter +} + +if ($initial_binlog_size != $current_binlog_size) +{ + die "Binlog size changed"; +} diff --git a/mysql-test/suite/binlog/r/binlog_tmp_table_row.result b/mysql-test/suite/binlog/r/binlog_tmp_table_row.result new file mode 100644 index 00000000000..3f2ef6d5e64 --- /dev/null +++ b/mysql-test/suite/binlog/r/binlog_tmp_table_row.result @@ -0,0 +1,41 @@ +RESET MASTER; +#Create table test +create temporary table t1(a int, b int); +#Add index test +create index index_a on t1(a); +#drop index test +drop index index_a on t1; +#Analyze test +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status Table is already up to date +#Optimize test +optimize table t1; +Table Op Msg_type Msg_text +test.t1 optimize status Table is already up to date +#Repair test +repair table t1; +Table Op Msg_type Msg_text +test.t1 repair status OK +#Check test +check table t1; +Table Op Msg_type Msg_text +test.t1 check status OK +#Checksum test +checksum table t1; +Table Checksum +test.t1 0 +#Rename test +rename table t1 to temp_t1; +analyze table non_existing; +Table Op Msg_type Msg_text +test.non_existing analyze Error Table 'test.non_existing' doesn't exist +test.non_existing analyze status Operation failed +optimize table non_existing; +Table Op Msg_type Msg_text +test.non_existing optimize Error Table 'test.non_existing' doesn't exist +test.non_existing optimize status Operation failed +repair table non_existing; +Table Op Msg_type Msg_text +test.non_existing repair Error Table 'test.non_existing' doesn't exist +test.non_existing repair status Operation failed diff --git a/mysql-test/suite/binlog/t/binlog_tmp_table_row.test b/mysql-test/suite/binlog/t/binlog_tmp_table_row.test new file mode 100644 index 00000000000..2f409b28012 --- /dev/null +++ b/mysql-test/suite/binlog/t/binlog_tmp_table_row.test @@ -0,0 +1,60 @@ +# ==== Purpose ==== +# +# Test if statements used temporary tables are not binlogged in the case of +# binlog_format=row +# +# ==== Method ==== +# +# We will see if binlog file size is increased or not, It should be constant for the +# entire period of test. +# +# ==== Related bugs ==== +# +# Mdev-9266 +# +source include/have_log_bin.inc; +source include/have_binlog_format_row.inc; + +RESET MASTER; + +--echo #Create table test +--let $sql_query= create temporary table t1(a int, b int) +--source suite/binlog/include/check_binlog_size.inc + +--echo #Add index test +--let $sql_query= create index index_a on t1(a) +--source suite/binlog/include/check_binlog_size.inc + +--echo #drop index test +--let $sql_query= drop index index_a on t1 +--source suite/binlog/include/check_binlog_size.inc + +--echo #Analyze test +--let $sql_query= analyze table t1 +--source suite/binlog/include/check_binlog_size.inc + +--echo #Optimize test +--let $sql_query= optimize table t1 +--source suite/binlog/include/check_binlog_size.inc + +--echo #Repair test +--let $sql_query= repair table t1 +--source suite/binlog/include/check_binlog_size.inc + +--echo #Check test +--let $sql_query= check table t1 +--source suite/binlog/include/check_binlog_size.inc + +--echo #Checksum test +--let $sql_query= checksum table t1 +--source suite/binlog/include/check_binlog_size.inc + +--echo #Rename test +--let $sql_query= rename table t1 to temp_t1 +--source suite/binlog/include/check_binlog_size.inc + + +#should not crash the server +analyze table non_existing; +optimize table non_existing; +repair table non_existing; diff --git a/sql/sql_admin.cc b/sql/sql_admin.cc index 06a453e1bb7..9310e710bad 100644 --- a/sql/sql_admin.cc +++ b/sql/sql_admin.cc @@ -1197,7 +1197,10 @@ bool Sql_cmd_analyze_table::execute(THD *thd) "analyze", lock_type, 1, 0, 0, 0, &handler::ha_analyze, 0); /* ! we write after unlocking the table */ - if (!res && !m_lex->no_write_to_binlog) + if (!res && !m_lex->no_write_to_binlog && + !(thd->is_current_stmt_binlog_format_row() && + first_table->table && + first_table->table->s->tmp_table != NO_TMP_TABLE )) { /* Presumably, ANALYZE and binlog writing doesn't require synchronization @@ -1254,7 +1257,10 @@ bool Sql_cmd_optimize_table::execute(THD *thd) "optimize", TL_WRITE, 1, 0, 0, 0, &handler::ha_optimize, 0); /* ! we write after unlocking the table */ - if (!res && !m_lex->no_write_to_binlog) + if (!res && !m_lex->no_write_to_binlog && + !(thd->is_current_stmt_binlog_format_row() && + first_table->table && + first_table->table->s->tmp_table != NO_TMP_TABLE )) { /* Presumably, OPTIMIZE and binlog writing doesn't require synchronization @@ -1287,7 +1293,10 @@ bool Sql_cmd_repair_table::execute(THD *thd) &handler::ha_repair, &view_repair); /* ! we write after unlocking the table */ - if (!res && !m_lex->no_write_to_binlog) + if (!res && !m_lex->no_write_to_binlog && + !(thd->is_current_stmt_binlog_format_row() && + first_table->table && + first_table->table->s->tmp_table != NO_TMP_TABLE )) { /* Presumably, REPAIR and binlog writing doesn't require synchronization diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index 4c0be4ebc8b..45239939bbe 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -447,6 +447,16 @@ void init_update_queries(void) CF_OPTIMIZER_TRACE; // (1) sql_command_flags[SQLCOM_EXECUTE]= CF_CAN_GENERATE_ROW_EVENTS; + /* + The following admin table operations are allowed + on log tables. + */ + sql_command_flags[SQLCOM_REPAIR]= CF_WRITE_LOGS_COMMAND | CF_AUTO_COMMIT_TRANS | CF_REPORT_PROGRESS; + sql_command_flags[SQLCOM_OPTIMIZE]|= CF_WRITE_LOGS_COMMAND | CF_AUTO_COMMIT_TRANS | CF_REPORT_PROGRESS; + sql_command_flags[SQLCOM_ANALYZE]= CF_WRITE_LOGS_COMMAND | CF_AUTO_COMMIT_TRANS | CF_REPORT_PROGRESS; + sql_command_flags[SQLCOM_CHECK]= CF_WRITE_LOGS_COMMAND | CF_AUTO_COMMIT_TRANS | CF_REPORT_PROGRESS; + sql_command_flags[SQLCOM_CHECKSUM]= CF_REPORT_PROGRESS; + /* We don't want to change to statement based replication for these commands */ @@ -457,18 +467,25 @@ void init_update_queries(void) sql_command_flags[SQLCOM_TRUNCATE]|= CF_FORCE_ORIGINAL_BINLOG_FORMAT; /* We don't want to replicate DROP for temp tables in row format */ sql_command_flags[SQLCOM_DROP_TABLE]|= CF_FORCE_ORIGINAL_BINLOG_FORMAT; + /* We don't want to replicate CREATE/DROP INDEX for temp tables in row format */ + sql_command_flags[SQLCOM_CREATE_INDEX]|= CF_FORCE_ORIGINAL_BINLOG_FORMAT; + sql_command_flags[SQLCOM_DROP_INDEX]|= CF_FORCE_ORIGINAL_BINLOG_FORMAT; + /* We don't want to replicate OPTIMIZE TABLE for temp tables in row format */ + sql_command_flags[SQLCOM_OPTIMIZE]|= CF_FORCE_ORIGINAL_BINLOG_FORMAT; + /* We don't want to replicate ANALYZE TABLE for temp tables in row format */ + sql_command_flags[SQLCOM_ANALYZE]|= CF_FORCE_ORIGINAL_BINLOG_FORMAT; + /* We don't want to replicate REPAIR TABLE for temp tables in row format */ + sql_command_flags[SQLCOM_REPAIR]|= CF_FORCE_ORIGINAL_BINLOG_FORMAT; + /* We don't want to replicate CHECK TABLE for temp tables in row format */ + sql_command_flags[SQLCOM_CHECK]|= CF_FORCE_ORIGINAL_BINLOG_FORMAT; + /* We don't want to replicate CHECKSUM TABLE for temp tables in row format */ + sql_command_flags[SQLCOM_CHECKSUM]|= CF_FORCE_ORIGINAL_BINLOG_FORMAT; + /* We don't want to replicate RENAME TABLE for temp tables in row format */ + sql_command_flags[SQLCOM_RENAME_TABLE]|= CF_FORCE_ORIGINAL_BINLOG_FORMAT; + /* One can change replication mode with SET */ sql_command_flags[SQLCOM_SET_OPTION]|= CF_FORCE_ORIGINAL_BINLOG_FORMAT; - /* - The following admin table operations are allowed - on log tables. - */ - sql_command_flags[SQLCOM_REPAIR]= CF_WRITE_LOGS_COMMAND | CF_AUTO_COMMIT_TRANS | CF_REPORT_PROGRESS; - sql_command_flags[SQLCOM_OPTIMIZE]|= CF_WRITE_LOGS_COMMAND | CF_AUTO_COMMIT_TRANS | CF_REPORT_PROGRESS; - sql_command_flags[SQLCOM_ANALYZE]= CF_WRITE_LOGS_COMMAND | CF_AUTO_COMMIT_TRANS | CF_REPORT_PROGRESS; - sql_command_flags[SQLCOM_CHECK]= CF_WRITE_LOGS_COMMAND | CF_AUTO_COMMIT_TRANS | CF_REPORT_PROGRESS; - sql_command_flags[SQLCOM_CHECKSUM]= CF_REPORT_PROGRESS; sql_command_flags[SQLCOM_CREATE_USER]|= CF_AUTO_COMMIT_TRANS; sql_command_flags[SQLCOM_DROP_USER]|= CF_AUTO_COMMIT_TRANS;