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:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.0 OS:Linux
Assigned to: Luis Soares CPU Architecture:Any

[26 Jun 2008 8:02] Victoria Reznichenko
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.
[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.