[MDEV-15919] lower_case_table_names does not behave as expected(nor consistently) on Replication Slave Created: 2018-04-18  Updated: 2023-06-16  Resolved: 2018-10-29

Status: Closed
Project: MariaDB Server
Component/s: Replication
Affects Version/s: 10.2
Fix Version/s: 5.5.62, 10.0.37, 10.3.11, 10.1.37, 10.2.19

Type: Bug Priority: Critical
Reporter: Claudio Nanni Assignee: Alice Sherepa
Resolution: Fixed Votes: 0
Labels: upstream-fixed


 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

 Comments   
Comment by Sachin Setiya (Inactive) [ 2018-06-26 ]

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

Comment by Sachin Setiya (Inactive) [ 2018-07-23 ]

bb-5.5-15919

Comment by Andrei Elkin [ 2018-10-16 ]

Finished the current round to wait for the final patch.

Comment by Andrei Elkin [ 2018-10-29 ]

Fixed by

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

Generated at Thu Feb 08 08:25:00 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.