I’ve been having some fun lately with multi-platform development. Personally, I develop on a Mac. However, I also have an Ubuntu workstation that runs a MySQL instance among other things that I can connect to at home. It’s a nice setup, since production is ultimately running on ubuntu server and I have always advocated developing on an environment as close to production as possible. (Our build server is Ubuntu as well) However, one of the other devs uses Windows and has MySQL installed on his Windows machine as a development environment. This can create interesting case-sensitivity problems. Anyone using the default Mac setup (HSF+) or Windows have case-insensitive file systems. (Although, I run a case-sensitive variant of HSF+ on my mac) This can be important if you are also running MySQL on that system, as described below:

In MySQL, databases correspond to directories within the data directory. Each table within a database corresponds to at least one file within the database directory (and possibly more, depending on the storage engine). Consequently, the case sensitivity of the underlying operating system plays a part in the case sensitivity of database and table names. This means database and table names are not case sensitive in Windows, and case sensitive in most varieties of Unix.

This can be especially interesting in Spring Batch. By default, SB uses all uppercase table names. An example being BATCH_JOB_EXECUTION. If for example, your windows colleague creates the tables in lower case on his Windows system, they will work fine for him/her, since the underlying file system isn’t case sensitive. However, if you take a data dump from his system and put it into your linux mysql instance, running batch jobs will result in the following error:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'dbmaster.BATCH_JOB_INSTANCE' doesn't exist

Or something similar. What could get even wackier is if you change the prefix of your Spring Batch tables. Setting the prefix to something like ‘awesome’, would result in SB querying for a table such as ‘awesome_JOB_EXECUTION’, which again, would work fine on Windows but blow up anywhere else. (Unless you happened to create the table in alternating caps)

There are some options, however. There is a property in MySQL called ‘lower_case_table_names’ that allows you to configure how MySQL deals with table name resolution:

• 0 - tables are stored on disk using the letter case defined in your create table statements and all checks are case sensitive. (This setting could be problematic on non-case sensitive systems)
• 1 - all table names are stored on disk as lowercase regardless of how you define them in your create statement, and any table names in queries will be converted to lower case
• 2 - table names are stored as defined in the create statement but mysql converts them to lowercase on lookup. Which obviously won’t make a difference on a case-insensitive system.

Note: lower_case_table_names was introduced in MySQL 4.0.17

On my ubuntu install of MySQL lower_case_table_names is set to 0, which I assume to be the default. However, you can check your system easily by entering: ‘show variables;’ into mysql.

On my local ubuntu instance I create a file called: “/etc/mysql/conf.d/lower_case_table_names.cnf”. The .cnf ending is important, as that is what my.cnf is setup to look for, at least in my setup. In the file is the following:

[mysqld]
lower_case_table_names=1

The [mysqld] is important as there are numerous engines which are configured in the same file. For example, you can all create settings for mysqldump specifically as well. Next time you bounce mysql, if you make another call to ‘show variables’ you should see the updated setting. After this change, even though Spring Batch is making SQL statements in all caps and the tables are actually stored in lower case, the queries will still work.

Amazon RDS

It’s worth noting that if you use Amazon’s RDS service that you must use their web console (or command line tools) to modify this setting. It’s under parameter groups. The following guide from amazon should help you: http://aws.amazon.com/articles/2935