sabato, maggio 31, 2008

MySQL case sensitive in linux


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 pippo
doesn'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:

  1. Chek the status of lower_case_table_names typing: $ mysqladmin -uroot -p variables
  2. $ sudo gedit /etc/mysql/my.cnf
  3. 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

  4. shutdown the mysqlserver: $ mysqladmin -uroot -p shutdown
  5. start the mysqlserver: $ sudo mysqld &
  6. Chek the new status of lower_case_table_names typing: $ mysqladmin -uroot -p variables
  7. 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.
  8. Test if works running a query like $ select * from pippo supposing 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.

4 commenti:

Anonimo ha detto...

Thanks Enrico, you saved my lot of time. I was actually trying to get data using hibernate when this error hit me. The best part is that now I can query mysql using both upper-case and lower-case table name similar to windows / mac platform. Good :)

I just wanted to ask, if there is a catch for that settings; any issue that might occur with this setting. It would be good if you can also explain that so that the issue can be handled likewise.

Thanks again.

Anonimo ha detto...

Thank you very much!

Diego Benna

paskuale ha detto...

Grazie mille enrico, cercavo questa guida per riparare una applicazione installata x la prima volta su ubuntu (da windows)

tuxdna ha detto...

That worked well for me.

I had to additionally rename the UPPER_CASE.frm files to lower_case.frm.

Thanks!