Bug #28976 | Mixing trans and non-trans tables in one transaction results in incorrect binlog | ||
---|---|---|---|
Submitted: | 8 Jun 2007 14:31 | Modified: | 16 Sep 2009 9:44 |
Reporter: | Dmitry Lenev | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Replication | Severity: | S2 (Serious) |
Version: | 5.0.44-BK, 5.0.42 | OS: | Any |
Assigned to: | Alfranio Tavares Correia Junior | CPU Architecture: | Any |
[8 Jun 2007 14:31]
Dmitry Lenev
[8 Jun 2007 16:35]
Valeriy Kravchuk
Thank you for a bug report. Verified just as described with your test case on latest 5.0.44-BK on Linux.
[13 Jun 2009 14: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/76235 2936 Alfranio Correia 2009-06-13 BUG#28976 Mixing trans and non-trans tables in one transaction results in incorrect binlog Mixing transactional and non-transactional tables on behalf of a transaction may lead inconsistencies among masters and slaves. The problem stems from the fact that although modifications done to non-transactional tables on behalf of a transaction become immediately visible to the other connections it does not immediately gets to the binary log and therefore consistency is broken. After the WL#2687, it will be safe to use either the MIXED or ROW mode to do such mix. Changes to non-transactional tables will immediately get to the binary log as rows. Under the STATEMENT mode, however, changing non-transactional tables before transactional ones is the only possible safe mix. This happen because it is as if such changes had not been executed on the behalf of a transaction. On the other hand, other combinations may hide a causal dependency, thus making impossible to immediately write statements with non-transactional tables to the binary log. This patch guarantees that the safe mix under the STATEMENT mode is properly handled and also classifies whether a statement is safe or not under such mode. In a nutshell, 1 - Any update, delete or insert statement accessing a non-transactional table is safe if it is issued on behalf of a transaction before any update, delete or insert statement accessing a transactional table and issued on behalf of the same transaction. 2 - Statements with only transactional tables are safe. 3 - Statements with transactional and non-transactional tables are unsafe.
[8 Jul 2009 8:22]
Alfranio Tavares Correia Junior
Important note: It is safe to mix non-transactional statements and transactional statements in the context of a transaction provided that the non-transactional statements are issued before any transactional statement in the context of the same transaction. For instance, assume TT (i.e. Innodb table) and NT (i.e. MyIsam) and the following transactions and their respective entries in the binary log. CONNECTION BINARY LOG BEGIN T1 UPDATE INTO TN INSERT INTO TN INSERT INTO TN UPDATE INTO TN BEGIN INSERT INTO TT INSERT INTO TT COMMIT T1 COMMIT T1 BEGIN T2 INSERT INTO TN INSERT INTO TN UPDATE INTO TN UPDATE INTO TN BEGIN 2 INSERT INTO TT INSERT INTO TT ROLLBACK T2 ROLLBACK 2 BEGIN T2 INSERT INTO TN INSERT INTO TN INSERT INTO TN INSERT INTO TN COMMIT T2 BEGIN T3 INSERT INTO TN INSERT INTO TN INSERT INTO TN INSERT INTO TN ROLLBACK T3
[8 Jul 2009 8:28]
Alfranio Tavares Correia Junior
The patch in this bug report fixes the case in which a mixed statement, i.e. a statement that accesses both non-transactional and transactional tables is issued. See the changeset below and the WL#2687 for further details.
[8 Jul 2009 8: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/78186 2972 Alfranio Correia 2009-07-08 BUG#28976 Mixing trans and non-trans tables in one transaction results in incorrect binlog Mixing transactional and non-transactional tables on behalf of a transaction may lead to inconsistencies among masters and slaves. The problem stems from the fact that although modifications done to non-transactional tables on behalf of a transaction become immediately visible to the other connections it does not immediately gets to the binary log and therefore consistency is broken. After the WL#2687, it will be safe to use either the MIXED or ROW mode to do such mix. Changes to non-transactional tables will immediately get to the binary log as rows. Under the STATEMENT mode, however, changing non-transactional tables before transactional ones is the only possible safe mix. This happen because it is as if such changes had not been executed on the behalf of a transaction. On the other hand, other combinations may hide a causal dependency, thus making impossible to immediately write statements with non-transactional tables to the binary log. This patch guarantees that a mix under the STATEMENT mode is handled as follows: 1) "B T T C" generates the "B T T C" entries in the binary log. 2) "B T T R" generates an "empty" entry in the binary log. 2) "B T N C" generates the "B T N C" entries in the binary log. 3) "B T N R" generates the "B T N R" entries in the binary log. 4) "T" generates the "B T C" entries in the binary log. 5) "T" with error generates an "empty" entry in the binary log. 6) "N" generates the "N" entry in the binary log. 7) "N" generates an empty entry if the error happens in the first tuple. Otherwise, generates the "N" entry in the binary log. 8) "M" generates the "B M C" entries in the binary log. 10) "M" with error generates the "B M C" entries in the binary log. 11) "B N N T C" generates the "N N B T C" entries in the binary log. 12) "B N N T R" generates the "N N B T R" entries in the binary log. 13) "B N N C" generates the "N N" entries in the binary log. 14) "B N N R" generates the "N N" entries in the binary log. 15) "B M T C" generates the "B M T C" entries in the binary log. 16) "B M T R" generates the "B M T R" entries in the binary log.
[9 Jul 2009 14:34]
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/78289 2972 Alfranio Correia 2009-07-09 BUG#28976 Mixing trans and non-trans tables in one transaction results in incorrect binlog Mixing transactional and non-transactional tables on behalf of a transaction may lead to inconsistencies among masters and slaves. The problem stems from the fact that although modifications done to non-transactional tables on behalf of a transaction become immediately visible to the other connections it does not immediately gets to the binary log and therefore consistency is broken. After the WL#2687, it will be safe to use either the MIXED or ROW mode to do such mix. Changes to non-transactional tables will immediately get to the binary log as rows. Under the STATEMENT mode, however, changing non-transactional tables before transactional ones is the only possible safe mix. This happen because it is as if such changes had not been executed on the behalf of a transaction. On the other hand, other combinations may hide a causal dependency, thus making impossible to immediately write statements with non-transactional tables to the binary log. This patch guarantees that a mix under the STATEMENT mode is handled as follows: 1) "B T T C" generates the "B T T C" entries in the binary log. 2) "B T T R" generates an "empty" entry in the binary log. 2) "B T N C" generates the "B T N C" entries in the binary log. 3) "B T N R" generates the "B T N R" entries in the binary log. 4) "T" generates the "B T C" entries in the binary log. 5) "T" with error generates an "empty" entry in the binary log. 6) "N" generates the "N" entry in the binary log. 7) "N" generates an empty entry if the error happens in the first tuple. Otherwise, generates the "N" entry in the binary log. 8) "M" generates the "B M C" entries in the binary log. 10) "M" with error generates the "B M C" entries in the binary log. 11) "B N N T C" generates the "N N B T C" entries in the binary log. 12) "B N N T R" generates the "N N B T R" entries in the binary log. 13) "B N N C" generates the "N N" entries in the binary log. 14) "B N N R" generates the "N N" entries in the binary log. 15) "B M T C" generates the "B M T C" entries in the binary log. 16) "B M T R" generates the "B M T R" entries in the binary log.
[10 Jul 2009 11:25]
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/78373 3018 Alfranio Correia 2009-07-10 BUG#28976 Mixing trans and non-trans tables in one transaction results in incorrect binlog Mixing transactional (T) and non-transactional (N) tables on behalf of a transaction may lead to inconsistencies among masters and slaves. The problem stems from the fact that although modifications done to non-transactional tables on behalf of a transaction become immediately visible to the other connections it does not immediately gets to the binary log and therefore consistency is broken. After the WL#2687, it will be safe to use either the MIXED or ROW mode to do such mix. Changes to non-transactional tables will immediately get to the binary log as rows. Under the STATEMENT mode, however, changing non-transactional tables before transactional ones is the only possible safe mix. This happen because it is as if such changes had not been executed on the behalf of a transaction. On the other hand, other combinations may hide a causal dependency, thus making impossible to immediately write statements with non-transactional tables to the binary log before committing or rolling back the transaction. In this bug, with fix two issues regarding the mix of T statements and N statements on behalf of a transaction when in STATEMENT mode. First, multi-level (e.g. a statement that fires a trigger) or multi-table table statements (e.g. update t1, t2...) were not handled correctly. In such cases, only the flag "modified_non_trans_table" is not enough to reflect that both a N and T tables were changed. To circumvent this issue, we also check if an engine is registered in the handler's list and changed something meaning that a M table was modified. Second, the fix for BUG#43929 introduced a regression issue. In a nutshell, when a N statement fails on behalf of a transaction, it is written to the binary log with the error code appended. Unfortunately, after BUG#43929, this scenario was flushing the transactional cache (i.e. the cache where changes a stored while processing a transaction before being written to the binary log upon a commit or rollback) although a commit or a rollback was not issued. To fix this issue, we avoid flushing the transactional cache when a commit or rollback is not issued.
[10 Jul 2009 11:39]
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/78377 3018 Alfranio Correia 2009-07-10 BUG#28976 Mixing trans and non-trans tables in one transaction results in incorrect binlog Mixing transactional (T) and non-transactional (N) tables on behalf of a transaction may lead to inconsistencies among masters and slaves. The problem stems from the fact that although modifications done to non-transactional tables on behalf of a transaction become immediately visible to the other connections it does not immediately gets to the binary log and therefore consistency is broken. After the WL#2687, it will be safe to use either the MIXED or ROW mode to do such mix. Changes to non-transactional tables will immediately get to the binary log as rows. Under the STATEMENT mode, however, changing non-transactional tables before transactional ones is the only possible safe mix. This happen because it is as if such changes had not been executed on the behalf of a transaction. On the other hand, other combinations may hide a causal dependency, thus making impossible to immediately write statements with non-transactional tables to the binary log before committing or rolling back the transaction. In this bug, with fix two issues regarding the mix of T statements and N statements on behalf of a transaction when in STATEMENT mode. First, multi-level (e.g. a statement that fires a trigger) or multi-table table statements (e.g. update t1, t2...) were not handled correctly. In such cases, only the flag "modified_non_trans_table" is not enough to reflect that both a N and T tables were changed. To circumvent this issue, we also check if an engine is registered in the handler's list and changed something meaning that a M table was modified. Second, the fix for BUG#43929 introduced a regression issue. In a nutshell, when a N statement fails on behalf of a transaction, it is written to the binary log with the error code appended. Unfortunately, after BUG#43929, this scenario was flushing the transactional cache (i.e. the cache where changes a stored while processing a transaction before being written to the binary log upon a commit or rollback) although a commit or a rollback was not issued. To fix this issue, we avoid flushing the transactional cache when a commit or rollback is not issued.
[11 Jul 2009 1: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/78446 3018 Alfranio Correia 2009-07-11 BUG#28976 Mixing trans and non-trans tables in one transaction results in incorrect binlog Mixing transactional (T) and non-transactional (N) tables on behalf of a transaction may lead to inconsistencies among masters and slaves in STATEMENT mode. The problem stems from the fact that although modifications done to non-transactional tables on behalf of a transaction become immediately visible to other connections they do not immediately get to the binary log and therefore consistency is broken. Although there may be issues in mixing T and M tables in STATEMENT mode, there are safe combinations that clients find useful. In this bug, we fix two cases that were supposed to be safe. In the first case, mixing N and T tables in multi-level (e.g. a statement that fires a trigger) or multi-table table statements (e.g. update t1, t2...) were not handled correctly. In such cases, it was not possible to distinguish when a T table was updated if the sequence of changes was N and T. In a nutshell, just the flag "modified_non_trans_table" was not enough to reflect that both a N and T tables were changed. To circumvent this issue, we check if an engine is registered in the handler's list and changed something which means that a T table was modified. In the second case, the fix for BUG#43929 introduced a regression issue. In a nutshell, when a N statement fails on behalf of a transaction, it is written to the binary log with the error code appended. Unfortunately, after BUG#43929, when an N statement was failing in the context, the transactional cache was flushed causing mismatch between execution and logging histories. To fix this issue, we avoid flushing the transactional cache when a commit or rollback is not issued. Check WL#2687 for a fully patch that will make the use of either the MIXED or ROW modes completely safe.
[13 Jul 2009 9:09]
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/78507 3018 Alfranio Correia 2009-07-13 BUG#28976 Mixing trans and non-trans tables in one transaction results in incorrect binlog Mixing transactional (T) and non-transactional (N) tables on behalf of a transaction may lead to inconsistencies among masters and slaves in STATEMENT mode. The problem stems from the fact that although modifications done to non-transactional tables on behalf of a transaction become immediately visible to other connections they do not immediately get to the binary log and therefore consistency is broken. Although there may be issues in mixing T and M tables in STATEMENT mode, there are safe combinations that clients find useful. In this bug, we fix the following issue. Mixing N and T tables in multi-level (e.g. a statement that fires a trigger) or multi-table table statements (e.g. update t1, t2...) were not handled correctly. In such cases, it was not possible to distinguish when a T table was updated if the sequence of changes was N and T. In a nutshell, just the flag "modified_non_trans_table" was not enough to reflect that both a N and T tables were changed. To circumvent this issue, we check if an engine is registered in the handler's list and changed something which means that a T table was modified. Check WL#2687 for a fully patch that will make the use of either the MIXED or ROW modes completely safe. @ mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result Truncate statement is wrapped in BEGIN/COMMIT. @ mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result Truncate statement is wrapped in BEGIN/COMMIT.
[18 Aug 2009 23:31]
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/81028 3072 Alfranio Correia 2009-08-19 BUG#28976 Mixing trans and non-trans tables in one transaction results in incorrect binlog Mixing transactional (T) and non-transactional (N) tables on behalf of a transaction may lead to inconsistencies among masters and slaves in STATEMENT mode. The problem stems from the fact that although modifications done to non-transactional tables on behalf of a transaction become immediately visible to other connections they do not immediately get to the binary log and therefore consistency is broken. Although there may be issues in mixing T and M tables in STATEMENT mode, there are safe combinations that clients find useful. In this bug, we fix the following issue. Mixing N and T tables in multi-level (e.g. a statement that fires a trigger) or multi-table table statements (e.g. update t1, t2...) were not handled correctly. In such cases, it was not possible to distinguish when a T table was updated if the sequence of changes was N and T. In a nutshell, just the flag "modified_non_trans_table" was not enough to reflect that both a N and T tables were changed. To circumvent this issue, we check if an engine is registered in the handler's list and changed something which means that a T table was modified. Check WL 2687 for a fully patch that will make the use of either the MIXED or ROW modes completely safe. @ mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result Truncate statement is wrapped in BEGIN/COMMIT. @ mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result Truncate statement is wrapped in BEGIN/COMMIT.
[18 Aug 2009 23:38]
Alfranio Tavares Correia Junior
Created a new patch after founding a glitch while developing WL#2687. We should not consider the binlog as a handler when you check if a transctional table was updated and the previous patch was not taking this into account. See below what was changed: static bool stmt_has_updated_trans_table(THD *thd) { Ha_trx_info *ha_info; for (ha_info= thd->transaction.stmt.ha_list; ha_info; ha_info= ha_info->next()) { if (ha_info->is_trx_read_write() && ha_info->ht() != binlog_hton) return (TRUE); } return (FALSE); }
[26 Aug 2009 23:13]
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/81646 3082 Alfranio Correia 2009-08-27 BUG#28976 Mixing trans and non-trans tables in one transaction results in incorrect binlog Mixing transactional (T) and non-transactional (N) tables on behalf of a transaction may lead to inconsistencies among masters and slaves in STATEMENT mode. The problem stems from the fact that although modifications done to non-transactional tables on behalf of a transaction become immediately visible to other connections they do not immediately get to the binary log and therefore consistency is broken. Although there may be issues in mixing T and M tables in STATEMENT mode, there are safe combinations that clients find useful. In this bug, we fix the following issue. Mixing N and T tables in multi-level (e.g. a statement that fires a trigger) or multi-table table statements (e.g. update t1, t2...) were not handled correctly. In such cases, it was not possible to distinguish when a T table was updated if the sequence of changes was N and T. In a nutshell, just the flag "modified_non_trans_table" was not enough to reflect that both a N and T tables were changed. To circumvent this issue, we check if an engine is registered in the handler's list and changed something which means that a T table was modified. Check WL 2687 for a full-fledged patch that will make the use of either the MIXED or ROW modes completely safe. @ mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result Truncate statement is wrapped in BEGIN/COMMIT. @ mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result Truncate statement is wrapped in BEGIN/COMMIT.
[27 Aug 2009 9:33]
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/81686 3084 Alfranio Correia 2009-08-27 Post-fix for BUG#28976. Updated main.mysqlbinlog_row_trans's result file as TRUNCATE statements are wrapped in BEGIN...COMMIT.
[2 Sep 2009 16:43]
Bugs System
Pushed into 5.1.39 (revid:joro@sun.com-20090902154533-8actmfcsjfqovgsb) (version source revid:alfranio.correia@sun.com-20090827093227-9exafl3k6362bvq7) (merge vers: 5.1.39) (pib:11)
[8 Sep 2009 18:00]
Jon Stephens
Documented bugfix in the 5.1.39 changelog as follows: When using the statement-based logging format, the only possible safe combination of transactional and non-transactional statements within the same transaction is to perform any updates on non-transactional tables (such as MyISAM tables) first, before updating any transactional tables (such as those using the InnoDB storage engine). This is due to the fact that, although a modification made to a non-transactional table is immediately visible to other connections, the update is not immediately written to the binary log, which can lead to inconsistencies between master and slave. (Other combinations may hide a causal dependency, thus making it impossible to write statements updating non-transactional tables to the binary log in the correct order.) However, in some cases, this situation was not handled properly, and the determination whether a given statement was safe or not under these conditions was not always correct. In particular, a multi-table update that affected both transactional and non-transactional tables or a statement modifying data in a non-transactional table having a trigger that operated on a transactional table (or the reverse) was not determined to be unsafe when it should have been. With this fix, the following determinations regarding replication safety are made when combining updates to transactional and non-transactional tables within the same transaction in statement-based logging mode: 1. Any statement modifying data in a non-transactional table within a given transaction is considered safe if it is issued prior to any data modification statement accessing a transactional table within the same transaction. 2. A statement that updates transactional tables only is always considered safe. 3. A statement affecting both transactional and non-transactional tables within a transaction is always considered unsafe. It is not necessary that both tables be modified for this to be true; for example, a statement such as INSERT INTO innodb_table SELECT * FROM myisam_table is also considered unsafe. NOTE. The current fix is valid only when using statement-based logging mode; we plan to address similar issues occurring when using the MIXED or ROW format in a future MySQL release. Set status to NDI, waiting to push to 5.4 tree.
[14 Sep 2009 16:06]
Bugs System
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090914155317-m1g9wodmndzdj4l1) (version source revid:alik@sun.com-20090914155317-m1g9wodmndzdj4l1) (merge vers: 5.4.4-alpha) (pib:11)
[16 Sep 2009 9:44]
Jon Stephens
Also documented bugfix in the 5.4.4 changelog. Closed.
[1 Oct 2009 5:59]
Bugs System
Pushed into 5.1.39-ndb-6.3.28 (revid:jonas@mysql.com-20091001055605-ap2kiaarr7p40mmv) (version source revid:jonas@mysql.com-20091001055605-ap2kiaarr7p40mmv) (merge vers: 5.1.39-ndb-6.3.28) (pib:11)
[1 Oct 2009 7:25]
Bugs System
Pushed into 5.1.39-ndb-7.0.9 (revid:jonas@mysql.com-20091001072547-kv17uu06hfjhgjay) (version source revid:jonas@mysql.com-20091001071652-irejtnumzbpsbgk2) (merge vers: 5.1.39-ndb-7.0.9) (pib:11)
[1 Oct 2009 13:25]
Bugs System
Pushed into 5.1.39-ndb-7.1.0 (revid:jonas@mysql.com-20091001123013-g9ob2tsyctpw6zs0) (version source revid:jonas@mysql.com-20091001123013-g9ob2tsyctpw6zs0) (merge vers: 5.1.39-ndb-7.1.0) (pib:11)
[5 Oct 2009 10:50]
Bugs System
Pushed into 5.1.39-ndb-6.2.19 (revid:jonas@mysql.com-20091005103850-dwij2dojwpvf5hi6) (version source revid:jonas@mysql.com-20090930185117-bhud4ek1y0hsj1nv) (merge vers: 5.1.39-ndb-6.2.19) (pib:11)
[7 Oct 2009 14:39]
Paul DuBois
The 5.4 fix has been pushed to 5.4.3.