Analysis of problems caused by MySQL case sensitivity

MYSQL is case sensitive

Seeing the words is believing, seeing the title tells the content. Have you ever been tricked because MYSQL is case-sensitive?

I have read Alibaba Java Development Manual before, and saw in the MySql table creation specification:

[Mandatory] Table names and field names must use lowercase letters or numbers. They must not start with numbers or have only numbers between two underscores. It is very costly to modify the database field names, and since pre-release is not possible, the field names need to be carefully considered.

Note: MySQL is not case-sensitive in Windows, but is case-sensitive by default in Linux. Therefore, no uppercase letters are allowed in database names, table names, and field names to avoid unnecessary complications.

Positive example: aliyun_admin, rdc_config, level 3_name Negative example: AliyunAdmin, rdcConfig, level 3 name

If you have never actually encountered a similar problem, sometimes you may not be able to fully appreciate these regulations when you just read them dryly, and you may only understand them vaguely and memorize them by rote.

01 A story about letter sizes

I have been tinkering with a project recently. There have been no problems during development and testing on my own machine, but after deploying it to the Linux server, I found an error. The log information is roughly as follows:

MySQLSyntaxErrorException: Table 'kytu.tb_sutyHo' doesn't exist

There is a problem, which makes me a little depressed. Local development is fine, but it doesn't work when deployed to the server. There are ghosts...but don't panic. Looking at the error message, it is obvious that the tb_sutyHo table does not exist!

① So I calmly opened nv (navicat) to check if the table existed. It really existed. It was tb_sutyho in the database, but the h was lowercase.

② After checking the code, I found that the table name was actually written as tb_sutyHo, with the h being written as a capital H.

The problem was found. It turned out that I accidentally wrote the wrong table name when writing SQL. I solved it by changing the table name, and the functions are now normal. Normally, the story should end here, right? The problem has been found and fixed, everything is fine, and we can eat chicken later.

For me who doesn't know how to play PUBG, this is not the end. It is indeed important to find and solve problems, but it is more important to find the root cause of the problem, so that you can avoid such problems next time. As a programmer, don't fall into the same pit twice.

I was thinking about this question, why doesn't this error message appear in the local Windows environment? It has to wait until I deploy the server before it appears. What is the problem? (If you are familiar with MySQL size sensitivity, you can skip the following content.)

So I used a search engine and found that the case sensitivity of database and table names in MySQL is controlled by the parameter lower_case_table_names.

View in the local Window environment as follows:

mysql> show variables like '%case%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_file_system | ON |
| lower_case_table_names | 1 |
+------------------------+-------+

Check the following on the Linux server:

mysql> show variables like '%case%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
+------------------------+-------+

The difference can be seen from the above results, but I still don’t have a feeling for these two parameters and don’t know what they mean specifically.

When introducing lower_case_table_names, let's also talk about lower_case_file_system.

lowercasefile_system

This variable describes the case sensitivity of file names on the file system where the data directory resides. OFF means file names are case sensitive, ON means they are case insensitive. This variable is read-only because it reflects a file system property and setting it has no effect on the file system.

lowercasetable_names

This parameter is static and can be set to 0, 1, or 2.

0 -- Case sensitive. (Unix, Linux default) The created library table will be saved as is on disk. For example, create database TeSt; will create a TeSt directory, create table AbCCC ... will generate AbCCC.frm as is. The SQL statement will also be parsed as is.

1 -- Case insensitive. (Windows default) When creating a library table, MySQL converts all library table names to lowercase and stores them on disk. SQL statements will also convert the library and table names to lowercase. If you need to query the previously created Testtable (generate the Testtable.frm file), even if you execute select * from Testtable, it will be converted into select * from testtable, causing the error table to not exist.

2 -- Case insensitive (OS X default) The created library table will be saved as is on disk. However, the SQL statement converts the library table name to lowercase.

On Windows the default value is 1. On macOS, the default value is 2. On Linux, a value of 2 is not supported; the server forces the value to 0 instead.

On Windows, the default value is 1. On macOS, the default value is 2. The value 2 is not supported on Linux; the server forces the value to 0.

And the official website also prompts: If you run MySQL on a system where the data directory resides on a case-insensitive file system (such as Windows or macOS), you should not set lowercasetable_names to 0.

When I tried to set lower_case_table_names to 0 in my Windows 10 environment, the MySQL service could not be started and an error was reported when starting the service. The Windows system is not case sensitive, see the figure below:

Note: If you want to modify the value of lower_case_table_names, modify the my.ini configuration file in Windows and the my.cnf configuration file in Linux. You need to restart the service. You can find information online for specific operations.

02 Notes

Common adverse risks caused by modifying lowercasetable_names: If a library table containing uppercase letters is created when lower_case_table_names=0, it will not be found after changing to lower_case_table_names=1.

First set lower_case_table_names=0

CREATE TABLE `Student` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `name` varchar(25) NOT NULL,
 PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

show tables;
+----------------+
| Tables_in_aflyun |
+----------------+
| Student |
+----------------+

Then set lower_case_table_names=1 and execute the query. Regardless of whether the table name is uppercase or lowercase, it will prompt that the table does not exist.

mysql> select * from Student;
1146 - Table 'aflyun.Student' doesn't exist

mysql> select * from student;
1146 - Table 'aflyun.student' doesn't exist

Solution: If you want to set the default lower_case_tables_name from 0 to 1, you must first convert the existing library table names to lowercase.

For the case where only uppercase letters exist in the table name:

①. When lower_case_tables_name=0, execute rename table to lowercase.

②. Set lower_case_tables_name=1 and restart to take effect.

For the case where the library name contains uppercase letters:

①. When lower_case_tables_name=0, use mysqldump to export and delete the old database.

②. Set lower_case_tables_name=1 and restart to take effect.

③. Import data into the instance. At this time, the library names containing uppercase letters have been converted to lowercase.

03 Conclusion

With the experience of stepping into the pit, I have a deeper understanding of the Ali MySQL specification mentioned at the beginning. Different operating systems lead to inconsistent case sensitivity. When developing, we should follow the principle of case sensitivity, so that the developed program can be compatible with different operating systems. Therefore, it is recommended to set the value of lower_case_table_names to 0 in the development and testing environment, so as to strictly control the case sensitivity of the code during development and improve the compatibility and rigor of the code.