Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I have a OSX MySQL master with lower_case_table_names set to 2. I have just setup a Linux MySQL slave with lower_case_table_names=1.
I haven't started replication on the slave, but everything looks good at this point.
Will it cause any issues with replication?
According to the MySQL 5.0 Certification Study Guide
Chapter 32 Section 32.3.4, Pages 456,457 describe the Conditions for Binary Portability which bring out the following:
Binary portability is important if you want to take a binary backup that was made on one machine and use it on another machine that has a different architecture. For example, using a binary backup is one way to copy databases from one MySQL server to another.
For MyISAM, binary portability means that you can directly copy the files for a MyISAM table from one MySQL server to another on a different machine and the second server will be able to access the table.
For InnoDB, binary portability means that you can directly copy the tablespace files from a MySQL server on one machine to another server on a different machine and the second server will be able to access the tablespace. By default, all the InnoDB tables managed by a server are stored together in the tablespace, so portability of the tablespace is a function of whether all individual InnoDB tables are portable. If even one table is not portable, neither is the tablespace.
MyISAM tables and InnoDB tablespaces are binary portable from one host to another if two conditions are met:
- Both machines must use two's-complement integer arithmetic
- Both machines must use IEEE floating-point format or else the tables must contain no floating-point columns (FLOAT or DOUBLE)
In practice, those two conditions pose little restriction. Two's-complement integer arithmetic and IEEE floating-point format are the norm on modern hardware. A third condition for InnoDB binary portability is that you should use lowercase names for tables and databases. This is because InnoDB stores these names internally (in its data dictionary) in lowercase on Windows. Using lowercase names allows binary portability between Windows and Unix, to force the use of lowercase names, you can put the following lines in an option file:
[mysqld]
lower_case_table_names=1
If you configure InnoDB to use per-table tablespaces, the conditions for binary portability are extended to include the .ibd files for InnoDB tables as well. (The conditions for the shared tablespaces still appliy because it contains the data dictionary that stores information about all InnoDB tables.)
If conditions for binary portability are not satisfied, you can copy MyISAM or InnoDB tables from one server to another by dumping them using some text format (for example, with mysqldump) and reloading them into the destination server.
From this quotation, you can see that InnoDB's data dictionary is very case sensitive to table names. You are basically playing Russian Roulette by mixing tablename case sensitivity between Master and Slave.
I have discussed mixing cases between operating systems in MySQL :
