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

          sachin.setiya.007 Sachin Setiya (Inactive) added a comment - - edited

          I think row format matter , it fails only in row format

          --source include/master-slave.inc
           
          --connection master
          create table RPL(a int);
          insert into RPL values(1);
          --error 1146
          insert into rpl values(1);
           
          --sync_slave_with_master
          select * from rpl;
           
          --connection master
          drop table RPL;
           
          --source include/rpl_end.inc
          
          

          slave

          --lower_case_table_names=1
          

          master

          --lower_case_table_names=0
          

          sachin.setiya.007 Sachin Setiya (Inactive) added a comment - - edited I think row format matter , it fails only in row format --source include/master-slave.inc   --connection master create table RPL(a int); insert into RPL values(1); --error 1146 insert into rpl values(1);   --sync_slave_with_master select * from rpl;   --connection master drop table RPL;   --source include/rpl_end.inc slave --lower_case_table_names=1 master --lower_case_table_names=0

          bb-5.5-15919

          sachin.setiya.007 Sachin Setiya (Inactive) added a comment - bb-5.5-15919
          Elkin Andrei Elkin added a comment -

          Finished the current round to wait for the final patch.

          Elkin Andrei Elkin added a comment - Finished the current round to wait for the final patch.
          Elkin Andrei Elkin added a comment -

          Fixed by

          commit e31e697f17f79ffa6913499e7e2d29866f24b475
          Author: Sachin <sachin.setiya@mariadb.com>
          Date: Sun Oct 14 23:16:53 2018 +0530

          Elkin Andrei Elkin added a comment - Fixed by commit e31e697f17f79ffa6913499e7e2d29866f24b475 Author: Sachin <sachin.setiya@mariadb.com> Date: Sun Oct 14 23:16:53 2018 +0530

          People

            alice Alice Sherepa
            claudio.nanni Claudio Nanni
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.