I came across as interesting situation with MySQL that I had not come across before. Most people would not have come across this problem either as the majority of MySQL installs are either single server based. If there are multiple servers, then they are usually the same platform or very close eg. Linux on x86 etc.

The issue I came across was actually in respect to a MySQL Forums entry and this guy who was trying to replicate from Unix to Mac OS X. The problem was explained that the replication showed the status as normal, the read_master_pos_log and the exec_master_pos_log were all fine, and the relay logs were working. There was no errors showing and the replication seemed to be going through according to all system status reports. The issue was that no data was going in to the tables.

On initial examination of the replicate-do-db status shown to be working, there appeared to be no reason for this strange occurrence of data not being updated without any errors?!?

It was when I was trying to copy directly from a Linux server to my Mac OS X server on my notebook that I noticed it gave no errors, but the updates did not happen?!? On examining the queries being run, I tried them directly and still got no updates, however I did notice that the case had changed to what was on my Mac OS X machines. When I altered the case manually, the queries all worked!

On examining the MySQL manual for a possible explanation, it turns out that MySQL does not handle case quite to standard just yet, but tries to implement a workaround. This is done using the option lower_case_table_names in the my.cnf options file. This option controls how MySQL treats the table names for different operating system types.

As it turns out, the case-sensitivity in operating systems is not a simple binary solution. The requirement is how MySQL will store the filenames onto a file system that may or may not be case sensitive? By default, MySQL will take the following approach

• UNIX/Linux: Tables are treated as case-sensitive filenames as defined in the CREATE TABLE statement.
• MS Windows and Mac OS X: Tables are treated as lowercase filenames.

However, when it comes to comparing the table names in the SQL statement to literal filenames there is now a problem. The lower_case_table_names attempts to fix this by allowing the DBA to define how MySQL treats this situation. The three different settings for this option are:

• Value 0: Files are stored case-sensitive and lookups are the same way.
• Value 1: Files are converted to lowercase and lookups are case-sensitive.
• Value 2: Files are stored case-sensitive and converted to lowercase for lookup.

BEWARE! Playing around with these options can cause major problems with your table indexes. This becomes a problem when you start forcing option value zero onto machines such as Mac OS X or MS Windows which are case-insensitive.

SOLUTION: Apart from fixing such a complex arrangement of options, there is actually a very simple solution for managing tables in a heterogeneous datastore environment. Simply define a standard naming convention for your tables that use only lowercase names and then the lookups do not matter as they will be consistent on all platforms.