MySQL table names are case-sensitive depending on the filesystem of the server. e.g. insensitive on Windows & Mac HFS+, Case sensitive on Unix.
It means that if you have stored the table PIPPO (upper case) in your database, the select query:
select * from pippodoesn't work and it returns a message like ..."table pippo doesn't exist".
In order to prevent this problem you have to set set lower_case_table_names=1 in your /etc/mysql/my.cnf file. In this way the mysql server will store the table in the file system using lower case.
Here the steps I have followed:
- Chek the status of lower_case_table_names typing:
$ mysqladmin -uroot -p variables $ sudo gedit /etc/mysql/my.cnf- edit the file adding the entry lower_case_table_names=1 just under the group definition: [mysqld]
[mysqld]
#
# * Basic Settings
#
#
# * IMPORTANT
# If you make changes to these settings and your system uses apparmor, you may
# also need to also adjust /etc/apparmor.d/usr.sbin.mysqld.
#
lower_case_table_names=1
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/english
skip-external-locking - shutdown the mysqlserver:
$ mysqladmin -uroot -p shutdown - start the mysqlserver:
$ sudo mysqld & - Chek the new status of lower_case_table_names typing:
$ mysqladmin -uroot -p variables - Remember that you have to re store the tables in the database, the best way to do that is dropping your database and running the SQL script.
- Test if works running a query like
$ select * from pipposupposing you have stored PIPPO upper case
I ran this configuration with ubuntu 8.0.4, dell XPS1530, mysql 5-0
Enjoy with MySQL on linux.
