Something notable about MySQL case sensitivity that my project team has encountered last weekend. On Windows and Mac OS X, all object identifiers (such as names of database, table, column, index and etc) are case insensitive.
So, even if someone doesn’t practice consistency of identifier naming on Windows and Mac OS X, you will not encounter table 'tbl_name' doesn't exist error, as long as spelling is correct.
However, if you have a MySQL table named as PRODUCT_ITEMS, you will encounter table 'product_items' doesn't exist error on most varieties of Unix and Linux, when the following select statement is executed.
select * from product_items;
Such a simple select statement isn’t it? But will fail in Unix and Linux as database names, table names and table aliases are case sensitive by default.
Solution 1: Ensure Identifier Naming Consistency
The most elegant solution, is of course to ensure identifier naming consistency in database and codes. But if you’re working on a mammoth project, this is not the quickest solution though.
Solution 2: Alter lower_case_table_names System Variable
The quickest solution in this case, is to alter the database default behavior, such that table names are always treated as case insensitive.
By default, lower_case_table_names is set to ‘0′ in Unix. So, you can do the following to alter the lower_case_table_names variable.
- Ensure that the database name and all existing table names are lowercase.
- Open to edit /etc/my.cnf file
- Add “lower_case__table_names=1” (without quote) into /etc/my.cnf
- Restart MySQL
After restart, the lower_case_table_names variable of your MySQL database should appear as ‘1‘. So, regardless of the case of identifier naming in your codes, all should work like case sensitivity is never an issue at all.
Refer to http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html for further information.
Related Posts:
0 Responses to “MySQL Table Names could be Case Sensitive in Unix”