Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am using Ubuntu 13.10 and MySQL 5.6 and I know database name and table name are case sensitive in Ubuntu (and some other *nix environments) by default.

Now, I want to make MySQL work as case insensitive in Ubuntu.

Is it possible? If yes, how can I do it?

share|improve this question

migrated from stackoverflow.com Feb 22 '14 at 9:31

This question came from our site for professional and enthusiast programmers.

Open terminal and edit /etc/mysql/my.cnf

sudo nano /etc/mysql/my.cnf

Underneath the [mysqld] section.add:

lower_case_table_names = 1

Restart mysql

sudo /etc/init.d/mysql restart

Then check it here:

mysqladmin -u root -p variables
share|improve this answer

From here: Case insensitive table names

It says:

So, the final recommendation is to leave the lower_case_table_names variable unaltered (i.e. its default value), which is 0 on Linux-based systems and 2 on Windows and write table names to a lettercase that suits you best (both at creation as well as at query time).

If you are moving from Windows to Linux, then set it to 2.

Case insensitive table names

In MySQL, databases correspond to directories within the data directory (default is /var/lib/mysql. Each table within a database corresponds to at least one file within the database directory (and possibly more, depending on the storage engine). Consequently, the case sensitivity of the underlying operating system plays a part in the case sensitivity of database and table names. This means database and table names are not case sensitive in Windows, and case sensitive in most varieties of Unix or Linux.

How table and database names are stored on disk and used in MySQL is affected by the lower_case_table_names system variable which can be set in the config when starting up your MySQL server. Possible values and their meaning are presented in the table below:

Value Description 0 Table and database names are stored on disk using the lettercase specified in the CREATE TABLE or CREATE DATABASE statement. Name comparisons are case sensitive. Note that if you force this variable to 0 with --lower-case-table-names=0 on a case-insensitive filesystem and access MyISAM tablenames using different lettercases, index corruption may result. 1 Table names are stored in lowercase on disk and name comparisons are not case sensitive. MySQL converts all table names to lowercase on storage and lookup. This behavior also applies to database names and table aliases. 2 Table and database names are stored on disk using the lettercase specified in the CREATE TABLE or CREATE DATABASE statement, but MySQL converts them to lowercase on lookup. Name comparisons are not case sensitive. This works only on filesystems that are not case sensitive! InnoDB table names are stored in lowercase, as for lower_case_table_names=1. Thus, returning to our problem, i.e. having case insensitive table names in MySQL under Linux, the bad news is ... you cannot. The closest solution to this would be to set lower_case_table_names=1, which would make all your tables lowercase, no matter how you write them. The main disadvantage however is that when you use SHOW TABLES or SHOW DATABASES, you do not see the names in their original lettercase, but always in lowercase.I f you plan to set the lower_case_table_names system variable to 1 on Linux, you must first convert your old database and table names to lowercase before stopping mysqld and restarting it with the new variable setting.

So, the final recommendation is to leave the lower_case_table_names variable unaltered (i.e. its default value), which is 0 on Linux-based systems and 2 on Windows and write table names to a lettercase that suits you best (both at creation as well as at query time).

share|improve this answer

This problem was causing pain for me, where Doctrine generated capital/CamelCase table names and mysql stored them as small letter!

Solved by changing my.cnf and adding

lower_case_table_names = 1

under [mysqlid] section

my.cnf path:

under LAMPP/XAMPP... :

/opt/lampp/etc/my.cnf

stand alone mysql server :

/etc/mysql/my.cnf

restart mysql server, and everything will be ok!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.