Naming Conventions Matter – Case Sensitivity in MYSQL and File Systems

I have been in the process of transferring a PHP + MYSQL site built on CodeIgniter from a Windows server to a Linux server and have been dealing case sensitivity problems due to the CamelCase naming convention that was used in parts of the website. This post discusses different options that you should be aware of when deciding on naming conventions if you plan on making your application compatible with both Linux and Windows.

Naming Conventions Matter

MYSQL Case Sensitivity

The first question that popped into my head when I found that MYSQL forced different case sensitivity standards on different filesystems was why don’t they force a standard? The answer lies within the documentation MYSQL identifier case sensitivity documentation, but here it is in short:

MYSQL lower_case_table_names configuration parameter:

Value Default On Stored Using Name Comparisons Comments
Unix Lettercase Specified in “Create Table” or “Create Database” statement Case Sensitive Reference with multiple cases on Case-Insensitive filesystems can cause corruption in MyISAM tables.
1 Windows Converted to Lowercase Converted to Lowercase Works on all file systems, however, case will not be preserved in Database and Table names.
2 Mac OS X Lettercase Specified in “Create Table” or “Create Database” statement Converted to Lowercase Only works on case-insensitive file systems

If you want a configuration that works on Windows and Linux without worrying about case sensitivity, set the parameter “lower_case_table_names” to “1” on both Linux and Windows (it should be 1 by default on Windows). All Database and Table names will be stored as lowercase if you use this option.

If you want a configuration that works on Windows and Linux that preserves case, set the parameter “lower_case_table_names” to “0” on Linux (should be default) and set it to “2” on Windows. Note that if you deviate from the case your Database or Table is setup using that it will work on Windows, but not on Unix.

File System Case Sensitivity – Apache Can Help (kind of)

Everybody knows that Windows is Case Insensitive and Linux is Case Sensitive. But Apache’s mod_speling (cleverly spelled wrong on purpose) is here to help!

  1. Install mod_speling
  2. Enable mod_speling
  3. Add to Server Config or Virtual Host: “CheckCaseOnly on”
  4. (view documentation for more options)

CodeIgniter Case Sensitivity

The only problem with this is that CodeIgniter loads the “index.php” file, and PHP loads the proper files and classes based of the URI. Apache cannot fix this spelling, because Apache has found Codeigniter’s “index.php” file and served it properly. Here are some possilbe fixes:

  • Hack Router.php and write file_exists() functionality using a combination of case conversions such as strtolower() to try to find the proper file when searching for the Controller
  • If all of your files are lowercase, have Apache’s mod_rewrite enforce lowercase URIs
  • Make sure all of your links use the proper case

How Does This Affect Naming Conventions?

Underscore: If you want to avoid problems with case altogether than the underscore naming convention (underscore_naming_convention) is a great route to go. Plus, if you are using CodeIgniter, they list underscore as their naming convention in their style guide.

CamelCase: The CamelCase (or camelCase) naming convention is also very common, especially for Java programmers, but will certainly cause all case issues across Windows and Linux. CamelCase is especially dangerous if developing on Windows because you will not get errors until you switch over to a case-sensitive file system. If you decide to go with CamelCase, consider developing on Linux.

Stick With It: Once you choose and implement a naming convention for an application, make sure to stick with it! If there are multiple programmers working on your application, make sure they all know the standard and use it. Use the same naming convention in your file names, PHP (or whatever) Classes, and MYSQL Databases/Tables/Fields for extra simplicity.