| Bug #37656 | lower_case_table_names=1 doesn't convert database names in replicated statements | ||
|---|---|---|---|
| Submitted: | 26 Jun 2008 10:02 | Modified: | 20 Oct 19:04 |
| Reporter: | Victoria Reznichenko | ||
| Status: | In progress | ||
| Category: | Server: Replication | Severity: | S3 (Non-critical) |
| Version: | 5.0 | OS: | Linux |
| Assigned to: | Luís Soares | Target Version: | |
| Triage: | Triaged: D3 (Medium) / R3 (Medium) / E2 (Low) | ||
[2 Aug 2008 15: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 20: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 12: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 16:02]
Alexander Barkov
Ok to push with minor changes. Comments send my e-mail.
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.