Case Insensitive Table And Column Names In MySQL

Mattias Geniar, Wednesday, April 15, 2009 - last modified: Sunday, February 19, 2012

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
/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.

Hi! My name is Mattias Geniar. I'm a Support Manager at Nucleus Hosting in Belgium, a general web geek & public speaker. Currently working on DNS Spy & Oh Dear!. Follow me on Twitter as @mattiasgeniar.

Share this post

Did you like this post? Will you help me share it on social media? Thanks!


swede Thursday, June 4, 2009 at 13:39 - Reply

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;

Matti Thursday, June 4, 2009 at 14:29 - Reply

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

Craig Friday, December 17, 2010 at 17:56 - Reply

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?

Matti Friday, December 17, 2010 at 19:18 - Reply

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

ling Tuesday, January 17, 2012 at 11:12 - Reply

I edit my.cnf only


in section [mysqld]

then restart mysql, It work find.

Vijay Akula Wednesday, September 12, 2012 at 21:22 - Reply

Thanks a lots. it worked for me :)

Leave a Reply

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

Inbound links