Lower_case_table_names set to 2, Workbench still does not resolve the database name in lower case

Question

I installed MySql Workbench 6.2 from MySql version 5.6 on a 64-bit version of Windows 7.

I would like to capitalize on my database name and table name. Therefore, I need to set the lower_case_table_names variable to 2. When I look at the General tab, it looks like this: When you click the Apply button, the No Change dialog box appears. No matter when I try to create a database with the letter "Capital", I get a warning:

The server is configured with bottom_case_table_names = 1, which only allows lowercase characters in schema and table names.

I have the feeling that the my.ini file on the server is different from the file specified in the configuration of the settings file. When I try to add this variable manually inside my my.ini file, I see the text below:

 # *** DO NOT EDIT THIS FILE. It a template which will be copied to the # *** default location during install, and will be replaced if you # *** upgrade to a newer version of MySQL. 

Here's how I got stuck in creating my circuit for a few days.

Answer 1

On Windows, the table name is case insensitive. That is, the Customer table and your Customer table will always be the same on Windows. This is a limitation of the NT file system. This applies when your MySQL server is running on a Windows platform. It doesn't matter where your desktop works.

(You can use table names with different names for different tables in Linux, BSD, etc., but this is considered very bad practice: do this only if you want to drive your colleagues crazy. Therefore, be careful.)

If you leave this lower_case_table_names parameter yourself, you can use mixed case in table names without any problems.

The my.ini file that the server actually uses when it starts is usually located in the data directory. The installation procedure may copy a pre-loaded version of this file, for example my_large.ini on top of my.ini , depending on what you are trying to do.

Answer 2

If your data files are located on a drive other than drive C: you can have two files "my.ini", one of which will be located on drive C: and when editing parameters in Workbench, this is a file that changes when "My .ini "your system actually works, remains valid. Check C: \ Program Data \ MySQL \ MySQL (server version) to see if the .ini file is there. If this is the case, you will probably find that it has changes at the bottom of the file that need to be written to the actual working .ini on the disk where your data is actually stored.

Answer 3

In the workbench go to: control panel> Settings file> General> System>

Check "lower_case_table_names", put a value of 2.

Close Workbench. Restart MYSQL56 Service

See img here. How to enable

Answer 4

• Change this file to /etc/mysql/my.cnf
• Add the following lines:

[tours]

lower_case_table_names = 1

3. Restart mysql sudo /etc/init.d/mysql restart

Answer 5

You cannot even start mysqld after changing the lower_case_table_names parameter other than 1 , which is the default.

0 => You should not set lower_case_table_names to 0 if you use MySQL on a system where the data directory is case-insensitive on the file system (for example, on Windows or macOS). This is an unsupported combination that can lead to a hang.

But try changing it to 2:

 # Specifies the on how table names are stored in the metadata. # If set to 0, will throw an error on case-insensitive operative systems # 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. # NOTE: Modify this value after Server initialization won't take effect. lower_case_table_names=2 

C: \ Program Files \ MySQL \ MySQL Server 8.0 \ bin> mysqld --defaults-file = "C: \ Program Files \ MySQL \ MySQL Server 8.0 \ my.ini"

 ERROR [MY-011087] [Server, Different lower_case_table_names settings for server ('2') and data dictionary ('1'). ERROR [MY-010020] [Server, Data Dictionary initialization failed. 

After initialization, you cannot change this parameter. Therefore, lower_case_table_names must be set with --initialize .

It is forbidden to start the server with the lower_case_table_names parameter which is different from the parameter used during server initialization. The restriction is necessary because the mappings used by the various fields of the data dictionary table are based on the setting defined during server initialization, and restarting the server with another parameter will lead to inconsistencies regarding how identifiers are ordered and compared.

mysqld --initialize - console --lower_case_table_names = 2

Then you will get the following error in the workbench after initializing the server again with lower_case_table_names=2 :

 A server configuration problem was detected. The server is in a system that does not properly support the selected lower_case_table_names option value. Some problems may occur. 

show variables like lower_case_table_names ;

 +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | lower_case_table_names | 2 | 

So, the conclusion: on Windows, leave the value 1, because 0 or 2 will not work or, as they put it, some problems may arise.

However, I do have the names of my database and tables that appear with capital letters. Which really doesn't do much, because the comparison will always be:

  # 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. 

Nicholas