| Bug #51894 | Replication failure with SBR on DROP TEMPORARY TABLE inside a transaction | ||
|---|---|---|---|
| Submitted: | 9 Mar 2010 21:58 | Modified: | 2 Sep 2010 14:13 |
| Reporter: | Elena Stepanova | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S2 (Serious) |
| Version: | 5.6.99-m4, 5.5.3-m3 | OS: | Any |
| Assigned to: | Alfranio Tavares Correia Junior | CPU Architecture: | Any |
| Tags: | regression | ||
[9 Mar 2010 21:58]
Elena Stepanova
[10 Mar 2010 12:26]
Alfranio Tavares Correia Junior
This is a regression bug caused by BUG#46364. In SBR, most statements were written to T-cache and flushed upon committing the transaction. BUG#46364 introduced the --binlog_direct_non_transactional_updates" option which makes non-transactional changes to be written to the binary log upon committing the statement, when it is turned "ON". Any statement is identified as transactional or non-transactional through a flag in the Log_event which is set taking into account the underlie storage engine that it affects. In the current bug,the flag was not being set correctly when the DROP TEMPORARY TABLE was issued: --- 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); } else if (thd->is_current_stmt_binlog_format_row() && tmp_table_deleted) To fix the problem, we need to correctly set the flag as suggested above.
[10 Mar 2010 12:53]
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/102880 3122 Alfranio Correia 2010-03-10 BUG#51894 Replication failure with SBR on DROP TEMPORARY TABLE inside a transaction In SBR, most statements were written to trx-cache and flushed upon committing the transaction. BUG#46364 introduced the --binlog_direct_non_transactional_updates" option which makes non-transactional changes to be written to the binary log upon committing the statement, when it is turned "ON". Any statement is identified as transactional or non-transactional through a flag in the Log_event which is set taking into account the underlie storage engine that it affects. In the current bug,the flag was not being set correctly when the DROP TEMPORARY TABLE was issued: --- 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); } else if (thd->is_current_stmt_binlog_format_row() && tmp_table_deleted) Note that at this point, we cannot figure out whether the table that is about to be dropped is transactional or non-transactional and, theferore, we keep it in the trx-cache if we have transaction's boundaries otherwise, we don't care. This is done by setting the is_trans = hd->in_multi_stmt_transaction()). The approach does not harm correctness as temporary tables are only visible to the current connection and drops to non-temporary tables implicitly commit an ongoing transaction. @ mysql-test/extra/rpl_tests/rpl_implicit_commit_binlog.test Updated test case. @ mysql-test/suite/rpl/r/rpl_stm_implicit_commit_binlog.result Updated test case. @ sql/sql_table.cc Added hd->in_multi_stmt_transaction() to keep the "DROP TEMPORARY..." in the trx-cache.
[17 Mar 2010 19:37]
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/103634 3122 Alfranio Correia 2010-03-17 BUG#51894 Replication failure with SBR on DROP TEMPORARY TABLE inside a transaction 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) The need to differentiate the type of engines when dropping a temporary table stems from the fact that such drop does not commit an ongoing transaction and thus to keep the slave in sync with the master non-transactional changes (e.g. DROP TEMPORARY t_myisam), must be write ahead of the transaction. Something similar happens with the CREATE TEMPORARY. Consider the following examples, BEGIN; INSERT ... CREATE TEMPORARY TABLE t_my (int id) engine=MyISAM; INSERT INTO t_my VALUES(1); DROP TEMPORARY TABLE t_my; COMMIT If the CREATE and DROP "are" kept in the transaction, we will have what follows in the binary log BEGIN; INSERT INTO t_my VALUES(1); ---> error because t_my does not exist COMMIT BEGIN; INSERT ... CREATE TEMPORARY TABLE t_my (int id) engine=MyISAM; DROP TEMPORARY TABLE t_my; COMMIT ---------------- BEGIN; INSERT ... CREATE TEMPORARY TABLE t_inno (int id) engine=Innodb; INSERT INTO t_my VALUES(1); DROP TEMPORARY TABLE t_inno; COMMIT If the CREATE and DROP are "not" kept in the transaction, we will have what follows in the binary log: BEGIN; CREATE TEMPORARY TABLE t_inno (int id) engine=Innodb; COMMIT; BEGIN; DROP TEMPORARY TABLE t_inno; COMMIT; BEGIN; INSERT ... INSERT INTO t_inno VALUES(1); ---> error because t_inno does not exist COMMIT The problem happens in the MIXED and STMT modes but not in the ROW mode as in such information on temporary tables is not written to the binary log. To fix the problem, every drop event is generated by the server based on the executed command and the appropriate flag is set based on the engine that the command affects (i.e. transactional or non-transactional). @ mysql-test/extra/rpl_tests/rpl_implicit_commit_binlog.test Changed the test because DROP TEMPORARY was being written by the wrong order to the binary log. @ 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/r/ctype_cp932_binlog_stm.result Updated the test case because the positions changed in the binary log due to the extra information in the DROP /* generated by server */. @ mysql-test/r/mysqlbinlog.result Updated the test case because every drop is now generated. @ mysql-test/suite/binlog/r/binlog_database.result Updated the test case because we distinguish when a temporary table is dropped. @ mysql-test/suite/binlog/r/binlog_row_binlog.result Updated the test case because every drop is now generated. Updated the test case because the size of the binary log changed due to the extra information in the DROP /* generated by server */. @ mysql-test/suite/binlog/r/binlog_row_drop_tbl.result Updated the test case because every drop is now generated. @ mysql-test/suite/binlog/r/binlog_row_drop_tmp_tbl.result Updated the test case because every drop is now generated. @ mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result Updated the test case because every drop is now generated. @ mysql-test/suite/binlog/r/binlog_stm_binlog.result Updated the test case because every drop is now generated. Updated the test case because we distinguish when a temporary table is dropped. Updated the test case because the positions changed in the binary log due to the extra information in the DROP /* generated by server */. @ mysql-test/suite/binlog/r/binlog_stm_blackhole.result Updated the test case because every drop is now generated. @ mysql-test/suite/binlog/r/binlog_stm_drop_tbl.result Updated the test case because every drop is now generated. @ mysql-test/suite/binlog/r/binlog_stm_drop_tmp_tbl.result Updated the test case because every drop is now generated. Updated the test case because we distinguish when a temporary table is dropped. @ mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result Updated the test case because every drop is now generated. Updated the test case because create table with innodb was being logged ahead of the transaction and thus without the rollback. @ mysql-test/suite/binlog/r/binlog_stm_row.result Updated the test case because every drop is now generated. @ mysql-test/suite/perfschema/r/binlog_mix.result Updated the test case because every drop is now generated. @ mysql-test/suite/perfschema/r/binlog_row.result Updated the test case because every drop is now generated. @ mysql-test/suite/perfschema/r/binlog_stmt.result Updated the test case because every drop is now generated. @ mysql-test/suite/rpl/r/rpl_drop_if_exists.result Updated the test case because every drop is now generated. @ mysql-test/suite/rpl/r/rpl_heartbeat.result Updated the test case because size of the binary log changed due to the extra information in the DROP /* generated by server */. @ mysql-test/suite/rpl/r/rpl_known_bugs_detection.result Updated the test case because size of the binary log changed due to the extra information in the DROP /* generated by server */. @ mysql-test/suite/rpl/r/rpl_mixed_implicit_commit_binlog.result Updated the test case because every drop is now generated. Updated the test case to log the DROP TEMPORARY based on the type of engine. @ mysql-test/suite/rpl/r/rpl_mixed_mixing_engines.result Updated the test case because every drop is now generated. Updated the test case to log the CREATE TEMPORARY based on the type of engine. Updated the test case to log the DROP TEMPORARY based on the type of engine. @ 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_implicit_commit_binlog.result Updated the test case because every drop is now generated. @ mysql-test/suite/rpl/r/rpl_row_log.result Updated the test case because every drop is now generated. @ mysql-test/suite/rpl/r/rpl_row_log_innodb.result Updated the test case because every drop is now generated. @ mysql-test/suite/rpl/r/rpl_row_mixing_engines.result Updated the test case because every drop is now generated. @ mysql-test/suite/rpl/r/rpl_row_show_relaylog_events.result Updated the test case because every drop is now generated. @ mysql-test/suite/rpl/r/rpl_server_id.result Updated the test case because every drop is now generated. @ mysql-test/suite/rpl/r/rpl_sp.result Updated the test case because every drop is now generated. @ mysql-test/suite/rpl/r/rpl_stm_implicit_commit_binlog.result Updated the test case because every drop is now generated. @ 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_log.result Updated the test case because every drop is now generated. @ mysql-test/suite/rpl/r/rpl_stm_mix_show_relaylog_events.result Updated the test case because every drop is now generated. @ mysql-test/suite/rpl/r/rpl_stm_mixing_engines.result Updated the test case because every drop is now generated. @ mysql-test/suite/rpl/r/rpl_temp_table_mix_row.result Updated the test case because every drop is now generated. Updated the test case to log the DROP TEMPORARY based on the type of engine. @ 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_stm_innodb.test Changed the format to both statement and mixed because we removed the other test that addressed mixed and row as row does not make and now mixed and row a similar in this test. @ mysql-test/suite/rpl_ndb/r/rpl_ndb_dd_basic.result Updated the test case because every drop is now generated. @ mysql-test/suite/rpl_ndb/r/rpl_ndb_log.result Updated the test case because every drop is now generated. @ mysql-test/suite/rpl_ndb/r/rpl_ndb_mixed_implicit_commit_binlog.result Updated the test case because every drop is now generated. @ mysql-test/suite/rpl_ndb/r/rpl_ndb_row_implicit_commit_binlog.result Updated the test case because every drop is now generated. @ mysql-test/suite/rpl_ndb/r/rpl_truncate_7ndb.result Updated the test case because every drop is now generated. Removed references to positions in the binary log. @ mysql-test/suite/rpl_ndb/t/rpl_truncate_7ndb.test Updated the test case because every drop is now generated and removed references to positions in the binary log. @ mysql-test/t/ctype_cp932_binlog_stm.test Updated the test case because size of the binary log changed due to the extra information in the DROP /* generated by server */. @ 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/mysql_priv.h Changed the signature of the functions mysql_create_table_no_lock and drop_temporary_table in order to get access to the type of table manipulated (i.e. transactional or non-transactional). @ sql/sql_base.cc Changed the function drop_temporary_table in order to get access to the type of table manipulated (i.e. transactional or non-transactional). @ sql/sql_insert.cc Changed the calls to drop_temporary_table-
[17 Mar 2010 20:28]
Alfranio Tavares Correia Junior
Status ====== This is a regression bug caused by both the WL#2687 and BUG#46364. The WL#2687 affects MIXED mode whereas the BUG#46364 affects the STMT mode. Analysis of the problem ======================== 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) The need to differentiate the type of engines when dropping a temporary table stems from the fact that such drop does not commit an ongoing transaction and thus to keep the slave in sync with the master non-transactional changes (e.g. DROP TEMPORARY t_myisam), must be write ahead of the transaction. Something similar happens with the CREATE TEMPORARY. Consider the following examples, BEGIN; INSERT ... CREATE TEMPORARY TABLE t_my (int id) engine=MyISAM; INSERT INTO t_my VALUES(1); DROP TEMPORARY TABLE t_my; If the CREATE and DROP "are" kept in the transaction, we will have what follows in the binary log BEGIN; INSERT INTO t_my VALUES(1); ---> error because t_my does not exist COMMIT BEGIN; INSERT ... CREATE TEMPORARY TABLE t_my (int id) engine=MyISAM; DROP TEMPORARY TABLE t_my; COMMIT ---------------- BEGIN; INSERT ... CREATE TEMPORARY TABLE t_inno (int id) engine=Innodb; INSERT INTO t_my VALUES(1); DROP TEMPORARY TABLE t_inno; COMMIT If the CREATE and DROP are "not" kept in the transaction, we will have what follows in the binary log: BEGIN; CREATE TEMPORARY TABLE t_inno (int id) engine=Innodb; COMMIT; BEGIN; DROP TEMPORARY TABLE t_inno; COMMIT; BEGIN; INSERT ... INSERT INTO t_inno VALUES(1); ---> error because t_inno does not exist COMMIT The problem happens in the MIXED and STMT modes but not in the ROW mode as in such information on temporary tables is not written to the binary log. Proposed Solution ================= To fix the problem, every drop event is generated by the server based on the executed command and the appropriate flag is set based on the engine that the command affects (i.e. transactional or non-transactional). The drop event, or simply the drop statement, is generated in two steps. First they are prepared, e.g. "DROP" or "DROP IF EXISTS", and then the set of tables to be dropped are appended. The prepare phase is described as follows: (1) If "DROP TEMPORARY" was not executed, a "DROP" is prepared. (2) A "DROP" may result in a "DROP TEMPORARY" but the opposite is not true. (3) - A "DROP TEMPORARY" does not need to be prepared if in the RBR mode because no information on temporary table is written to the binary log in such mode. (4) - If in MIXED mode and the current format is ROW, the IF EXISTS token needs to be appended because one does not know if CREATE TEMPORARY was previously written to the binary log or not. (5) - The "IF EXISTS" token is added in both the "DROP" and "DROP TEMPORARY" if the original statement has it (6) - For temporary tables, there is a need to differentiate tables in transactional and non-transactional storage engines. After the prepare phase, the possible dropped tables are appended to the proper prepared statements as follows: (1) If it is a temporary table and the format is ROW, simply ignore the fact that a table was dropped. (2) Append to the temporary statement if: (2.1) "DROP TEMPORARY" was executed and a temporary table was affected. (2.2) "DROP" was executed but a temporary table was affected. (2.3) "DROP TEMPORARY TABLE IF EXISTS" was executed no matter if a table was dropped or not. Note, however, that if a DROP "TEMPORARY" was executed but the table was not found or is not temporary, it is not logged. DROP TEMPORARY TABLE tmp where it exists as a temporary table, in the binary log: DROP TEMPORARY tmp /* generated by server */. DROP TEMPORARY TABLE tmp where it exists as a regular table, in the binary log: /* Nothing */ DROP TEMPORARY TABLE tmp where it does not exist as regular table, in the binary log: /* Nothing */ DROP TEMPORARY TABLE tmp_1, tmp_2 where one exists and the other does not, in the binary log: DROP TEMPORARY TABLE tmp_1 /* generated by server */ (2.4) "DROP TEMPORARY TABLE IF EXISTS" is always written to the binary log provided the format is not ROW. (3) If it is not a temporary table (3.1) Append the table to the "DROP" or "DROP TABLE IF EXISTS" no matter if dropped or not. (3.2) If "DROP" contains at least a table that was dropped write the statement to the binary log. Otherwise, ignore it. (3.2) If "DROP" contains some tables that were not found, log it with the appropriate error code. DROP TABLE t where it exists as a temporary table, in the binary log: DROP TEMPORARY TABLE t /* generated by server */. DROP TABLE t where it exists as a regular table, in the binary log: DROP TABLE t /* generated by server */. DROP TABLE tmp_1, tmp_2 where one exists and the other does not, in the binary log: DROP TABLE tmp_1, tmp_2 /* generated by server */ along with the error code. (3.4) "DROP EXISTS" is always written to the binary log.
[26 Mar 2010 0:45]
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/104393 3144 Alfranio Correia 2010-03-26 BUG#51894 Replication failure with SBR on DROP TEMPORARY TABLE inside a transaction 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) The need to differentiate the type of engines when dropping a temporary table stems from the fact that such drop does not commit an ongoing transaction and thus to keep the slave in sync with the master non-transactional changes (e.g. DROP TEMPORARY t_myisam), must be write ahead of the transaction. Something similar happens with the CREATE TEMPORARY. Consider the following examples, BEGIN; INSERT ... CREATE TEMPORARY TABLE t_my (int id) engine=MyISAM; INSERT INTO t_my VALUES(1); DROP TEMPORARY TABLE t_my; COMMIT If the CREATE and DROP "are" kept in the transaction, we will have what follows in the binary log BEGIN; INSERT INTO t_my VALUES(1); ---> error because t_my does not exist COMMIT BEGIN; INSERT ... CREATE TEMPORARY TABLE t_my (int id) engine=MyISAM; DROP TEMPORARY TABLE t_my; COMMIT ---------------- BEGIN; INSERT ... CREATE TEMPORARY TABLE t_inno (int id) engine=Innodb; INSERT INTO t_my VALUES(1); DROP TEMPORARY TABLE t_inno; COMMIT If the CREATE and DROP are "not" kept in the transaction, we will have what follows in the binary log: BEGIN; CREATE TEMPORARY TABLE t_inno (int id) engine=Innodb; COMMIT; BEGIN; DROP TEMPORARY TABLE t_inno; COMMIT; BEGIN; INSERT ... INSERT INTO t_inno VALUES(1); ---> error because t_inno does not exist COMMIT The problem happens in the MIXED and STMT modes but not in the ROW mode as in such information on temporary tables is not written to the binary log. To fix the problem, every drop event is generated by the server based on the executed command and the appropriate flag is set based on the engine that the command affects (i.e. transactional or non-transactional). @ mysql-test/extra/rpl_tests/rpl_implicit_commit_binlog.test Changed the test because DROP TEMPORARY was being written by the wrong order to the binary log. @ 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/r/ctype_cp932_binlog_stm.result Updated the test case because the positions changed in the binary log due to the extra information in the DROP /* generated by server */. @ mysql-test/r/mysqlbinlog.result Updated the test case because every drop is now generated. @ mysql-test/suite/binlog/r/binlog_database.result Updated the test case because we distinguish when a temporary table is dropped. @ mysql-test/suite/binlog/r/binlog_row_binlog.result Updated the test case because every drop is now generated. Updated the test case because the size of the binary log changed due to the extra information in the DROP /* generated by server */. @ mysql-test/suite/binlog/r/binlog_row_drop_tbl.result Updated the test case because every drop is now generated. @ mysql-test/suite/binlog/r/binlog_row_drop_tmp_tbl.result Updated the test case because every drop is now generated. @ mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result Updated the test case because every drop is now generated. @ mysql-test/suite/binlog/r/binlog_stm_binlog.result Updated the test case because every drop is now generated. Updated the test case because we distinguish when a temporary table is dropped. Updated the test case because the positions changed in the binary log due to the extra information in the DROP /* generated by server */. @ mysql-test/suite/binlog/r/binlog_stm_blackhole.result Updated the test case because every drop is now generated. @ mysql-test/suite/binlog/r/binlog_stm_drop_tbl.result Updated the test case because every drop is now generated. @ mysql-test/suite/binlog/r/binlog_stm_drop_tmp_tbl.result Updated the test case because every drop is now generated. Updated the test case because we distinguish when a temporary table is dropped. @ mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result Updated the test case because every drop is now generated. Updated the test case because create table with innodb was being logged ahead of the transaction and thus without the rollback. @ mysql-test/suite/binlog/r/binlog_stm_row.result Updated the test case because every drop is now generated. @ mysql-test/suite/perfschema/r/binlog_mix.result Updated the test case because every drop is now generated. @ mysql-test/suite/perfschema/r/binlog_row.result Updated the test case because every drop is now generated. @ mysql-test/suite/perfschema/r/binlog_stmt.result Updated the test case because every drop is now generated. @ mysql-test/suite/rpl/r/rpl_drop_if_exists.result Updated the test case because every drop is now generated. @ mysql-test/suite/rpl/r/rpl_heartbeat.result Updated the test case because size of the binary log changed due to the extra information in the DROP /* generated by server */. @ mysql-test/suite/rpl/r/rpl_known_bugs_detection.result Updated the test case because size of the binary log changed due to the extra information in the DROP /* generated by server */. @ mysql-test/suite/rpl/r/rpl_mixed_implicit_commit_binlog.result Updated the test case because every drop is now generated. Updated the test case to log the DROP TEMPORARY based on the type of engine. @ mysql-test/suite/rpl/r/rpl_mixed_mixing_engines.result Updated the test case because every drop is now generated. Updated the test case to log the CREATE TEMPORARY based on the type of engine. Updated the test case to log the DROP TEMPORARY based on the type of engine. @ 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_implicit_commit_binlog.result Updated the test case because every drop is now generated. @ mysql-test/suite/rpl/r/rpl_row_log.result Updated the test case because every drop is now generated. @ mysql-test/suite/rpl/r/rpl_row_log_innodb.result Updated the test case because every drop is now generated. @ mysql-test/suite/rpl/r/rpl_row_mixing_engines.result Updated the test case because every drop is now generated. @ mysql-test/suite/rpl/r/rpl_row_show_relaylog_events.result Updated the test case because every drop is now generated. @ mysql-test/suite/rpl/r/rpl_server_id.result Updated the test case because every drop is now generated. @ mysql-test/suite/rpl/r/rpl_sp.result Updated the test case because every drop is now generated. @ mysql-test/suite/rpl/r/rpl_stm_implicit_commit_binlog.result Updated the test case because every drop is now generated. @ 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_log.result Updated the test case because every drop is now generated. @ mysql-test/suite/rpl/r/rpl_stm_mix_show_relaylog_events.result Updated the test case because every drop is now generated. @ mysql-test/suite/rpl/r/rpl_stm_mixing_engines.result Updated the test case because every drop is now generated. @ mysql-test/suite/rpl/r/rpl_temp_table_mix_row.result Updated the test case because every drop is now generated. Updated the test case to log the DROP TEMPORARY based on the type of engine. @ 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_stm_innodb.test Changed the format to both statement and mixed because we removed the other test that addressed mixed and row as row does not make and now mixed and row a similar in this test. @ mysql-test/suite/rpl_ndb/r/rpl_ndb_dd_basic.result Updated the test case because every drop is now generated. @ mysql-test/suite/rpl_ndb/r/rpl_ndb_log.result Updated the test case because every drop is now generated. @ mysql-test/suite/rpl_ndb/r/rpl_ndb_mixed_implicit_commit_binlog.result Updated the test case because every drop is now generated. @ mysql-test/suite/rpl_ndb/r/rpl_ndb_row_implicit_commit_binlog.result Updated the test case because every drop is now generated. @ mysql-test/suite/rpl_ndb/r/rpl_truncate_7ndb.result Updated the test case because every drop is now generated. Removed references to positions in the binary log. @ mysql-test/suite/rpl_ndb/t/rpl_truncate_7ndb.test Updated the test case because every drop is now generated and removed references to positions in the binary log. @ mysql-test/t/ctype_cp932_binlog_stm.test Updated the test case because size of the binary log changed due to the extra information in the DROP /* generated by server */. @ 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/mysql_priv.h Changed the signature of the functions mysql_create_table_no_lock and drop_temporary_table in order to get access to the type of table manipulated (i.e. transactional or non-transactional). @ sql/sql_base.cc Changed the function drop_temporary_table in order to get access to the type of table manipulated (i.e. transactional or non-transactional). @ sql/sql_insert.cc Changed the calls to drop_temporary_table-
[1 Apr 2010 1: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/104752 3144 Alfranio Correia 2010-04-01 BUG#51894 Replication failure with SBR on DROP TEMPORARY TABLE inside a transaction 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. ****** BUG#51894 Replication failure with SBR on DROP TEMPORARY TABLE inside a transaction 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. @ 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. ****** 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. ****** 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/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. ****** 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_drop_create_temp_table.result Added a test case. ****** Added a test case. @ 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. ****** 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. ****** Removed the test case as it did not make sense in ROW mode. @ mysql-test/suite/rpl/r/rpl_row_drop_create_temp_table.result Added a test case. ****** Added a test case. @ mysql-test/suite/rpl/r/rpl_stm_drop_create_temp_table.result Added a test case. ****** Added a test case. @ 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. ****** 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. ****** 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. ****** 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/t/rpl_mixed_drop_create_temp_table.test Added a test case. ****** Added a test case. @ mysql-test/suite/rpl/t/rpl_mixed_row_innodb.test Removed the test case as it did not make sense in ROW mode. ****** Removed the test case as it did not make sense in ROW mode. @ mysql-test/suite/rpl/t/rpl_row_drop_create_temp_table.test Added a test case. ****** Added a test case. @ mysql-test/suite/rpl/t/rpl_stm_drop_create_temp_table.test Added a test case. ****** Added a test case. @ mysql-test/suite/rpl_ndb/r/rpl_truncate_7ndb.result Updated the test case to remove references to positions in the binary log. ****** 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. ****** Updated the test case to remove references to positions in the binary log. @ 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. ****** 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. ****** 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). ****** 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). ****** 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_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. ****** 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.
[1 Apr 2010 1:17]
Alfranio Tavares Correia Junior
Please, disregard other comments on the behavior of the "drop". Temporary tables that are changed, i.e., (1) created by a CREATE TEMPORARY, CREATE TEMPORARY SELECT or CREATE LIKE; (2) dropped by a DROP; (3) dropped by a DROP TEMPORARY; (4) have a row inserted, deleted or updated; are subjected to the following rules regarding what is written to the binary log. (1) If the format is ROW, changes to temporary tables are never written to the binary log. (2) In the other formats, changes to temporary tables are written to the binary log as follows: (2.1) INSERTS, UPDATES and DELETES are wrapped by a BEGIN and then either a COMMIT/ROLLBACK. (2.2) If there is a transaction, CREATE TEMPORARY, CREATE TEMPORARY SELECT, CREATE LIKE and DROP TEMPORARY are wrapped by a BEGIN and then either a COMMIT/ROLLBACK. (2.3) Otherwise, such commands are written as single statements. (2.4) DROP is written to the binary log as a single statement because it commits an on-going transaction. (3) If the format is MIXED, there is a temporary table, and the execution switched from statements to rows, the system behaves as in ROW mode. However, if a DROP affects a temporary table, the change is written to the binary log with an IF EXISTS token because the table may not exist in the slave. This test uses the commands available at: extra/rpl_tests/rpl_drop_create_temp_table.inc
[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.
[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:11]
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:51]
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 12:35]
Jon Stephens
Documented bugfix in the 5.5.5 and 6.0.14 changelogs as follows:
Issuing any DML on a temporary table temp followed by DROP TEMPORARY
TABLE temp -- both within the same transaction -- caused replication
to fail.
See also BUG#46364, BUG#51291.
Closed.
[29 Apr 2010 10:44]
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/106919 3026 Alfranio Correia 2010-04-29 Post-merge fix for BUG#51894, BUG#53075 thread_temporary_used is not initialized causing valgrind's warnings.
[7 May 2010 9:21]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100507091908-vqyhpwf2km0aokno) (version source revid:alik@sun.com-20100507091737-12vceffs11elb25g) (merge vers: 6.0.14-alpha) (pib:16)
[7 May 2010 9:22]
Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100507091655-349gwq21ursz8y4p) (version source revid:alik@sun.com-20100507091655-349gwq21ursz8y4p) (merge vers: 5.5.5-m3) (pib:16)
[7 May 2010 9:23]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100507091823-nzao4h3qosau4tin) (version source revid:alik@sun.com-20100507091720-ib9r8uny2aeazvas) (pib:16)
[7 May 2010 19:03]
Paul DuBois
Already fixed in 5.5.x, 6.0.x.
[7 May 2010 19:26]
Paul DuBois
Addition to changelog entry: The fix introduces a change to statement-based binary logging with respect to temporary tables. Within a transaction, changes to temporary tables are saved to the transaction cache and written to the binary log when the transaction commits. Otherwise, out-of-order logging of events could occur. This means that temporary tables are treated similar to transactional tables for purposes of caching and logging. This affects assessment of statements as safe or unsafe and the associated error message was changed from: Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements that read from both transactional and non-transactional tables and write to any of them are unsafe. To: Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements that read from both transactional (or a temporary table of any engine type) and non-transactional tables and write to any of them are unsafe.
[23 Jul 2010 12:22]
Bugs System
Pushed into mysql-trunk 5.5.6-m3 (revid:alik@sun.com-20100723121820-jryu2fuw3pc53q9w) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (merge vers: 5.5.5-m3) (pib:18)
[23 Jul 2010 12:29]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100723121929-90e9zemk3jkr2ocy) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (pib:18)
[3 Aug 2010 13:21]
Jon Stephens
Already documented fix for 5.5.5. Setting back to Closed.
[25 Aug 2010 9:22]
Bugs System
Pushed into mysql-5.5 5.5.6-m3 (revid:alik@ibmvm-20100825092002-2yvkb3iwu43ycpnm) (version source revid:alik@ibmvm-20100825092002-2yvkb3iwu43ycpnm) (merge vers: 5.5.6-m3) (pib:20)
[26 Aug 2010 8:19]
Jon Stephens
Already documented fix for 5.5.5. Setting back to Closed.
[30 Aug 2010 8:31]
Bugs System
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@sun.com-20100830082732-n2eyijnv86exc5ci) (version source revid:alik@sun.com-20100830082732-n2eyijnv86exc5ci) (merge vers: 5.6.1-m4) (pib:21)
[30 Aug 2010 8:34]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100830082745-n6sh01wlwh3itasv) (version source revid:alik@sun.com-20100830082745-n6sh01wlwh3itasv) (pib:21)
[30 Aug 2010 10:56]
Jon Stephens
Added entry to the 5.6.1 changelog. Closed.
[2 Sep 2010 14:13]
Jon Stephens
No new changelog entry needed. Set back to Closed state.
