Case Insensitive Table And Column Names In MySQL

While you should always uphold the case-sensitive tablenames, it can be troublesome when migrating from a host that had this option enabled (table & column names become case insensitive), to a host that doesn't have this option -- so you suddenly find yourself "stuck" with case sensitive table and column names. For instance when migrating from a Windows environment, to a Linux environment.

Table 'database.TableName' doesn't exist (because the table "tablename" exists, without capitals)

To solve this, edit your /etc/my.cnf file and add the following line:

set-variable = lower_case_table_names=1

Restart your MySQLd.

service mysqld restart
or
/etc/init.d/mysqld restart

There are some serious consequences to this change, which are best explained on this page; MySQL case insensitive table and column names. And if you're thinking of using this in combination with Plesk's Control Panel; don't. You'll break Plesk's functionality, and receive continuous notices that the server's SMTP server is down, even though it's not. This is because Plesk performs its checks using a case sensitive database, and those checks fail once you revert to a case insensitive system.

Write a Comment

Do you care about the markup if your comment? You can use the following HTML tags:

<code>command</code>: command highlighting
<pre>text</pre>: pre-formatted code, can be multi-line (black background, white letters)

example <pre> tag
<blockquote>text</blockquote> quoted text
quoted example


None of this is needed of course, it's all optional!

Comment

*

  1. tried this and mysql wouldn’t start.
    log says :
    Unknown suffix ‘;’ used for variable ‘lower_case_table_names’ (value ‘1;’)
    090604 9:11:55 [ERROR] /usr/libexec/mysqld: Error while setting value ‘1;’ to ‘lower_case_table_names’

    looks like the ‘;’ shouldn’t be there….
    set-variable = lower_case_table_names=1;

  2. I get nothing when I insert the following line into /etc/mysql/my.cnf:
    set-variable = lower_case_table_names=1

    So I tried:
    set-variable = lower_case_table_names=1;

    and I also get nothing.

    I tried putting it in a different location such as /etc/my.cnf – to no avail. Any ideas?

  3. @Craig: did you restart MySQL after the change?
    Are you certain that ‘s the my.cnf that is actually used? That last one (with semi-colon), should give a syntax error when you restart MySQL.

    What version of MySQL are you running?