How to enforce case-sensitive table and column names in MySql?

Question

The thing is, we are working on a Windows machine, and one day we deploy code on a Unix machine. The code works fine on windows, but then on unix we get an error like "no such table exists" after changing the table name in the right case, it works fine on unix as well. In fact, windows do not have case-sensitive table names by default, but they do have them on unix (read that MySQL tables are actually files, and unix has a case-sensitive file name, but not on windows). A workaround may be to re-create all the tables and allow the table name to be lowercase. Yes, we can do it too, that's great.

But we can still impose case sensitivity on table names in MySql (a Windows machine). If so, please let me know how to do this.

Answer 1

This setting is called lower_case_table_names . If you set the value to 0 , the comparison will be case sensitive.

However

You should not set this variable to 0 if you are using MySQL on a case-insensitive name system (such as Windows or Mac OS X). If you force this variable to 0 with --lower-case-table-names = 0 on a case-insensitive file system and gain access to the MyISAM mascots using different letters, this can damage the index.

make all table names lowercase on all systems (including Linux), i.e. setting it to 1 sounds like the best option:

Table names are stored in lower case on disk, and name mappings are not case sensitive. MySQL converts all table names to lowercase when stored and searched. This behavior also applies to database names and table aliases.

Answer 2

On Unix, the default value of lower_case_table_names is 0. On Windows, the default value is 1. On Mac OS X, the default value is 1 through MySQL 4.0.18 and 2 since 4.0.18.

To fix this, you can find the parameter: lower_case_table_names in the my.ini file found in or around: C: \ Program Files \ MySQL \ MySQL Server 4.1, depending on the version you are running on. If you do not find this parameter, you can simply add it to the end of the my.ini file, as I did, for example:

 lower_case_table_names=0 

Remember to restart the MySQL service before checking to see if it works.

If you use MySQL on only one platform, you usually do not need to change the value of the lower_case_table_names variable from the default value. However, you may encounter difficulties if you want to transfer tables between platforms that are case sensitive in the file system. For example, on Unix, you can have two different tables named my_table and MY_TABLE, but on Windows these names are considered identical. To avoid data transfer problems arising from the alphabetic designation of database names or tables, you have two options:

Use lower_case_table_names = 1 for all systems. The main disadvantage of this is that when you use SHOW TABLES or SHOW DATABASES, you do not see the names in their original booklet.

Use lower_case_table_names = 0 on Unix and lower_case_table_names = 2 on Windows. This preserves the case of letters of database and table names. The disadvantage of this is that you have to make sure that your statements always refer to your databases and table names with the correct letter in Windows. If you submit your statements to Unix, where the case of letters is significant, they do not work if the letter format is incorrect.

Exception: if you use InnoDB tables and try to avoid these data transfer problems, you must set lower_case_table_names to 1 on all platforms to force names to be lowercase.

If you plan to set the lower_case_table_names system variable to 1 on Unix, you must first convert the old database and table names to lower case before stopping mysqld and restarting it with the new variable setting.

For more information about this, visit the MySQL website and some important warnings http://dev.mysql.com/doc/refman/4.1/en/identifier-case-sensitivity.html

Answer 3

You can put the code in JPA to convert all your physical names, schema, sequence and table names to lowercase and use case on both systems so that the database can be exported from Windows and imported to Unix without any problems.

Add this class somewhere:

 public class ImprovedNamingStrategy implements PhysicalNamingStrategy { @Override public Identifier toPhysicalCatalogName(Identifier identifier, JdbcEnvironment jdbcEnv) { return convert(identifier); } @Override public Identifier toPhysicalColumnName(Identifier identifier, JdbcEnvironment jdbcEnv) { return convert(identifier); } @Override public Identifier toPhysicalSchemaName(Identifier identifier, JdbcEnvironment jdbcEnv) { return convert(identifier); } @Override public Identifier toPhysicalSequenceName(Identifier identifier, JdbcEnvironment jdbcEnv) { return convert(identifier); } @Override public Identifier toPhysicalTableName(Identifier identifier, JdbcEnvironment jdbcEnv) { return convert(identifier); } /** * Converts table name. * * @param identifier * the identifier. * @return the identifier. */ private Identifier convert(Identifier identifier) { if (identifier == null || identifier.getText().trim().isEmpty()) { return identifier; } return Identifier.toIdentifier(identifier.getText().toLowerCase()); } } 

and add this property to you persistence.xml

 <property name="hibernate.physical_naming_strategy" value="ImprovedNamingStrategy" /> 

Answer 4

(This answer does not give you a clear solution to the problem, but gives you a deeper understanding.)

Mac OS has other considerations than Windows:

9.2.2 Identity case sensitivity In MySQL, databases correspond to directories in the data directory. Each table in the database corresponds to at least one file in the database directory (and, possibly, to a larger number, depending on the storage mechanism). Triggers also correspond to files. Therefore, the case sensitivity of the underlying operating system plays a role in the case sensitivity of database names, tables, and triggers. This means that such names are not case sensitive on Windows, but case sensitive on most varieties of Unix. One notable exception is macOS, which is based on Unix, but uses the default file system type (HFS +), which is not case sensitive. However, macOS also supports UFS volumes that are case sensitive, as with any Unix. See Section 1.8.1, “MySQL Extensions for Standard SQL”. The system variable lower_case_table_names also affects how the server handles case sensitivity for identifiers, as described later in this section.

- https://dev.mysql.com/doc/refman/8.0/en/identifier-case-sensitivity.html

From some error reports:

An error of fully converting table names to lower case led to errors later when trying to rename tables. (especially partitioned tables)

Table names were not compared in lower case on macOS with the parameter lower_case_table_names = 2 , which caused instability after the server restarted. (Error No. 28170699, Error No. 91204)

Important Note for MySQL 8.0 :

Now it is forbidden to start the server with the lower_case_table_names parameter , which is different from the parameter used when initializing the server. The restriction is necessary because the sorting parameters used by the fields of the data dictionary table are based on the setting defined during server initialization, and restarting the server with a different setting would lead to inconsistencies regarding the order and comparison of identifiers. (Error No. 27309094, Error No. 89035)

When I last encountered this problem, I decided to perform the difficult task of reducing all table names and changing all the code. Sorry, I have no better way.