| Bug #42829 | binlogging enabled for all schemas regardless of binlog-db-db / binlog-ignore-db | ||
|---|---|---|---|
| Submitted: | 13 Feb 13:41 | Modified: | 11 Nov 16:01 |
| Reporter: | Nishant Deshpande | ||
| Status: | Closed | ||
| Category: | Server: Replication | Severity: | S2 (Serious) |
| Version: | 5.1.31, 5.1, 6.0 bzr | OS: | Linux (2.6.18-92.el5) |
| Assigned to: | Luís Soares | Target Version: | 5.1+ |
| Tags: | ignored, binlog-ignore-db, binlog-do-db, schemas, binary log | ||
| Triage: | Triaged: D3 (Medium) | ||
[13 Feb 13:41]
Nishant Deshpande
[13 Feb 17:57]
Valeriy Kravchuk
Thank you for the problem report. Please, try to execute: use s2; immediately after create schema s2; and check if that will change anything.
[13 Feb 21:13]
Nishant Deshpande
db1102:root metrics_utf8_2> create database s2; Query OK, 1 row affected (0.00 sec) db1102:root metrics_utf8_2> use s2; Database changed db1102:root s2> create table t1 (x int, y int); Query OK, 0 rows affected (0.00 sec) db1102:root s2> create table t2 (x int, y int); Query OK, 0 rows affected (0.00 sec) db1102:root s2> select @@tx_isolation; +----------------+ | @@tx_isolation | +----------------+ | READ-COMMITTED | +----------------+ 1 row in set (0.00 sec) db1102:root s2> insert into t1 select * from t2; ERROR 1598 (HY000): Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT'
[14 Feb 14:01]
Sveta Smirnova
Thank you for the report. Verified as described. More likely feature request though. Workaround: use session level binary log format.
[15 Feb 4:53]
Nishant Deshpande
do you mean switch to 'mixed' or 'row' binlog format in the session? i believe for 5.1.31, you need 'super' permissions to switch binlog format. i cannot allow clients who want to work on a schema that i don't want to binlog to connect as super just for this workaround. i feel like this is not a feature request, but a bug which will break existing users who expect that binlog-do-db / binlog-ignore-db will be respected. not respecting this will cause other problems as well.
[15 Feb 9:35]
Sveta Smirnova
Yes, I mean binlog_format row. Also user can change session sql_mode.
[23 Apr 13: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/72707 2840 Luis Soares 2009-04-23 BUG#42829: binlogging enabled for all schemas regardless of binlog-db-db / binlog-ignore-db InnoDB will return an error if statement based replication is used along with transaction isolation level READ-COMMITTED (or stricter), even if the statement in question is filtered out according to the binlog-do-db rules set. In this case, an error should not be printed. This patch addresses this issue by extending the existing check in external_lock to take into account the filter rules before deciding to print an error. Furthermore, it also changes decide_logging_format to take into consideration whether the statement is filtered out from binlog before decision is made. @ mysql-test/suite/binlog/r/binlog_mix_do_db.result Result file. @ mysql-test/suite/binlog/r/binlog_stm_do_db.result Result file. @ mysql-test/suite/binlog/t/binlog_mix_do_db.test Added test case for mixed format to check that statements are turned into unsafe and logged in rows and that the additional clause added in decide logging format produces no side-effects. @ mysql-test/suite/binlog/t/binlog_stm_do_db.test Test case for statement based logging. This format triggered the bug reported. @ sql/sql_base.cc Changed the check on decide_logging_format to take into account whether statement is filtered or not (and if it is in mixed mode) before making the decision on log format (error checking). @ sql/sql_class.cc Added the thd_binlog_filter_ok to INNODB_COMPATIBILITY_HOOKS set. @ storage/innobase/handler/ha_innodb.cc Extended check in external_lock to take into consideration the filtering when deciding to throw an error. @ storage/innobase/handler/ha_innodb.h Added declaration of new hook.
[20 Jun 15:58]
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/76763 2950 Luis Soares 2009-06-20 BUG#42829: binlogging enabled for all schemas regardless of binlog-db-db / binlog-ignore-db InnoDB will return an error if statement based replication is used along with transaction isolation level READ-COMMITTED (or stricter), even if the statement in question is filtered out according to the binlog-do-db rules set. In this case, an error should not be printed. This patch addresses this issue by: 1. adding a innodb compatibility hook that checks if statement is filtered (to be used when logging in STATEMENT mode). 2. extending the existing check in external_lock to verify binlog filter hook before deciding to print an error. 3. changing decide_logging_format, so that if statement is ignored, then no error is printed. This change also makes decide_logging_format to calculate capabilities for both, ROW and STMT formats always. Collecting STMT mode capabilities is done considering all engines involved (this was the behavior before this patch). Collecting ROW mode capabilities considers binlog filtering rules. So, for each engine involved in the statement, its capabilities are conly considered if the table does not belong to a filtered database. Decision on which capabilities to take (for checking if logging is possible), depends on the logging format chosen. 4. extending rpl_filter with a new method that checks for a list of tables if at least one of them belongs to a non filtered database. This is useful to find out if a statement logged in ROW mode has its all changes (possibly involving different databases) filtered. Consider the following: UPDATE db1.t1, db2.t2 SET db1.t1.a=1, db2.t2.a=2; if db1 and db2 are filtered and ROW mode is used, then statement is completely filtered from binlog. On the other hand, if only db1 was to be filtered, then changes to db2 will make it into the binlog. This new method is most useful in decide_logging_format. @ mysql-test/extra/rpl_tests/rpl_innodb_rc_do_db_cleanup.test Clean up shared part of the test. @ mysql-test/extra/rpl_tests/rpl_innodb_rc_do_db_mix_row.test Part of the test for mixed and row mode. @ mysql-test/extra/rpl_tests/rpl_innodb_rc_do_db_prepare.test Shared part of the test. (Prepare section) @ mysql-test/extra/rpl_tests/rpl_innodb_rc_do_db_stm.test Part of the test for statement mode. @ mysql-test/suite/rpl/t/rpl_innodb_rc_do_db_mix_row-master.opt Option file stating which database log. @ mysql-test/suite/rpl/t/rpl_innodb_rc_do_db_mix_row.test Mixed and Row mode logging test. @ mysql-test/suite/rpl/t/rpl_innodb_rc_do_db_stm-master.opt Option file stating which database log. @ mysql-test/suite/rpl/t/rpl_innodb_rc_do_db_stm.test Statement mode logging test. @ sql/rpl_filter.cc Added Rpl_filter::db_ok(TABLE_LIST* tables) which checks if at least one table in the list provided belongs to a database that is not filtered out. @ sql/rpl_filter.h Added 'bool db_ok(TABLE_LIST *tables)' method declaration. @ sql/sql_base.cc Reworked decide_logging_format to take into account filtering rules. After this patch, it decides to skip error reporting if statement is filtered out from binlog. Also, capabilities are calculated considering filtering rules. @ sql/sql_class.cc Added thd_binlog_filter_ok to INNODB_COMPATIBILITY_HOOKS set. @ storage/innobase/handler/ha_innodb.cc Extended check in external_lock to take into consideration the filtering when deciding to throw an error. @ storage/innobase/handler/ha_innodb.h Added declaration of new hook.
[14 Jul 22: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/78689
[23 Jul 15:00]
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/79190 3028 Luis Soares 2009-07-23 BUG#42829: binlogging enabled for all schemas regardless of binlog-db-db / binlog-ignore-db InnoDB will return an error if statement based replication is used along with transaction isolation level READ-COMMITTED (or weaker), even if the statement in question is filtered out according to the binlog-do-db rules set. In this case, an error should not be raised. This patch addresses this issue by extending the existing check in external_lock to take into account binlog filtering rules before deciding to print an error. After BUG 39934 is fixed, decide_logging_format takes into account filtering rules for SBL. Thence, there is no need to change decide_logging_format as part of this bugfix. @ mysql-test/suite/binlog/r/binlog_stm_do_db.result Result file. @ mysql-test/suite/binlog/t/binlog_stm_do_db-master.opt Server option to filter all databases except the one with the name 'b42829'. @ mysql-test/suite/binlog/t/binlog_stm_do_db.test Test case for statement based logging. This format triggered the bug reported. @ sql/sql_class.cc Added the thd_binlog_filter_ok to INNODB_COMPATIBILITY_HOOKS set. @ storage/innobase/handler/ha_innodb.cc Extended check in external_lock to take into consideration the filtering when deciding to throw an error. @ storage/innobase/handler/ha_innodb.h Added declaration of new hook.
[4 Sep 13:22]
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/82448 3094 Luis Soares 2009-09-04 BUG#42829: binlogging enabled for all schemas regardless of binlog-db-db / binlog-ignore-db InnoDB will return an error if statement based replication is used along with transaction isolation level READ-COMMITTED (or weaker), even if the statement in question is filtered out according to the binlog-do-db rules set. In this case, an error should not be printed. This patch addresses this issue by extending the existing check in external_lock to take into account the filter rules before deciding to print an error. Furthermore, it also changes decide_logging_format to take into consideration whether the statement is filtered out from binlog before decision is made. @ sql/sql_base.cc Changed the check on decide_logging_format to take into account whether statement is filtered or not in SBR. @ sql/sql_class.cc Added the thd_binlog_filter_ok to INNODB_COMPATIBILITY_HOOKS set. @ storage/innobase/handler/ha_innodb.cc Extended check in external_lock to take into consideration the filtering when deciding to throw an error. @ storage/innobase/handler/ha_innodb.h Added declaration of new hook. @ storage/innodb_plugin/handler/ha_innodb.cc Extended check in external_lock to take into consideration the filtering when deciding to throw an error. @ storage/innodb_plugin/handler/ha_innodb.h Added declaration of new hook.
[24 Sep 16: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/84520 3094 Luis Soares 2009-09-24 BUG#42829: binlogging enabled for all schemas regardless of binlog-db-db / binlog-ignore-db InnoDB will return an error if statement based replication is used along with transaction isolation level READ-COMMITTED (or weaker), even if the statement in question is filtered out according to the binlog-do-db rules set. In this case, an error should not be printed. This patch addresses this issue by extending the existing check in external_lock to take into account the filter rules before deciding to print an error. Furthermore, it also changes decide_logging_format to take into consideration whether the statement is filtered out from binlog before decision is made. @ sql/sql_base.cc Changed the check on decide_logging_format to take into account whether statement is filtered or not in SBR. @ sql/sql_class.cc Added the thd_binlog_filter_ok to INNODB_COMPATIBILITY_HOOKS set. @ storage/innobase/handler/ha_innodb.cc Extended check in external_lock to take into consideration the filtering when deciding to throw an error. @ storage/innobase/handler/ha_innodb.h Added declaration of new hook. @ storage/innodb_plugin/handler/ha_innodb.cc Extended check in external_lock to take into consideration the filtering when deciding to throw an error. @ storage/innodb_plugin/handler/ha_innodb.h Added declaration of new hook.
[2 Nov 0: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/88875 3172 Luis Soares 2009-11-01 [merge] BUG#42829: manually merged approved bzr bundle from bug report. Conflicts ========= Text conflict in sql/sql_class.cc 1 conflicts encountered.
[2 Nov 0:28]
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/88876 3661 Luis Soares 2009-11-01 [merge] BUG#42829: manual merge mysql-5.1-bugteam --> mysql-pe Conflicts ========= Conflict adding files to storage/innodb_plugin. Created directory. Conflict because storage/innodb_plugin is not versioned, but has versioned children. Versioned directory. Conflict adding files to storage/innodb_plugin/handler. Created directory. Conflict because storage/innodb_plugin/handler is not versioned, but has versioned children. Versioned directory. Contents conflict in storage/innodb_plugin/handler/ha_innodb.cc Contents conflict in storage/innodb_plugin/handler/ha_innodb.h 6 conflicts encountered.
[2 Nov 0:38]
Luís Soares
Queued in mysql-5.1-bugteam and mysql-pe.
[4 Nov 10:25]
Bugs System
Pushed into 5.1.41 (revid:joro@sun.com-20091104092152-qz96bzlf2o1japwc) (version source revid:kristofer.pettersson@sun.com-20091103162305-08l4gkeuif2ozsoj) (merge vers: 5.1.41) (pib:13)
[10 Nov 13:23]
Jon Stephens
Documented bugfix in the 5.1.41 changelog as follows:
When using statement-based replication and the transaction
isolation level was set to READ COMMITTED or stricter, InnoDB
returned an error even if the statement in question was filtered
out according to the --binlog-do-db or --binlog-ignore-db rules
in effect at the time.
Closed.
[11 Nov 7:52]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091110093407-rw5g8dys2baqkt67) (version source revid:alik@sun.com-20091109080109-7dxapd5y5pxlu08w) (merge vers: 6.0.14-alpha) (pib:13)
[11 Nov 8:00]
Bugs System
Pushed into 5.5.0-beta (revid:alik@sun.com-20091109115615-nuohp02h8mdrz8m2) (version source revid:alik@sun.com-20091105105814-cvc2tb7ogm4ewgnh) (merge vers: 5.5.0-beta) (pib:13)
[11 Nov 15:42]
Jon Stephens
Also documented in the 5.5.0 and 6.0.14 changelogs. Closed.
