Bug #3454 lower_case_table)names with InnoDB and replication
Submitted: 13 Apr 2004 4:42 Modified: 21 Apr 2004 14:03
Reporter: Bruce Dembecki Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S1 (Critical)
Version:4.0.19 OS:MacOS (OS X)
Assigned to: CPU Architecture:Any

[13 Apr 2004 4:42] Bruce Dembecki
Description:
Hi! We already went through most of this with BugĀ #2994 lower_case_table_names=2 is not sensible for 
InnoDB... After Monty graciously fixed it for us I adventured out and compiled a version of 4.0.19 for us 
to use. Setting lower_case_table_names=0 worked almost wonderfully for us. In terms of turning it on 
and using our databases, applications etc we are fine.

However we now have an issue inserting new tables of type InnoDB. When we add a new table it works 
fine on the local machine, in mixed case. When it replicates to the backup it is added in lower case. This 
with both machines set to lower_case_table_names=0.

That slips by unoticed until the replication process starts to populate the new tables... replication stops 
because InnoDB can't find the table. This was a new database for us, the only way I found around the 
issue was to drop the database (which is replicated back to the original server and everything is gone), 
then add replicate-ignore-db=new_database to our my.cnf and fire it up... The stuff for the new 
database is ignored, and we are back in production.

Clearly however, we need to be able to add new databases and tables and have them replicate in the 
manner one would expect.

How to repeat:
In OSX replicate a new database with mixed case table names in InnoDB and 
lower_case_table_names=0, then add data using queries specifying the mixed case table name. The 
table should have been created under InnoDB but on the replication slave it has lower case table names, 
and the incoming mixed case queries generate an InnoDB error.
[16 Apr 2004 11:44] Bruce Dembecki
Go ahead and close this, I did some messing around and it is no longer reproduceable.

Thanks.