MySQL Bugs: #23997: Replication fails between Linux master and OS X slave using mixed case names

Bug #23997	Replication fails between Linux master and OS X slave using mixed case names
Submitted:	6 Nov 2006 3:14	Modified:	10 Jan 2013 11:21
Reporter:	Jeff Kilbride	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.0.27	OS:	Linux (Linux, Mac OS X)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Replication between a Linux master and Mac OS X (10.4.7) slave, with mixed case database/table names, fails after a replicated table is opened locally on the slave. Both machines are setup with default case settings:

[master]
mysql> show variables like '%case%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | OFF   | 
| lower_case_table_names | 0     | 
+------------------------+-------+
2 rows in set (0.00 sec)

[slave]
mysql> show variables like '%case%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | ON    | 
| lower_case_table_names | 2     | 
+------------------------+-------+
2 rows in set (0.00 sec)

How to repeat:
Using 5.0.27 on both machines, create a mixed case database and table on the master. Slave was started with skip-slave-start option:

[MASTER]
mysql> create database TestMe;
Query OK, 1 row affected (0.00 sec)

mysql> use TestMe
Database changed
mysql> create table TestNumberOne (
    -> TestID int not null,
    -> TestChar varchar(10) not null
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> show tables;
+------------------+
| Tables_in_TestMe |
+------------------+
| TestNumberOne    | 
+------------------+
1 row in set (0.00 sec)

[SLAVE]
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| mysql              | 
+--------------------+
2 rows in set (0.00 sec)

mysql> slave start;
Query OK, 0 rows affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| TestMe             | 
| mysql              | 
+--------------------+
3 rows in set (0.00 sec)

mysql> use TestMe
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+------------------+
| Tables_in_testme |
+------------------+
| TestNumberOne    | 
+------------------+
1 row in set (0.00 sec)

Use a script to populate the TestNumberOne table on the master. I am attaching the perl script that I used. After first execution of the script on the master, everything seems okay:

[MASTER]
mysql> select * from TestNumberOne;
+--------+------------+
| TestID | TestChar   |
+--------+------------+
|      0 | abcdefghij | 
|      1 | abcdefghij | 
|      2 | abcdefghij | 

  (cut to save space)

|     18 | abcdefghij | 
|     19 | abcdefghij | 
+--------+------------+
20 rows in set (0.00 sec)

[SLAVE]
mysql> select * from TestNumberOne;
+--------+------------+
| TestID | TestChar   |
+--------+------------+
|      0 | abcdefghij | 
|      1 | abcdefghij | 
|      2 | abcdefghij | 

  (cut to save space)

|     18 | abcdefghij | 
|     19 | abcdefghij | 
+--------+------------+
20 rows in set (0.00 sec)

After second execution of the script on the master, we see the problem:

[master]
mysql> select * from TestNumberOne;
+--------+------------+
| TestID | TestChar   |
+--------+------------+
|      0 | abcdefghij | 
|      1 | abcdefghij | 
|      2 | abcdefghij | 

  (cut to save space)

|     18 | abcdefghij | 
|     19 | abcdefghij | 
|      0 | abcdefghij | 
|      1 | abcdefghij | 
|      2 | abcdefghij | 

  (cut to save space)

|     18 | abcdefghij | 
|     19 | abcdefghij | 
+--------+------------+
40 rows in set (0.00 sec)  <----------***

[SLAVE]
mysql> select * from TestNumberOne;
+--------+------------+
| TestID | TestChar   |
+--------+------------+
|      0 | abcdefghij | 
|      1 | abcdefghij | 
|      2 | abcdefghij | 

  (cut to save space)

|     18 | abcdefghij | 
|     19 | abcdefghij | 
+--------+------------+
20 rows in set (0.00 sec)  <----------***

The slave did not replicate the second round of data and there are no errors listed in 'show slave status' or the error log. After looking around a bit, I noticed the following on the slave:

[SLAVE]
mysql> show open tables;
+----------+---------------+--------+-------------+
| Database | Table         | In_use | Name_locked |
+----------+---------------+--------+-------------+
| TestMe   | testnumberone |      0 |           0 | 
| testme   | testnumberone |      0 |           0 | 
+----------+---------------+--------+-------------+
2 rows in set (0.00 sec)

There seems to be a conflict between the table opened via replication and the table opened locally on the slave via the command line client. Issuing a FLUSH TABLES seems to alleviate the problem and the slave immediately replicates the missing data:

[SLAVE]
mysql> flush tables;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from TestNumberOne;
+--------+------------+
| TestID | TestChar   |
+--------+------------+
|      0 | abcdefghij | 
|      1 | abcdefghij | 
|      2 | abcdefghij | 

  (cut to save space)

|     18 | abcdefghij | 
|     19 | abcdefghij | 
|      0 | abcdefghij | 
|      1 | abcdefghij | 
|      2 | abcdefghij | 

  (cut to save space)

|     18 | abcdefghij | 
|     19 | abcdefghij | 
+--------+------------+
40 rows in set (0.00 sec)  <----------***

After more testing, this is what I have seen:

1) Replication seems to work fine, until a replicated table is opened locally on the slave. From that point forward, no data is replicated and no errors are reported.

2) Issuing a FLUSH TABLES on the slave resets the condition -- replication starts working again until the replicated table is opened again locally on the slave.

Suggested fix:
No suggestions...

Script for populating test tables

Attachment: test_table.pl (text/plain), 480 bytes.

With further testing, I have confirmed that using an all lower case database name when creating the database on the master avoids this problem. In this case, show open tables only shows one instance of the replicated db/table:

mysql> show open tables;
+----------+---------------+--------+-------------+
| Database | Table         | In_use | Name_locked |
+----------+---------------+--------+-------------+
| testme   | testnumberone |      0 |           0 | 
+----------+---------------+--------+-------------+
1 row in set (0.00 sec)

This should prominent in the documentation until this problem is resolved.

Thanks for bug report! 

Verified at 5.0-bk, 5.1-bk. 

The problem: though table names are lowercased, database names are not, hence ending up with dupe file descriptors. 

+----------+-------------+--------+-------------+
| Database | Table       | In_use | Name_locked |
+----------+-------------+--------+-------------+
| test     | reptest     |      0 |           0 | 
| mysql    | slow_log    |      1 |           0 | 
| mysql    | general_log |      1 |           0 | 
| MuHaHa   | testme      |      0 |           0 | 
| muhaha   | testme      |      0 |           0 | 
+----------+-------------+--------+-------------+

Attempted mysql-test test case

Attachment: rpl_name_case.test (application/octet-stream, text), 2.15 KiB.

Results with --lower-case-table-names=1

Attachment: rpl_name_case.log.opt1 (application/octet-stream, text), 1.03 KiB.

Results with --lower-case-table-names=2

Attachment: rpl_name_case.log.opt2 (application/octet-stream, text), 1.68 KiB.

slave option file

Attachment: rpl_name_case-slave.opt (application/octet-stream, text), 28 bytes.

Any solution should take into consideration a solution for #22166 and 27425.

Table A gets dropped, but never gets replicated to the slave. Later the master has the table reappear. On replicating the data hits the wrong object.

Tables should be allowed to have different definitions, but we should know when the "objects" differ by creation.

The reported problem (Linux-Mac) was not reproducible hence the bug is closed
with status "can't repeat". But a similar problem was observed between (Linux-Windows) a new internal issue has been reported to track the that.