MySQL table name case sensitivity is causing the problem

 

Recently encountered a very strange small problem in the project. In the development process of self-test no problem, but after the test, testing at the same time a function times wrong, the log is:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table ‘testdb.Emp‘ doesn‘t exist

• 1

Then open the database client, found that the TestDB database has this table, it is really strange. Project local testing or no problem, tangled up for a long while to notice the code in the table name is Emp , and the database is emp . It turns out that the table name is case sensitive. Never cared about this question before.

Variable Lower_case_table_names

Why is there no problem with local development, but the test environment does not exist on the cue table? Because of the local Windows development environment, the default is case insensitive. While the testing environment of Linux is sensitive.
Local Windows We execute commands to see the MySQL system variables in our computer can be seen:

show variables like ‘%lower_case_table_names%‘;

• 1

Results:

A value of 1 indicates that the case is not sensitive, while the Linux environment of MySQL is 0.

modifying variable Lower_case_table_names

Why this problem occurs, mainly because the development and testing environment is inconsistent, which is sometimes the test environment is completely no problem, one to the production of a strange problem of one of the reasons. Therefore, in addition to the test colleagues to carefully test, it is best to local and test environment is consistent.
For the current problem, we directly modify the SQL in order Emp to emp solve the problem, but to fundamentally solve the problem, we also need to modify the MySQL environment variable, that is, the value of Lower_case_table_names to 0.
Modification Method:
1. Locate the installation location for MySQL
2. Locate the My.ini configuration file in the home directory
3. Add lower_case_table_names=0 under the [MYSQLD] node
4. Restart the MySQL service

These problems are most likely to occur when configured:
Where's Q:my.ini?
A: Usually under the installation path. I have been modifying the My.ini in the directory before, restarting or not being valid. After the internet for help, found that the configuration is not used, but in the task management, service right-click Properties, see the executable file specified in the file:

Q: Why does the service not start when the property is modified?
A: MySQL started successfully in one computer, but the other one never started. The initial determination is the operating system reason, one is win7 boot cannot. Another win10 can be modified to start and take effect. The final Win7 was set to 2 before it started successfully.
Reason:

Https://dev.mysql.com/doc/refman/5.6/en/identifier-case-sensitivity.html
Use lower_case_table_names=0 on Unix and lower_case_table_names=2 on Windows

Why can't windows take 0?

The official documentation gives the answer, which is plainly that the operating system does not support case-sensitive file systems.

You should not set Lower_case_table_names to 0 if is running MySQL on a system where the data directory is resides on a Case-insensitive file system (such as on Windows or OS X).

We can test it, build a file in D, name it test.txt , and build a folder called Test.txt . We get a bunch of hints that the operating system considers test and test as the same string:

We'll see the difference between 0 and 2 when we take a range of values below.

Value of variable Lower_case_table_names

The value range is three, 0, 1, 2, respectively.
1. Set 0 : The table name is stored in the SQL case that you write, uppercase and lowercase, and case sensitive when comparing.
2. Set 1 to: The table name is converted to lowercase and then stored to the hard disk, compared with case insensitive.
3. Set 2 : The table name is stored in the SQL case that you write, uppercase and lowercase, and the comparison is a uniform to lowercase comparison.

This option is not only suitable for case-sensitive table names, but also for database names and table aliases.

For more information, refer to the Official Document System Variables section:

Https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_lower_case_table_names

Article tags: mysql database

MySQL table name case sensitivity is causing the problem