revision-id: db779516bb48c45ba9e70ec45af4c0b7cc078e4e (mariadb-10.0.34-56-gdb779516bb4) parent(s): 42fac3241368ad72f8cfef2b8521269e6c173558 author: Sachin Setiya committer: Sachin Setiya timestamp: 2018-05-04 17:48:58 +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/r/binlog_tmp_table_row.result | 22 ++++++ .../suite/binlog/t/binlog_tmp_table_row.test | 81 ++++++++++++++++++++++ sql/sql_admin.cc | 12 +++- sql/sql_parse.cc | 33 ++++++--- 4 files changed, 136 insertions(+), 12 deletions(-) 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..49b2103f28f --- /dev/null +++ b/mysql-test/suite/binlog/r/binlog_tmp_table_row.result @@ -0,0 +1,22 @@ +RESET MASTER; +create temporary table t1(a int, b int); +#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 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..5a2984a53f2 --- /dev/null +++ b/mysql-test/suite/binlog/t/binlog_tmp_table_row.test @@ -0,0 +1,81 @@ +# ==== 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; + +--let $binlog_size= query_get_value(show binary logs, File_size, 1) +create temporary table t1(a int, b int); +--disable_parsing + +--echo #Add index test +create index index_a on t1(a); +alter table t1 add index index_b(b); +insert into t1 values(1,2); + +--let $current_size= query_get_value(show binary logs, File_size, 1) +if ($binlog_size != $current_size) { + die "Temp table written into binlog"; +} + +--echo #drop index test +drop index index_a on t1; +drop index index_b on t1; + +--let $current_size= query_get_value(show binary logs, File_size, 1) +if ($binlog_size != $current_size) { + die "Temp table written into binlog"; +} + +--enable_parsing +--echo #Analyze test +analyze table t1; + +--let $current_size= query_get_value(show binary logs, File_size, 1) +if ($binlog_size != $current_size) { + die "Temp table written into binlog"; +} + +--echo #Optimize test +optimize table t1; + +--let $current_size= query_get_value(show binary logs, File_size, 1) +if ($binlog_size != $current_size) { + die "Temp table written into binlog"; +} + +--echo #Repair test +repair table t1; + +--let $current_size= query_get_value(show binary logs, File_size, 1) +if ($binlog_size != $current_size) { + die "Temp table written into binlog"; +} + +--echo #Check test +check table t1; + +--let $current_size= query_get_value(show binary logs, File_size, 1) +if ($binlog_size != $current_size) { + die "Temp table written into binlog"; +} +--echo #Checksum test +checksum table t1; + +--let $current_size= query_get_value(show binary logs, File_size, 1) +if ($binlog_size != $current_size) { + die "Temp table written into binlog"; +} diff --git a/sql/sql_admin.cc b/sql/sql_admin.cc index 06a453e1bb7..3b2ea349f53 100644 --- a/sql/sql_admin.cc +++ b/sql/sql_admin.cc @@ -1197,7 +1197,9 @@ 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->s->tmp_table != NO_TMP_TABLE )) { /* Presumably, ANALYZE and binlog writing doesn't require synchronization @@ -1254,7 +1256,9 @@ 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->s->tmp_table != NO_TMP_TABLE )) { /* Presumably, OPTIMIZE and binlog writing doesn't require synchronization @@ -1287,7 +1291,9 @@ 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->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..75f52ed9b87 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,23 @@ 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; + /* 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;