| Bug #52616 | Temp table prevents switch binlog format from STATEMENT to ROW | ||
|---|---|---|---|
| Submitted: | 6 Apr 2010 10:35 | Modified: | 28 Apr 2010 13:00 |
| Reporter: | Zhenxing He | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S3 (Non-critical) |
| Version: | 5.1 | OS: | Any |
| Assigned to: | Alfranio Tavares Correia Junior | CPU Architecture: | Any |
[6 Apr 2010 10:35]
Zhenxing He
[13 Apr 2010 11:08]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/105489 3144 Alfranio Correia 2010-04-13 BUG#51894 Replication failure with SBR on DROP TEMPORARY TABLE inside a 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, e.g.: --- sql/sql_table.cc 2010-03-02 14:34:50 +0000 +++ sql/sql_table.cc 2010-03-10 01:39:40 +0000 tables). In this case, we can write the original query into the binary log. */ - error |= write_bin_log(thd, !error, thd->query(), thd->query_length()); + error |= write_bin_log(thd, !error, thd->query(), thd->query_length(), + ---> TRUE if innodb, FALSE if MyIsam <---); } else if (thd->is_current_stmt_binlog_format_row() && tmp_table_deleted) 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. This is necessary because BEGIN; INSERT INTO t_innodb SELECT * FROM t_myisam_temp; /* M-Statement */ DROP TEMPORARY TABLE t_myisam_temp; COMMIT; would produce the following events into the binary log BEGIN; DROP TEMPORARY TABLE t_myisam_temp; COMMIT; BEGIN; INSERT INTO t_innodb SELECT * FROM t_myisam_temp; /* ---> ERROR <--- */ COMMIT; 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 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_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 Added comments to the test case based on the fact that in ROW mode DROP TEMPORARY is not written to the binary log. @ 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_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_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/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. ****** 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 Removed the test case as it did not make sense in ROW mode. @ 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_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 comments to the test case based on the fact that in ROW mode DROP TEMPORARY is not written to the binary log. @ 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_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 @ mysql-test/suite/rpl/t/rpl_mixed_row_innodb.test Removed the test case as it did not make sense in ROW mode. @ 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 @ 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 @ 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/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 @ 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 @ 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/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.
[14 Apr 2010 11:56]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/105635 3155 Alfranio Correia 2010-04-14 BUG#51894 Replication failure with SBR on DROP TEMPORARY TABLE inside a 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, e.g.: --- sql/sql_table.cc 2010-03-02 14:34:50 +0000 +++ sql/sql_table.cc 2010-03-10 01:39:40 +0000 tables). In this case, we can write the original query into the binary log. */ - error |= write_bin_log(thd, !error, thd->query(), thd->query_length()); + error |= write_bin_log(thd, !error, thd->query(), thd->query_length(), + ---> TRUE if innodb, FALSE if MyIsam <---); } else if (thd->is_current_stmt_binlog_format_row() && tmp_table_deleted) 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. This is necessary because BEGIN; INSERT INTO t_innodb SELECT * FROM t_myisam_temp; /* M-Statement */ DROP TEMPORARY TABLE t_myisam_temp; COMMIT; would produce the following events into the binary log BEGIN; DROP TEMPORARY TABLE t_myisam_temp; COMMIT; BEGIN; INSERT INTO t_innodb SELECT * FROM t_myisam_temp; /* ---> ERROR <--- */ COMMIT; 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 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_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 Added comments to the test case based on the fact that in ROW mode DROP TEMPORARY is not written to the binary log. @ 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_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_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/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. ****** 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 Removed the test case as it did not make sense in ROW mode. @ 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_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 comments to the test case based on the fact that in ROW mode DROP TEMPORARY is not written to the binary log. @ 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_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 @ mysql-test/suite/rpl/t/rpl_mixed_row_innodb.test Removed the test case as it did not make sense in ROW mode. @ 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 @ 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 @ 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/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 @ 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 @ 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/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.
[15 Apr 2010 9:15]
Zhenxing He
This bug is required by bug#51894, please triage and tag accordingly, thank you!
[16 Apr 2010 8:01]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/105811 3155 Alfranio Correia 2010-04-16 BUG#51894 Replication failure with SBR on DROP TEMPORARY TABLE inside a 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_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/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 Removed the test case as it did not make sense in ROW mode. @ 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_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/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.
[16 Apr 2010 10:30]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/105844 3156 Alfranio Correia 2010-04-16 BUG#51894 Replication failure with SBR on DROP TEMPORARY TABLE inside a transaction BUG#52616 Temp table prevents switch binlog format from STATEMENT to ROW Post-fix.
[20 Apr 2010 9:12]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/106077 3015 Alfranio Correia 2010-04-20 BUG#51894 Replication failure with SBR on DROP TEMPORARY TABLE inside a 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.
[27 Apr 2010 9:45]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100427094135-5s49ecp3ckson6e2) (version source revid:alik@sun.com-20100427093843-uekr85qkd7orx12t) (merge vers: 6.0.14-alpha) (pib:16)
[27 Apr 2010 9:48]
Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100427093804-a2k3rrjpwu5jegu8) (version source revid:alik@sun.com-20100427093804-a2k3rrjpwu5jegu8) (merge vers: 5.5.5-m3) (pib:16)
[27 Apr 2010 9:50]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100427094036-38frbg3famdlvjup) (version source revid:alik@sun.com-20100427093825-92wc8b22d4yg34ju) (pib:16)
[28 Apr 2010 13:00]
Jon Stephens
Documented bugfix in the 5.5.5 and 6.0.14 changelogs as follows:
When temporary tables were in use, switching the binary logging
format from STATEMENT to ROW did not take effect until all temporary
tables were dropped. (The existence of temporary tables should prevent
switching the format only from ROW to STATEMENT from taking effect,
not the reverse.)
Closed.
