How to change MySQL table names in Linux server to be case insensitive?

Question

I'm working on an old website that used to be hosted on an Apple server. When it was migrated into a new Linux server it stopped working. I'm pretty sure it's because all the MySQL queries used in the php scripts have different case combinations for the table names (I don't know why the original developers didn't follow any conventions when they created the table names or the php scripts) and it didn't matter because both Mac and Windows MySQL servers are case insensitive by default when it comes to this. However, Linux is not.

Is there a way to change the Linux default on MySQL so it becomes case insensitive and it works like Mac or Windows? I've been looking but haven't found any answers that don't involve changing either the scripts or the table names or both. The website must have been generated using some CMS so there are dozens upon dozens of pages and include files with multiple queries in each and hundreds of tables. I began trying to implement this type of solution in the smartest way I could think of but if I touch the table names then other currently working pages stop working (I'm trying to avoid breaking the site further).

There was a system variable (lower_case_table_names) in the MySQL Server console in Webmin in the Linux server that I read could be changed from 0 to 1 to tackle this issue, but Webmin won't let me change it because it's a "read-only" variable.

You'd think this would be an easily problem to solve, but so far I'm losing hope. I'm hoping someone's got an answer that maybe eludes me at the moment.

Answer 1

This happen because of the file system you are using. This is explained as

In MySQL, databases correspond to directories within the data directory.
Each table within a database corresponds to at least one file within the database directory (and possibly more, depending on the storage engine). 
Triggers also correspond to files. Consequently, the case sensitivity of the underlying operating system plays a part in the case sensitivity of database, table, and trigger names. 
This means such names are not case-sensitive in Windows, but are case-sensitive in most varieties of Unix. 
One notable exception is macOS, which is Unix-based but uses a default file system type (HFS+) that is not case-sensitive. 
However, macOS also supports UFS volumes, which are case-sensitive just as on any Unix.

You can read more here at mysql official site

You must check the case sensitivity by running the below query to know case sensitivity of your database :

SHOW VARIABLES LIKE "lower_case_table_names";

the value 0 (Default on Linux ) means name comparisons are case-sensitive, value 1 (Default on Windows) means case insensitive while value 2 (default on macos) can also be considered as case insensitive.

Now, if the result of query is 0, then you need to change lower_case_table_names variable to make your database case insensitive.

Do remember to take backup of your database, as post changing the value of this variable your database may not behave well. You may restore the database post making it case insensitive. Use below command to take backup of your database (and should delete it after the backup) :

mysqldump -u username -p db_name > dump.sql

Now, you need to locate your mysql configuration file. The configuration file may be available at

/etc/mysql/my.cnf

or at

/etc/mysql/mysql.conf.d/mysqld.cnf

You need to look for [mysqld], whichever file you find details like(either one of them):

[mysqld]
user            = mysql
pid-file        = /***
socket          = /***
port            = ****
basedir         = /***
datadir         = /***
tmpdir          = /tmp
lc-messages-dir = /****

Edit your file:

vi /etc/mysql/mysql.conf.d/mysqld.cnf

and paste the line lower_case_table_names=1 below [mysqld]

You need to restart mysql as :

sudo service mysql restart

You can restore your database now using the below command:

mysql -u username -p db_name < dump.sql

Done. Cheers.

Answer 2

Edit the mysql configuration file

nano /etc/mysql/my.cnf

or any other my.cnf file which is used to configure your mysql.

Under

[mysqld]

add the line

lower_case_table_names=1

Run

sudo service mysqld restart

You might want to reimport your windows database into your linux database. Preferably from scratch, with add table and insert statements.

It's good!

Answer 3

The lower_case_table_names parameter should be set as part of a custom DB parameter group before creating a DB instance. You should avoid changing the lower_case_table_names parameter for existing database instances because doing so could cause inconsistencies with point-in-time recovery backups and Read Replica DB instances.

Answer 4

There is a MySQL server variable with the same name. You probably need to set that specific variable on system start-up, as described in this help page. You will have to find the location of the MySQL options file (mine is at /etc/my.cnf) for your DB server instance and edit/add this option to the [mysqld] section.

Don't forget to restart the MySQL daemon afterwards...

Answer 5

MySQL's case sensitivity is by default handled by the file system, which is why you found this difference:

9.2.2. Identifier Case Sensitivity

In MySQL, databases correspond to directories within the data directory. 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. One notable exception is Mac OS X, which is Unix-based but uses a default file system type (HFS+) that is not case sensitive. However, Mac OS X also supports UFS volumes, which are case sensitive just as on any Unix. See Section 1.8.4, “MySQL Extensions to Standard SQL”.

Fortunately, the next sentence could help you:

The lower_case_table_names system variable also affects how the server handles identifier case sensitivity, as described later in this section.

The lower_case_table_names blurb:

If set to 0, table names are stored as specified and comparisons are case sensitive. If set to 1, table names are stored in lowercase on disk and comparisons are not case sensitive. If set to 2, table names are stored as given but compared in lowercase. This option also applies to database names and table aliases. For additional information, see Section 9.2.2, “Identifier Case Sensitivity”.

You should not set this variable to 0 if you are running MySQL on a system that has case-insensitive file names (such as Windows or Mac OS X). If you set this variable to 0 on such a system and access MyISAM tablenames using different lettercases, index corruption may result. On Windows the default value is 1. On Mac OS X, the default value is 2.

So it appears you should set lower_case_table_names to 1 in the MySQL config file.

Answer 6

You can't change the value of lower_case_table_names while mysql is running - it needs to be set on startup. You will need to edit my.cnf (maybe in /etc, maybe somewhere else, not sure). Then restart mysql and you should be good.