I'm sensitive about my case.

Case Sensitivity in MySQL

Yesterday I spent more than an hour trying to get a MySQL export from a Windows machine to play nice with a Unix server. This problem was compounded by Windows hiding a configuration file from me. Luckily for you, I eventually solved my problem and decided to write another Mistakes story.

The problem I was having was that I was working with scripts that created tables with mixed-case names. I ran the scripts first on a local Windows development machine. Unfortunately, this local machine ignored the names it was given and converted everything to lower-case. This worked fine while I did several days worth of setup work, but then blew up in my face when I tried to move everything to a public testing site on a Unix server.

If you are an experienced developer, you may have a few thoughts at this point. The first thought might be, "Doesn't he know that you should always use lower-case for your table names?" I'll be honest, I didn't know that. I will definitely remember it in the future, but it didn't matter because it was not my code that I was working on. Also, before anyone starts thinking that I'm blaming this all on Microsoft, I'm not. I'm blaming part of it on Microsoft. The behavior I was seeing was correct based on my dev server settings. However, Windows made fixing that behavior difficult.

To cut to the chase, here is what I need to do. I needed my Windows machine to create, use and export table names in mixed-case. According to the page I linked to above, I needed to add the line below to the MySQL configuration file.

This is where the problems began.

First, I had to find the configuration file. So, I went to C:\WINDOWS\my.ini. After several unsuccessful tries I realized I was missing an important piece. My code needed to look more like this.

Unfortunately, no matter what I did, MySQL did not behave any differently. Eventually, I realized that this file only set the configuration for the MySQL command line tools. I had to find another file that was setting up the MySQL server.

There wasn't any file called C:\my.ini. So, next I checked the MySQL install. I was using XAMPP, so I checked C:\xampp\mysql\bin. At first glance, there didn't seem to be anything there either. Then, I noticed this teeny-tiny little thing:

The real name of that file was my.cnf and even though I had told Windows not to hide file extensions, it hid the extension anyway. Microsoft, in its infinite wisdom, had decided that this was a SpeedDial file, and wouldn't let me rename it or open it with any other program without jumping through some hoops.

Eventually, I did get control and I did manage to change this setting. However, in my research I did note some other things that you should be aware of if you try this:

• This setting will only work on file systems that are case-insensitive like Windows and normal OSX volumes, and it will have no effect on InnoDB tables.
• This will only work on tables that are created after you change the setting and restart the server. If you need mixed-case on existing tables, you may have to drop them entirely and then re-create them with the correct mixed-case.

As always, I hope this helped you from going through the same frustrations I did. Stay tuned for more exciting adventures.