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


Hi! My name is Mattias Geniar. I'm a Support Manager at Nucleus Hosting in Belgium, a general web geek, public speaker and podcaster. If you're interested in keeping up with me, have a look at my podcast and weekly newsletter below. For more updates, follow me on Twitter as @mattiasgeniar.

SysCast podcast

In the SysCast podcast I talk about Linux & open source projects, interview sysadmins or developers and discuss web-related technologies. A show by and for geeks!

cron.weekly newsletter

A weekly newsletter - delivered every Sunday - for Linux sysadmins and open source users. It helps keeps you informed about open source projects, Linux guides & tutorials and the latest news.

Share this post

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

Comments

swede Thursday, June 4, 2009 at 13:39

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;

Reply


Matti Thursday, June 4, 2009 at 14:29

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

Reply


Craig Friday, December 17, 2010 at 17:56

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?

Reply


Matti Friday, December 17, 2010 at 19:18

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

Reply


ling Tuesday, January 17, 2012 at 11:12

I edit my.cnf only

lower_case_table_names=1

in section [mysqld]

then restart mysql, It work find.

Reply


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

Thanks a lots. it worked for me :)

Reply


Leave a Reply

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

Inbound links