Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-15919

lower_case_table_names does not behave as expected(nor consistently) on Replication Slave

    Details

      Description

      While it's not a good idea to use a different setting of lower_case_table_names on Master and Slave that should still behave consistently.
      In this case the Master A has lower_case_table_names=0, the Slave B has lower_case_table_names=1.

      binlog_format doesn't make any difference.

      Two are the relevant observations:

      1) I can use uppercase or lowercase table name on B and still address the same table without any problem:

      Slave B:

      Database changed
      MariaDB [db_007]> SHOW TABLES;
      +------------------+
      | Tables_in_db_007 |
      +------------------+
      | joe              |
      +------------------+
      1 row in set (0.00 sec)
       
      MariaDB [db_007]> INSERT INTO JOE VALUES(111);
      Query OK, 1 row affected (0.02 sec)
       
      MariaDB [db_007]> INSERT INTO joe VALUES(222);
      Query OK, 1 row affected (0.01 sec)
       
      MariaDB [db_007]> SELECT * FROM JOE;
      +------+
      | id   |
      +------+
      |  111 |
      |  222 |
      +------+
      

      This is expected having lower_case_table_names=1

      But if I run the same insert on the Master A (which is case sensitive):

      Master A:

      MariaDB [db_007]> INSERT INTO joe VALUES (333);
      ERROR 1146 (42S02): Table 'db_007.joe' doesn't exist
      MariaDB [db_007]> INSERT INTO JOE VALUES (333);
      Query OK, 1 row affected (0.01 sec)
       
      MariaDB [db_007]> SELECT * FROM JOE;
      +------+
      | id   |
      +------+
      |  333 |
      +------+
      1 row in set (0.00 sec)
      
      

      That command will not make it on the Slave, like that the table name is not converted to lower case as it's done for the commands run via the cli:

      Slave B:

                      Last_IO_Error: 
                     Last_SQL_Errno: 1146
                     Last_SQL_Error: Error executing row event: 'Table 'db001.JOE' doesn't exist'
      
      

      Consideration: The Slave SQL thread is not treated like the client thread

      2) With all the above said, if I run a DDL on the Master A, that will actually make it:

      Master A:

      MariaDB [db_007]> DROP TABLE JOE;
      Query OK, 0 rows affected (0.03 sec)
       
      MariaDB [db_007]> SHOW TABLES;
      Empty set (0.00 sec)
      

      Slave B:

      MariaDB [db_007]> SELECT * FROM JOE;
      ERROR 1146 (42S02): Table 'db_007.joe' doesn't exist
      MariaDB [db_007]> SHOW TABLES;
      Empty set (0.00 sec)
       
      MariaDB [db_007]> show slave status\G
      *************************** 1. row ***************************
                     Slave_IO_State: Waiting for master to send event
                        Master_Host: 127.0.0.1
                        Master_User: repl
                        Master_Port: 40209
                      Connect_Retry: 60
                    Master_Log_File: tossanc-bin.000003
                Read_Master_Log_Pos: 1380
                     Relay_Log_File: tossanc-relay-bin.000005
                      Relay_Log_Pos: 1681
              Relay_Master_Log_File: tossanc-bin.000003
                   Slave_IO_Running: Yes
                  Slave_SQL_Running: Yes
      
      

      Consideration: The Slave SQL thread treats DDLs and DMLs differently

      This was apparently fixed in 5.6: https://bugs.mysql.com/bug.php?id=37656

        Attachments

          Activity

            People

            • Assignee:
              Elkin Andrei Elkin
              Reporter:
              claudio.nanni Claudio Nanni
            • Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: