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 4:14	Modified:	11 Nov 2006 13:07
Reporter:	Jeff Kilbride
Status:	Verified
Category:	Server: Replication	Severity:	S2 (Serious)
Version:	5.0.27	OS:	Linux (Linux, Mac OS X)
Assigned to:	Alexander Barkov	Target Version:
Triage:	Triaged: D2 (Serious)

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.