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.

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>

Advertisement

Why ads?

I'm glad you made it to this blogpost. I hope it helps solve your problem. So why then do I show ads on the site? Writing content, testing it and making sure the layout isn't totally b0rked takes time. A lot of time. The ads are a way to pay back a small portion of that time.

And as you know running a site costs (a bit of) money: the domain name, webhosting, time spent writing and updating content, ... So if you like the content of this blog, consider disabling your AdBlocker for this domain. Thanks!

Recent posts

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 this blog.