| Bug #51639 | Some replication filters are case sensitive, some are not! | ||
|---|---|---|---|
| Submitted: | 2 Mar 2010 11:43 | Modified: | 29 Nov 2010 16:16 |
| Reporter: | Luis Soares | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S3 (Non-critical) |
| Version: | 5.1.45, 5.0, 5.1, 5.5.99 | OS: | Any |
| Assigned to: | Daogang Qu | CPU Architecture: | Any |
| Tags: | replicate-do-db, rpl_filters | ||
[3 Mar 2010 9:09]
Sveta Smirnova
Thank you for the report. Verified as described.
[27 Jul 2010 3:19]
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/114390 3310 Dao-Gang.Qu@sun.com 2010-07-27 Bug #51639 Some replication filters are case sensitive, some are not! There is an inconsistency in rpl filters. Some are case sensitive, while others are not. And they did not follow the set of lower_case_table_names. To fix the problem to make all the filters follow the set of lower_case_table_name to be case insensitive when seting lower_case_table_name=1. Otherwise they go to case sensitive. @ mysql-test/suite/rpl/r/rpl_do_filter.result Test result for BUG# 51639 @ mysql-test/suite/rpl/r/rpl_ignore_db_filter.result Test result for BUG# 51639 @ mysql-test/suite/rpl/r/rpl_ignore_table_filter.result Test result for BUG# 51639 @ mysql-test/suite/rpl/r/rpl_rewrite_db_filter.result Test result for BUG# 51639 @ mysql-test/suite/rpl/t/rpl_do_filter.test Added test file to verify that 'do db' and 'do table 'filters will follow the set of lower_case_table_name to be case insensitive when seting lower_case_table_name=1 @ mysql-test/suite/rpl/t/rpl_ignore_db_filter.test Added test file to verify that 'ignore db' filter will follow the set of lower_case_table_name to be case insensitive when seting lower_case_table_name=1 @ mysql-test/suite/rpl/t/rpl_ignore_table_filter.test Added test file to verify that 'ignore table' filter will follow the set of lower_case_table_name to be case sensitive when seting lower_case_table_name=0 @ mysql-test/suite/rpl/t/rpl_rewrite_db_filter.test Added test file to verify that 'rewrite db' filter will follow the set of lower_case_table_name to be case insensitive when seting lower_case_table_name=1 @ sql/rpl_filter.cc Added code to make filters will follow the set of lower_case_table_name to be case insensitive when seting lower_case_table_name=1. Otherwise they go to case sensitive.
[27 Jul 2010 6: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/114398 3310 Dao-Gang.Qu@sun.com 2010-07-27 Bug #51639 Some replication filters are case sensitive, some are not! There is an inconsistency in rpl filters. Some are case sensitive, while others are not. And they did not follow the set of lower_case_table_names. To fix the problem to make all the filters follow the set of lower_case_table_name to be case sensitive when seting lower_case_table_name=0. Otherwise they go to case insensitive. @ mysql-test/suite/rpl/r/rpl_do_filter.result Test result for BUG# 51639 @ mysql-test/suite/rpl/r/rpl_ignore_db_filter.result Test result for BUG# 51639 @ mysql-test/suite/rpl/r/rpl_ignore_table_filter.result Test result for BUG# 51639 @ mysql-test/suite/rpl/r/rpl_rewrite_db_filter.result Test result for BUG# 51639 @ mysql-test/suite/rpl/t/rpl_do_filter.test Added test file to verify that 'do db' and 'do table 'filters will follow the set of lower_case_table_name to be case insensitive when seting lower_case_table_name=1 @ mysql-test/suite/rpl/t/rpl_ignore_db_filter.test Added test file to verify that 'ignore db' filter will follow the set of lower_case_table_name to be case insensitive when seting lower_case_table_name=1 @ mysql-test/suite/rpl/t/rpl_ignore_table_filter.test Added test file to verify that 'ignore table' filter will follow the set of lower_case_table_name to be case sensitive when seting lower_case_table_name=0 @ mysql-test/suite/rpl/t/rpl_rewrite_db_filter.test Added test file to verify that 'rewrite db' filter will follow the set of lower_case_table_name to be case insensitive when seting lower_case_table_name=1 @ sql/rpl_filter.cc Added code to make filters will follow the set of lower_case_table_name to be case sensitive when seting lower_case_table_name=0. Otherwise they go to case insensitive.
[27 Jul 2010 9:46]
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/114417 3310 Dao-Gang.Qu@sun.com 2010-07-27 Bug #51639 Some replication filters are case sensitive, some are not! There is an inconsistency in name comparison of rpl filters. Some are case sensitive, while others are not. And they did not follow the setting of lower_case_table_names. To fix the problem to make all the filters follow the setting of lower_case_table_name to be case sensitive when setting lower_case_table_name=0. Otherwise they will be case insensitive. @ mysql-test/suite/rpl/r/rpl_do_filter.result Test result for BUG# 51639 @ mysql-test/suite/rpl/r/rpl_ignore_db_filter.result Test result for BUG# 51639 @ mysql-test/suite/rpl/r/rpl_ignore_table_filter.result Test result for BUG# 51639 @ mysql-test/suite/rpl/r/rpl_rewrite_db_filter.result Test result for BUG# 51639 @ mysql-test/suite/rpl/t/rpl_do_filter.test Added test file to verify that 'do db' and 'do table 'filters will follow the setting of lower_case_table_name to be case insensitive when setting lower_case_table_name > 0 @ mysql-test/suite/rpl/t/rpl_ignore_db_filter.test Added test file to verify that 'ignore db' filter will follow the setting of lower_case_table_name to be case insensitive when setting lower_case_table_name > 0 @ mysql-test/suite/rpl/t/rpl_ignore_table_filter.test Added test file to verify that 'ignore table' filter will follow the setting of lower_case_table_name to be case sensitive when setting lower_case_table_name=0 @ mysql-test/suite/rpl/t/rpl_rewrite_db_filter.test Added test file to verify that 'rewrite db' filter will follow the setting of lower_case_table_name to be case insensitive when setting lower_case_table_name > 0 @ sql/rpl_filter.cc Added code to make filters follow the setting of lower_case_table_name to be case sensitive when setting lower_case_table_name=0. Otherwise they will be case insensitive.
[28 Jul 2010 6:26]
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/114502 3310 Dao-Gang.Qu@sun.com 2010-07-28 Bug #51639 Some replication filters are case sensitive, some are not! There is an inconsistency in name comparison of rpl filters. Some are case sensitive, while others are not. And they did not follow the setting of lower_case_table_names. To fix the problem to make all the filters follow the setting of lower_case_table_name to be case sensitive when setting lower_case_table_name=0. Otherwise they will be case insensitive. @ mysql-test/suite/rpl/r/rpl_do_filter.result Test result for BUG# 51639 @ mysql-test/suite/rpl/r/rpl_ignore_db_filter.result Test result for BUG# 51639 @ mysql-test/suite/rpl/r/rpl_ignore_table_filter.result Test result for BUG# 51639 @ mysql-test/suite/rpl/r/rpl_rewrite_db_filter.result Test result for BUG# 51639 @ mysql-test/suite/rpl/t/rpl_do_filter.test Added test file to verify that 'do db' and 'do table 'filters will follow the setting of lower_case_table_name to be case insensitive when setting lower_case_table_name > 0 @ mysql-test/suite/rpl/t/rpl_ignore_db_filter.test Added test file to verify that 'ignore db' filter will follow the setting of lower_case_table_name to be case insensitive when setting lower_case_table_name > 0 @ mysql-test/suite/rpl/t/rpl_ignore_table_filter.test Added test file to verify that 'ignore table' filter will follow the setting of lower_case_table_name to be case sensitive when setting lower_case_table_name=0 @ mysql-test/suite/rpl/t/rpl_rewrite_db_filter.test Added test file to verify that 'rewrite db' filter will follow the setting of lower_case_table_name to be case insensitive when setting lower_case_table_name > 0 @ sql/rpl_filter.cc Added code to make filters follow the setting of lower_case_table_name to be case sensitive when setting lower_case_table_name=0. Otherwise they will be case insensitive.
[30 Jul 2010 3:14]
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/114691 3310 Dao-Gang.Qu@sun.com 2010-07-30 Bug #51639 Some replication filters are case sensitive, some are not! There is an inconsistency in name comparison of rpl filters. Some are case sensitive, while others are not. And they did not follow the setting of lower_case_table_names. To fix the problem to make all the filters follow the setting of lower_case_table_name to be case sensitive when setting lower_case_table_name=0. Otherwise they will be case insensitive. @ mysql-test/suite/rpl/r/rpl_do_filter.result Test result for BUG# 51639 @ mysql-test/suite/rpl/r/rpl_ignore_db_filter.result Test result for BUG# 51639 @ mysql-test/suite/rpl/r/rpl_ignore_table_filter.result Test result for BUG# 51639 @ mysql-test/suite/rpl/r/rpl_rewrite_db_filter.result Test result for BUG# 51639 @ mysql-test/suite/rpl/t/rpl_do_filter.test Added test file to verify that 'do db' and 'do table 'filters will follow the setting of lower_case_table_name to be case insensitive when setting lower_case_table_name > 0 @ mysql-test/suite/rpl/t/rpl_ignore_db_filter.test Added test file to verify that 'ignore db' filter will follow the setting of lower_case_table_name to be case insensitive when setting lower_case_table_name > 0 @ mysql-test/suite/rpl/t/rpl_ignore_table_filter.test Added test file to verify that 'ignore table' filter will follow the setting of lower_case_table_name to be case sensitive when setting lower_case_table_name=0 @ mysql-test/suite/rpl/t/rpl_rewrite_db_filter.test Added test file to verify that 'rewrite db' filter will follow the setting of lower_case_table_name to be case insensitive when setting lower_case_table_name > 0 @ sql/rpl_filter.cc Added code to make filters follow the setting of lower_case_table_name to be case sensitive when setting lower_case_table_name=0. Otherwise they will be case insensitive. @ sql/rpl_filter.h Added code to change the data struct from HASH to DYNAMIC_ARRAY for do_table and ignore_table in order to the setting of the 'do_table' and 'ignore_table' filters is affected by the setting of lower_case_table_name regardless of the setting order.
[13 Aug 2010 7:38]
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/115638 3310 Dao-Gang.Qu@sun.com 2010-08-13 Bug #51639 Some replication filters are case sensitive, some are not! There is an inconsistency in name comparison of rpl filters. Some are case sensitive, while others are not. And they did not follow the setting of lower_case_table_names. To fix the problem to make all the filters follow the setting of lower_case_table_name to be case sensitive when setting lower_case_table_name=0. Otherwise they will be case insensitive. @ mysql-test/suite/rpl/r/rpl_do_filter.result Test result for BUG# 51639 @ mysql-test/suite/rpl/r/rpl_ignore_db_filter.result Test result for BUG# 51639 @ mysql-test/suite/rpl/r/rpl_ignore_table_filter.result Test result for BUG# 51639 @ mysql-test/suite/rpl/r/rpl_rewrite_db_filter.result Test result for BUG# 51639 @ mysql-test/suite/rpl/t/rpl_do_filter.test Added test file to verify that 'do db' and 'do table 'filters will follow the setting of lower_case_table_name to be case insensitive when setting lower_case_table_name > 0 @ mysql-test/suite/rpl/t/rpl_ignore_db_filter.test Added test file to verify that 'ignore db' filter will follow the setting of lower_case_table_name to be case insensitive when setting lower_case_table_name > 0 @ mysql-test/suite/rpl/t/rpl_ignore_table_filter.test Added test file to verify that 'ignore table' filter will follow the setting of lower_case_table_name to be case sensitive when setting lower_case_table_name=0 @ mysql-test/suite/rpl/t/rpl_rewrite_db_filter.test Added test file to verify that 'rewrite db' filter will follow the setting of lower_case_table_name to be case insensitive when setting lower_case_table_name > 0 @ sql/mysqld.cc Added code to build do_table and ignore_table rules to hush after resetting of table_alias_charset, so that the filters is affected by the setting of lower_case_table_name regardless of the setting order. @ sql/rpl_filter.cc Added code to make filters follow the setting of lower_case_table_name to be case sensitive when setting lower_case_table_name=0. Otherwise they will be case insensitive. And build do_table and ignore table rules to Hash from DYNAMIC_ARRAY for quick search.
[19 Aug 2010 10:07]
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/116221 3310 Dao-Gang.Qu@sun.com 2010-08-19 Bug #51639 Some replication filters are case sensitive, some are not! There is an inconsistency in name comparison of rpl filters. Some are case sensitive, while others are not. And they did not follow the setting of lower_case_table_names. To fix the problem to make all the filters follow the setting of lower_case_table_name to be case sensitive when setting lower_case_table_name=0. Otherwise they will be case insensitive. @ mysql-test/suite/rpl/r/rpl_do_filter.result Test result for BUG# 51639 @ mysql-test/suite/rpl/r/rpl_ignore_db_filter.result Test result for BUG# 51639 @ mysql-test/suite/rpl/r/rpl_ignore_table_filter.result Test result for BUG# 51639 @ mysql-test/suite/rpl/r/rpl_rewrite_db_filter.result Test result for BUG# 51639 @ mysql-test/suite/rpl/t/rpl_do_filter.test Added test file to verify that 'do db' and 'do table 'filters will follow the setting of lower_case_table_name to be case insensitive when setting lower_case_table_name > 0 @ mysql-test/suite/rpl/t/rpl_ignore_db_filter.test Added test file to verify that 'ignore db' filter will follow the setting of lower_case_table_name to be case insensitive when setting lower_case_table_name > 0 @ mysql-test/suite/rpl/t/rpl_ignore_table_filter.test Added test file to verify that 'wild ignore table' and 'ignore table' filters will follow the setting of lower_case_table_name to be case sensitive when setting lower_case_table_name=0 @ mysql-test/suite/rpl/t/rpl_rewrite_db_filter.test Added test file to verify that 'rewrite db' filter will follow the setting of lower_case_table_name to be case insensitive when setting lower_case_table_name > 0 @ sql/mysqld.cc Added code to build do_table and ignore_table rules to hush after resetting of table_alias_charset, so that the filters is affected by the setting of lower_case_table_name regardless of the setting order. @ sql/rpl_filter.cc Added code to make filters follow the setting of lower_case_table_name to be case sensitive when setting lower_case_table_name=0. Otherwise they will be case insensitive. And build do_table and ignore table rules to Hash from DYNAMIC_ARRAY for faster filter checking.
[15 Sep 2010 7: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/118275 3287 Dao-Gang.Qu@sun.com 2010-09-15 Bug #51639 Some replication filters are case sensitive, some are not! There is an inconsistency in name comparison of rpl filters. Some are case sensitive, while others are not. And they did not follow the setting of lower_case_table_names. To fix the problem to make all the filters follow the setting of lower_case_table_name to be case sensitive when setting lower_case_table_name=0. Otherwise they will be case insensitive but accent sensitive. @ include/m_ctype.h Added code to extern my_charset_utf8_tolower_ci @ mysql-test/suite/rpl/r/rpl_do_filter.result Test result for BUG# 51639 @ mysql-test/suite/rpl/r/rpl_ignore_db_filter.result Test result for BUG# 51639 @ mysql-test/suite/rpl/r/rpl_ignore_table_filter.result Test result for BUG# 51639 @ mysql-test/suite/rpl/r/rpl_rewrite_db_filter.result Test result for BUG# 51639 @ mysql-test/suite/rpl/t/rpl_do_filter.test Added test file to verify that 'do db' and 'do table 'filters will follow the setting of lower_case_table_name to be case insensitive when setting lower_case_table_name > 0 @ mysql-test/suite/rpl/t/rpl_ignore_db_filter.test Added test file to verify that 'ignore db' filter will follow the setting of lower_case_table_name to be case insensitive when setting lower_case_table_name > 0 @ mysql-test/suite/rpl/t/rpl_ignore_table_filter.test Added test file to verify that 'wild ignore table' and 'ignore table' filters will follow the setting of lower_case_table_name to be case sensitive when setting lower_case_table_name=0 @ mysql-test/suite/rpl/t/rpl_rewrite_db_filter.test Added test file to verify that 'rewrite db' filter will follow the setting of lower_case_table_name to be case insensitive when setting lower_case_table_name > 0 @ sql/mysqld.cc Added code to build do_table and ignore_table rules to hush after resetting of table_alias_charset, so that the filters is affected by the setting of lower_case_table_name regardless of the setting order. @ sql/rpl_filter.cc Added code to make filters follow the setting of lower_case_table_name to be case sensitive when setting lower_case_table_name=0. Otherwise they will be case insensitive but accent sensitive. And build do_table and ignore table rules to Hash from DYNAMIC_ARRAY for faster filter checking. @ strings/ctype-utf8.c Added code to define a new CHARSET_INFO to support case insensitive but accent sensitive.
[8 Oct 2010 6: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/120339
[8 Oct 2010 6:51]
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/120340
[13 Oct 2010 7: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/120621
[13 Oct 2010 8:57]
Alexander Barkov
The patch http://lists.mysql.com/commits/120621 is Ok to push.
[13 Oct 2010 23:19]
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/120735
[13 Oct 2010 23:23]
Daogang Qu
Pushed into mysql-next-mr-bugfixing
[13 Nov 2010 16:25]
Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)
[13 Nov 2010 16:36]
Bugs System
Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:jimmy.yang@oracle.com-20100804103744-vbpeghipkz6pyc9z) (pib:21)
[29 Nov 2010 16:16]
Jon Stephens
Documented as follows in the 5.6.1 changelog:
Replication filtering options such as --replicate-do-db,
--replicate-rewrite-db, and --replicate-do-table were not
consistent with one another in regard to case sensitivity.
Now all --replicate-* options follow the same rules for case
sensitivity applying to names of databases and tables elsewhere
in the MySQL server, including the effects of the
lower_case_table_names system variable.
Also added notes to replication-rules and the description of lower_case_table_names in all versions of the Manual. See
http://lists.mysql.com/commits/125381 for details.
Closed.
Description: There is an inconsistency in rpl filters. Some are case sensitive, while others are not. At least, replicate-do-db and replicate-rewrite-db seem to be case sensitive while replicate-do-table is not. I suspect that there is an inconsistency in replicate-ignore-... rules as well. Looking at rpl_filter.cc, I find that: Rpl_filter::db_ok(const char* db) Rpl_filter::get_rewrite_db(const char* db, size_t *new_len) - both use strcmp while comparing db names. Rpl_filter::tables_ok(const char* db, TABLE_LIST* tables) - uses either hash_search or find_wild I have checked this in: - MySQL Version 5.1.45 - revision-id: joro@sun.com-20100215085427-g2we19b3m2a3jvne For details, check the how to repeat section. How to repeat: 1. Add a test file: rpl_do_filter.test -- source include/master-slave.inc CREATE DATABASE DB; USE DB; CREATE TABLE T1 (a INT); INSERT INTO T1 VALUES (1); -- sync_slave_with_master USE DB; SELECT * FROM T1; -- connection master DROP DATABASE DB; -- sync_slave_with_master -- exit 2. Add a opt file for slave: rpl_do_filter-slave.opt --replicate-do-db=db --replicate-do-table=db.t1 NOTE: notice the lower case in "...do-db=db" and in "...do-table=db.t1" 3. perl mysql-test-run.pl rpl_do_filter mysqltest: At line 11: query 'USE DB' failed: 1049: Unknown database 'DB' NOTE: fails for all three formats: MIXED, STATEMENT and ROW 4. Now change rpl_do_filter-slave.opt: --replicate-do-db=DB --replicate-do-table=db.t1 NOTE: notice the upper case in "...do-db=DB" but not in "...do-table=db.t1" 5. perl mysql-test-run.pl rpl_do_filter (...) rpl.rpl_do_db 'row' [ pass ] 96 ======================================== Rewrite rules are also affected! 1. Create file: rpl_rewrite_filter -- source include/master-slave.inc SET SQL_LOG_BIN=0; CREATE DATABASE DB; SET SQL_LOG_BIN=1; -- connection slave CREATE DATABASE db2; -- connection master USE DB; CREATE TABLE T1 (a INT); INSERT INTO T1 VALUES (1); -- sync_slave_with_master SHOW TABLES; USE db2; SHOW TABLES; SELECT * FROM T1; -- connection master SET SQL_LOG_BIN=0; DROP DATABASE DB; SET SQL_LOG_BIN=1; -- sync_slave_with_master DROP DATABASE db2; -- exit 2. Add a opt file for slave: rpl_rewrite_filter-slave.opt --replicate-rewrite-db="DB->db2" 3. perl mysql-test-run.pl rpl_rewrite_filter rpl.rpl_do_db 'row' [ pass ] 76 rpl.rpl_do_db 'stmt' [ pass ] 87 rpl.rpl_do_db 'mix' [ pass ] 78 4. Change opt file: --replicate-rewrite-db="db->db2" 5. perl mysql-test-run.pl rpl_rewrite_filter Error 'Unknown database 'DB'' on query. Default database: 'DB'. Query: 'CREATE TABLE T1 (a INT)' NOTE: fails for all three formats: MIXED, STATEMENT and ROW Conclusion: replicate-do-table is case insensitive while replicate-rewrite-db and replicate-do-db are not Suggested fix: Maybe all filters behave the same way, either case sensitive or case insensitive.