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.

Looking for help?

Tired of fixing all these tech-problems yourself? We've got an excellent team at Nucleus, a top-class Belgian hosting provider, that can help you. Discover our Managed Hosting, where skilled engineers manage your servers and keep them up-to-date, so you can focus on your core business. We use a variety of Configuration Management Systems such as Puppet to make sure every config is reviewed, unit-tested and guaranteed to be working.

Want to get in touch? Find me as @mattiasgeniar on Twitter or via the contact-page on my blog.

Tagged with: , ,
Posted in Technology
6 comments on “Case Insensitive Table And Column Names In MySQL
  1. swede says:

    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. Matti says:

    @Swede; thanks for noticing, I’ve updated the post!

  3. Craig says:

    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?

  4. Matti says:

    @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?

  5. ling says:

    I edit my.cnf only

    lower_case_table_names=1

    in section [mysqld]

    then restart mysql, It work find.

  6. Vijay Akula says:

    Thanks a lots. it worked for me :)

1 Pings/Trackbacks for "Case Insensitive Table And Column Names In MySQL"
  1. [...] to this blog post on ImprovedNamingStrategy for pointing the way. This post also helped me find the [...]

Leave a Reply

Your email address will not be published. Required fields are marked *

*

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>