• Alfranio Correia's avatar
    BUG#51894 Replication failure with SBR on DROP TEMPORARY TABLE inside a · 4bf7d53b
    Alfranio Correia authored
              transaction
    BUG#52616 Temp table prevents switch binlog format from STATEMENT to ROW
    
    Before the WL#2687 and BUG#46364, every non-transactional change that happened
    after a transactional change was written to trx-cache and flushed upon
    committing the transaction. WL#2687 and BUG#46364 changed this behavior and
    non-transactional changes are now written to the binary log upon committing
    the statement.
    
    A binary log event is identified as transactional or non-transactional through
    a flag in the Log_event which is set taking into account the underlie storage
    engine on what it is stems from. In the current bug, this flag was not being
    set properly when the DROP TEMPORARY TABLE was executed.
    
    However, while fixing this bug we figured out that changes to temporary tables
    should be always written to the trx-cache if there is an on-going transaction.
    Otherwise, binlog events in the reversed order would be produced.
    
    Regarding concurrency, keeping changes to temporary tables in the trx-cache is
    also safe as temporary tables are only visible to the owner connection.
    
    In this patch, we classify the following statements as unsafe:
       1 - INSERT INTO t_myisam SELECT * FROM t_myisam_temp
    
       2 - INSERT INTO t_myisam_temp SELECT * FROM t_myisam
    
       3 - CREATE TEMPORARY TABLE t_myisam_temp SELECT * FROM t_myisam
    
    On the other hand, the following statements are classified as safe:
    
       1 - INSERT INTO t_innodb SELECT * FROM t_myisam_temp
    
       2 - INSERT INTO t_myisam_temp SELECT * FROM t_innodb
    
    The patch also guarantees that transactions that have a DROP TEMPORARY are
    always written to the binary log regardless of the mode and the outcome:
    commit or rollback. In particular, the DROP TEMPORARY is extended with the
    IF EXISTS clause when the current statement logging format is set to row.
    
    Finally, the patch allows to switch from STATEMENT to MIXED/ROW when there
    are temporary tables but the contrary is not possible.
    
    mysql-test/extra/rpl_tests/rpl_binlog_max_cache_size.test:
      Updated the test case because 
         CREATE TEMPORARY TABLE t_innodb_temp SELECT * FROM t_myisam is not unsafe.
    mysql-test/extra/rpl_tests/rpl_implicit_commit_binlog.test:
      Updated the test case due to the new rules: changes to
      temporary tables are written to the binary log in the
      boundaries of a transaction if there is any.
    mysql-test/extra/rpl_tests/rpl_innodb.test:
      Removed comments from the test case that became false after the patch.
    mysql-test/extra/rpl_tests/rpl_loaddata.test:
      Suppressed warning messages due to the following cases:
      
         1 - INSERT INTO t_myisam SELECT * FROM t_myisam_temp
      
         2 - INSERT INTO t_myisam_temp SELECT * FROM t_myisam
    mysql-test/include/ctype_utf8_table.inc:
      Suppressed warning messages due to the following cases:
      
         1 - INSERT INTO t_myisam SELECT * FROM t_myisam_temp
      
         2 - INSERT INTO t_myisam_temp SELECT * FROM t_myisam
    mysql-test/r/ctype_cp932_binlog_stm.result:
      Updated the test case due to the new rules: changes to
      temporary tables are written to the binary log in the
      boundaries of a transaction if there is any.
    mysql-test/suite/binlog/r/binlog_database.result:
      Updated the test case due to the new rules: changes to
      temporary tables are written to the binary log in the
      boundaries of a transaction if there is any.
    mysql-test/suite/binlog/r/binlog_innodb_row.result:
      Updated the result file.
    mysql-test/suite/binlog/r/binlog_multi_engine.result:
      Updated the unsafe message.
    mysql-test/suite/binlog/r/binlog_row_binlog.result:
      Updated the test case due to the new rules: changes to
      temporary tables are written to the binary log in the
      boundaries of a transaction if there is any.
    mysql-test/suite/binlog/r/binlog_row_drop_tmp_tbl.result:
      Updated the test case due to the new rules: changes to
      temporary tables are written to the binary log in the
      boundaries of a transaction if there is any.
    mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result:
      Updated the test case due to the new rules: changes to
      temporary tables are written to the binary log in the
      boundaries of a transaction if there is any.
    mysql-test/suite/binlog/r/binlog_stm_binlog.result:
      Updated the result file.
    mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result:
      Updated the test case due to the new rules: changes to
      temporary tables are written to the binary log in the
      boundaries of a transaction if there is any.
    mysql-test/suite/binlog/t/binlog_tmp_table.test:
      Suppressed warning messages due to the following cases:
      
         1 - INSERT INTO t_myisam SELECT * FROM t_myisam_temp
      
         2 - INSERT INTO t_myisam_temp SELECT * FROM t_myisam
    mysql-test/suite/ndb/r/ndb_binlog_format.result:
      Updated the unsafe message.
    mysql-test/suite/rpl/r/rpl_concurrency_error.result:
      Updated the unsafe message.
    mysql-test/suite/rpl/r/rpl_mixed_binlog_max_cache_size.result:
      Updated the result file because 
         CREATE TEMPORARY TABLE t_innodb_temp SELECT * FROM t_myisam is not unsafe.
    mysql-test/suite/rpl/r/rpl_mixed_implicit_commit_binlog.result:
      Updated the test case due to the new rules: changes to
      temporary tables are written to the binary log in the
      boundaries of a transaction if there is any.
    mysql-test/suite/rpl/r/rpl_mixed_mixing_engines.result:
      Updated the test case due to the new rules: changes to
      temporary tables are written to the binary log in the
      boundaries of a transaction if there is any.
    mysql-test/suite/rpl/r/rpl_mixed_row_innodb.result:
      Added some comments to ease the understanding of the result file.
    mysql-test/suite/rpl/r/rpl_non_direct_mixed_mixing_engines.result:
      Updated the test case due to the new rules: changes to
      temporary tables are written to the binary log in the
      boundaries of a transaction if there is any.
    mysql-test/suite/rpl/r/rpl_non_direct_row_mixing_engines.result:
      Updated the test case due to the new rules: changes to
      temporary tables are written to the binary log in the
      boundaries of a transaction if there is any.
    mysql-test/suite/rpl/r/rpl_non_direct_stm_mixing_engines.result:
      Updated the test case due to the new rules: changes to
      temporary tables are written to the binary log in the
      boundaries of a transaction if there is any.
    mysql-test/suite/rpl/r/rpl_row_drop.result:
      Updated the test case due to the new rules: changes to
      temporary tables are written to the binary log in the
      boundaries of a transaction if there is any.
    mysql-test/suite/rpl/r/rpl_row_implicit_commit_binlog.result:
      Updated the test case due to the new rules: changes to
      temporary tables are written to the binary log in the
      boundaries of a transaction if there is any.
    mysql-test/suite/rpl/r/rpl_row_mixing_engines.result:
      Updated the test case due to the new rules: changes to
      temporary tables are written to the binary log in the
      boundaries of a transaction if there is any.
    mysql-test/suite/rpl/r/rpl_stm_binlog_max_cache_size.result:
      Updated the result file because 
         CREATE TEMPORARY TABLE t_innodb_temp SELECT * FROM t_myisam is not unsafe.
    mysql-test/suite/rpl/r/rpl_stm_implicit_commit_binlog.result:
      Updated the test case due to the new rules: changes to
      temporary tables are written to the binary log in the
      boundaries of a transaction if there is any.
    mysql-test/suite/rpl/r/rpl_stm_innodb.result:
      Added some comments to ease the understanding of the result file.
    mysql-test/suite/rpl/r/rpl_stm_mixing_engines.result:
      Updated the test case due to the new rules: changes to
      temporary tables are written to the binary log in the
      boundaries of a transaction if there is any.
    mysql-test/suite/rpl/r/rpl_stm_stop_middle_group.result:
      Updated the unsafe message.
    mysql-test/suite/rpl/r/rpl_temp_temporary.result:
      Added a test case.
    mysql-test/suite/rpl/t/rpl000013.test:
      Suppressed warning messages due to the following cases:
      
         1 - INSERT INTO t_myisam SELECT * FROM t_myisam_temp
      
         2 - INSERT INTO t_myisam_temp SELECT * FROM t_myisam
      
         3 - CREATE TEMPORARY TABLE t_myisam_temp SELECT * FROM t_myisam
    mysql-test/suite/rpl/t/rpl_misc_functions.test:
      Suppressed warning messages.
    mysql-test/suite/rpl/t/rpl_temp_table.test:
      Suppressed warning messages due to the following cases:
      
         1 - INSERT INTO t_myisam SELECT * FROM t_myisam_temp
      
         2 - INSERT INTO t_myisam_temp SELECT * FROM t_myisam
      
         3 - CREATE TEMPORARY TABLE t_myisam_temp SELECT * FROM t_myisam
    mysql-test/suite/rpl/t/rpl_temp_temporary.test:
      Added a test case.
    mysql-test/suite/rpl/t/rpl_temporary.test:
      Suppressed warning messages due to the following cases:
      
         1 - INSERT INTO t_myisam SELECT * FROM t_myisam_temp
      
         2 - INSERT INTO t_myisam_temp SELECT * FROM t_myisam
      
         3 - CREATE TEMPORARY TABLE t_myisam_temp SELECT * FROM t_myisam
    mysql-test/suite/rpl_ndb/r/rpl_ndb_row_implicit_commit_binlog.result:
      Updated the test case due to the new rules: changes to
      temporary tables are written to the binary log in the
      boundaries of a transaction if there is any.
    mysql-test/suite/rpl_ndb/r/rpl_truncate_7ndb.result:
      Updated the test case to remove references to positions
      in the binary log.
    mysql-test/suite/rpl_ndb/t/rpl_truncate_7ndb.test:
      Updated the test case to remove references to positions
      in the binary log.
    mysql-test/t/create_select_tmp.test:
      Suppressed warning messages due to the following cases:
      
         1 - INSERT INTO t_myisam SELECT * FROM t_myisam_temp
      
         2 - INSERT INTO t_myisam_temp SELECT * FROM t_myisam
      
         3 - CREATE TEMPORARY TABLE t_myisam_temp SELECT * FROM t_myisam
    mysql-test/t/ctype_cp932_binlog_stm.test:
      Suppressed warning messages due to the following cases:
      
         1 - INSERT INTO t_myisam SELECT * FROM t_myisam_temp
      
         2 - INSERT INTO t_myisam_temp SELECT * FROM t_myisam
      
         3 - CREATE TEMPORARY TABLE t_myisam_temp SELECT * FROM t_myisam
    mysql-test/t/mysqlbinlog.test:
      Suppressed warning messages due to the following cases:
      
         1 - INSERT INTO t_myisam SELECT * FROM t_myisam_temp
      
         2 - INSERT INTO t_myisam_temp SELECT * FROM t_myisam
      
         3 - CREATE TEMPORARY TABLE t_myisam_temp SELECT * FROM t_myisam
    sql/log.cc:
      Improved the code by creating several functions to hide decision
      on type of engine changed, commit/abort, etc:  
      
      . stmt_has_updated_non_trans_table
      
      . trans_has_updated_non_trans_table
      
      . ending_trans
      
      Updated the binlog_rollback function and the use of the 
      OPTION_KEEP_LOG which indincates when a temporary table was
      either created or dropped and as such the command must be 
      logged if not in MIXED mode and even while rolling back the
      transaction.
    sql/log.h:
      Improved the code by creating several functions to hide decision
      on type of engine changed, commit/abort, etc.
    sql/log_event.cc:
      Removed the setting of the OPTION_KEEP_LOG as it is related to CREATE
      TEMPORARY and DROP TEMPORARY and not to the type of engine (i.e.
      transactional or non-transactional).
    sql/log_event_old.cc:
      Removed the setting of the OPTION_KEEP_LOG as it is related to CREATE
      TEMPORARY and DROP TEMPORARY and not to the type of engine (i.e.
      transactional or non-transactional).
    sql/share/errmsg-utf8.txt:
      Updated the unsafe message.
    sql/sql_class.cc:
      Classifies the following statements as unsafe:
         1 - INSERT INTO t_myisam SELECT * FROM t_myisam_temp
      
         2 - INSERT INTO t_myisam_temp SELECT * FROM t_myisam
      
      On the other hand, the following statements are classified as safe:
      
         1 - INSERT INTO t_innodb SELECT * FROM t_myisam_temp
      
         2 - INSERT INTO t_myisam_temp SELECT * FROM t_innodb
    sql/sql_class.h:
      It allows to switch from STATEMENT to MIXED/ROW when there are temporary
      tables but the contrary is not possible.
    sql/sql_table.cc:
      Fixed the case that a DROP/DROP TEMPORARY that affects a temporary table in MIXED
      mode is written as a DROP TEMPORARY TABLE IF EXISTS because the table may not exist in the slave and due to the IF EXISTS token an error will never happen
      while processing the statement in the slave.
      
      Removed a function that was not being used.
    4bf7d53b
rpl_stm_mixing_engines.result 596 KB