How to make MySQL table name case insensitive in Ubuntu?

Question

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?

score 61 · Answer 1 · edited Jun 30 '14 at 16:44

61

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

edited Jun 30 '14 at 16:44

mustaccio

25,896
22
57
72

answered Jun 30 '14 at 07:29

Rajneesh Sharma

611
5
3

4

You have to add this section if it is not available. Otherwise the restart will fail. – alexander Mar 26 '17 at 18:44
5

Unfortunately, this does not work with MySQL 8.0 anymore. It has to be set before initialization. – stackprotector Jul 23 '20 at 14:25
I have installed the mysql as a contener with podman. As it turned out it has Oracle Linux Server 8.8 and mysql 8.1.0. I needed to install vim (microdnf install -y vim) if I wanted a more powerful editor than cat and sed. But other answers suggest that it will be a pain. – Arpad Horvath Nov 10 '23 at 10:55

Gerold Broser · Answer 2 · 2020-12-08T23:34:35.710

14

If you change lower_case_table_names in a DB with existing tables Stack Overflow: MySQL > Table doesn't exist. But it does (or it should) can happen.

The comment to this answer helped me in this case:

I reverted the value, restarted the database, exported the tables, set the value back to 1, restarted the database, re-imported the tables and everything worked again.

edited Dec 08 '20 at 23:34

answered Mar 15 '17 at 00:26

Gerold Broser

241
3
6

2

Happened to me and I wasn't aware that I have a max heart rate of 197 bpm ;-) – Sal Dec 08 '20 at 18:13

score 6 · Answer 3 · edited Jun 29 '16 at 10:50

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

It was solved by changing my.cnf and adding

lower_case_table_names = 1

under the [mysqld] section

my.cnf can be found:

under LAMPP/XAMPP... :

/opt/lampp/etc/my.cnf
stand alone mysql server :

/etc/mysql/my.cnf

Afterwards restart MySQL server, and everything will be ok.

How to make MySQL table name case insensitive in Ubuntu?

3 Answers3

Linked