| Bug #37656 | lower_case_table_names=1 doesn't convert database names in replicated statements | ||
|---|---|---|---|
| Submitted: | 26 Jun 2008 8:02 | Modified: | 18 Dec 2010 13:02 |
| Reporter: | Victoria Reznichenko | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S3 (Non-critical) |
| Version: | 5.0 | OS: | Linux |
| Assigned to: | Luis Soares | CPU Architecture: | Any |
[2 Aug 2008 13:26]
Sven Sandberg
The same problem exists for table names when row-based logging is used: master options: --lower_case_table_names=0 --binlog-format=row slave options: --lower_case_table_names=1 Test script: # Create table `T` on master and `t` on slave CREATE TABLE T (a INT); # Master will log insert into `T`, slave does not convert table # names in row events to lower case before applying them, so # slave will stop with # Last_SQL_Error = Error 'Table 'db.T' doesn't exist' on opening tables INSERT INTO T VALUES (1);
[22 Jun 2009 18: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/76860 2804 Luis Soares 2009-06-22 BUG#37656: lower_case_table_names=1 doesn't convert database names in replicated statements This bug revealed itself while using case sensitive filesystems and exhibited two symptoms: 1. If setting lower_case_table_names=1 on the slave, but not on the master, this setting will not convert database name in replicated statements, ultimately breaking replication; 2. The same problem for symptom 1. surfaced in RBR, but this time for table names, as these would not be converted to lower case for row based replication events. Symptom 1. is addressed by conditionally converting to lower case, database name on Query_log_event constructor and Load_log_event::do_apply_event. Symptom 2. is addressed by conditionally converting to lower case database name and table name when processing Table_map_log_event. On top of these two fixes, this patch also provides functionality to automatically turn into down case user defined replication filtering rules. For example, if lower_case_table_names=1 is used simultaneously with any replication filtering rule, say --replicate-do-db=TEST, then all rules are automatically and implicitly translated to lower case, in this example it would turn do-db rule into --replicate-do-db=test. This was accomplished by extending the Rpl_filter class with a to_lower_case public method that gets called on mysqld startup if lower_case_table_names=1 is set. @ sql/log_event.cc Changed Query_log_event constructor, Load_log_event::do_apply_event and Table_map_log_event::do_apply_event to automatically turn database and table names to down case. @ sql/mysqld.cc Added check to mysqld option initialization so that whenever lower_case_table_names==1 filtering rules are turned into lower case. @ sql/rpl_filter.cc Extended Rpl_filter class by adding a to_lower_case public method that turns all internal filter rules into lower case. Used in mysqld.cc @ sql/rpl_filter.h Added to_lower_case(CHARSET_INFO * cs_info); to Rpl_filter interface.
[3 Jul 2009 10: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/77870 2804 Luis Soares 2009-07-03 BUG#37656: lower_case_table_names=1 doesn't convert database names in replicated statements This bug revealed itself while using case sensitive filesystems and exhibited two symptoms: 1. If setting lower_case_table_names=1 on the slave, but not on the master, this setting will not convert database name in replicated statements, ultimately breaking replication; 2. The same problem for symptom 1. surfaced in RBR, but this time for table names, as these would not be converted to lower case for row based replication events. Symptom 1. is addressed by conditionally converting to lower case, database name on Query_log_event constructor and Load_log_event::do_apply_event. Symptom 2. is addressed by conditionally converting to lower case database name and table name when processing Table_map_log_event. On top of these two fixes, this patch also provides functionality to automatically turn into down case user defined replication filtering rules. For example, if lower_case_table_names=1 is used simultaneously with any replication filtering rule, say --replicate-do-db=TEST, then all rules are automatically and implicitly translated to lower case, in this example it would turn do-db rule into --replicate-do-db=test. This was accomplished by extending the Rpl_filter class with a to_lower_case public method that gets called on mysqld startup if lower_case_table_names=1 is set. @ sql/log_event.cc Changed Query_log_event constructor, Load_log_event::do_apply_event and Table_map_log_event::do_apply_event to automatically turn database and table names to down case. @ sql/mysqld.cc Added check to mysqld option initialization so that whenever lower_case_table_names==1 filtering rules are turned into lower case. @ sql/rpl_filter.cc Extended Rpl_filter class by adding a to_lower_case public method that turns all internal filter rules into lower case. Used in mysqld.cc @ sql/rpl_filter.h Added to_lower_case(CHARSET_INFO * cs_info); to Rpl_filter interface.
[8 Jul 2009 14:02]
Alexander Barkov
Ok to push with minor changes. Comments send my e-mail.
[25 Jan 2010 19:49]
Andrew Hutchings
Bug #50597 duplicate of this.
[2 Mar 2010 16: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/102084 3346 Luis Soares 2010-03-02 BUG#37656: lower_case_table_names=1 doesn't convert database names in replicated statements BUG#50653: drop procedure implicitely treats db name in a case sensitive way This bug revealed itself while using case sensitive filesystems and exhibited two symptoms: 1. If setting lower_case_table_names=1 on the slave, but not on the master, this setting will not convert database name in replicated statements, ultimately breaking replication; 2. The same problem for symptom 1. surfaced in RBR, but this time for table names, as these would not be converted to lower case for row based replication events. Symptom 1. is addressed by conditionally converting to lower case, database name on Query_log_event constructor and Load_log_event::do_apply_event. Symptom 2. is addressed by conditionally converting to lower database name and table name when processing Table_map_log_event. A side note on BUG#50653. It's basically a duplicate of BUG#37656 but I included a test case for it in this patch. @ sql/log_event.cc Changed Query_log_event constructor, Load_log_event::do_apply_event and Table_map_log_event::do_apply_event to automatically turn database and table names to down case.
[31 Mar 2010 17:42]
Luis Soares
BUG#50653 was marked as duplicate of this one.
[23 Sep 2010 18:43]
Konstantin Osipov
R3 risk is too high to be fixed in MRU. If you would like it to be triaged for 5.1 or 5.5, please re-evaluate the risk.
[9 Dec 2010 15: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/126438 3429 Luis Soares 2010-12-09 [merge] BUG#37656 Manually merged bzr bundle from bug report into mysql-trunk-bugfixing. Conflicts ========= Text conflict in sql/log.cc
[10 Dec 2010 15:48]
Luis Soares
Queued in mysql-trunk-bugfixing.
[17 Dec 2010 12:55]
Bugs System
Pushed into mysql-trunk 5.6.1 (revid:georgi.kodinov@oracle.com-20101217125013-y8pb3az32rtbplc9) (version source revid:luis.soares@oracle.com-20101210150113-586io70ryd90b2p1) (merge vers: 5.6.1) (pib:24)
[18 Dec 2010 13:02]
Jon Stephens
Documented bugfix in the 5.6.1 changelog as follows:
When lower_case_table_names was set to 1 on a slave, but not on
the master, names of databases in replicated statements were not
converted, causing replication to fail on slaves using
case-sensitive file systems. This occurred for both
statement-based and row-based replication.
In addition, when using row-based replication with
lower_case_table_names set to 1 on the slave only, names of
tables were also not converted, also causing replication failure
on slaves using case-sensitive file systems.
Also added notes about this issue to description of lower_case_table_names and "Replication and Variables" in all versions of the Manual.
Closed.
Description: This bug affects all OS with case-sensitive file systems. I tested on Linux. If you have lower_case_table_names=1 set on the slave, but not on the master, this setting will not convert database name in replicated statements and it breaks replication. I created Repl_test database on master and slave, on slave it was created as repl_test because of lower_case_table_names=1. Now when I inserted a row on master, I get error on the slave: mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 127.0.0.1 Master_User: root Master_Port: 5060 Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 1013 Relay_Log_File: mysql-relay-bin.000002 Relay_Log_Pos: 235 Relay_Master_Log_File: mysql-bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1146 Last_Error: Error 'Table 'Repl_test.test' doesn't exist' on query. Default database: 'Repl_test'. Query: 'insert into TEST values(1)' Skip_Counter: 0 Exec_Master_Log_Pos: 919 Relay_Log_Space: 329 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL 1 row in set (0.00 sec) As you can see from error message, MySQL converted table name TEST to test (as expected) but not database name: Repl_test.test If I execute now this statement on slave directly, it works fine: mysql> insert into TEST values(1); Query OK, 1 row affected (0.00 sec) mysql> insert into Repl_test.TEST values(1); Query OK, 1 row affected (0.00 sec) If I don't use capital letters in database name (e.g. have repl_test on both master and slave), everything works fine as well. How to repeat: 1. run master with default lower_case_table_names value and slave with lower_case_table_names=1 2. Create database Repl_test on both servers (it will be created as repl_test on slave) 3. create table TEST on master: create table TEST(id int); 4. make dump wtih --master-data option and set-up replication 5. execute simple insert statement on master: insert into TEST values(1); 6. Check SHOW SLAVE STATUS\G on the slave. You will see an error Suggested fix: lower_case_table_names should convert database name in replicated statements as well.