Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.2(EOL)
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