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.
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;
@Swede; thanks for noticing, I’ve updated the post!
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?
@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?
I edit my.cnf only
lower_case_table_names=1
in section [mysqld]
then restart mysql, It work find.
Thanks a lots. it worked for me :)