CODEWITHSUNDEEP
mysqlwindowscase-sensitive
Ehsan Khodarahmi
Ehsan Khodarahmi

Reputation: 4922

How to force case sensitive table names?

I'm working on a MySQL database in windows.

I must move it into a Linux environment. MySQL database contains a lot of tables and stored procedures which are CASE SENSITIVE.

When I backup my database, all table names are forced lowercase so when I restore it in Linux it complains because you can't make duplicate tables and stored procedures.

I don't have access to the MySQL configuration in the linux environment so I cant change MySQL settings to case insensitive mode.

Is there any way to force MySQL (v5.x) to use case sensitive table names in windows?

Upvotes: 48

Views: 105536

Answers (9)

Nayeem Bin Ahsan
Nayeem Bin Ahsan

Reputation: 471

By default, table names are case insensitive in Windows, but you can make it case sensitive by updating the lower_case_table_names variable to 2.

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

However, before going to the further process, create backups of your existing databases. Then you need to remove all existing databases to avoid table name inconsistency.

We were on the step to set lower_case_table_names to 2, but this variable is read-only, so you can not update it using the script. So need to change it to the configure file (my.ini). For windows, it's available in the C:\ProgramData\MySQL\MySQL Server X.X directory. Open this file in administrator mode and set the lower_case_table_names variable value to 2.

These changes will not take effect until you restart the MySQL server before restarting the MySQL Server by following steps.

  • Open the Run window by using the Windows+R keyboard
  • Type services.msc and press Enter
  • Select the MySQL service and click the restart button

ref: https://www.mysqltutorial.org/mysql-adminsitration/restart-mysql/

MySQL server might not start because of changes in the my.ini file. In that case, we can not proceed further. Therefore lower_case_table_names variable value set back 1 again. then restart the MySQL server mentioned above.

Finally, we only have one remaining option to make a case-sensitive table name for MySQL in windows, completely removing MySQL from your machine and configuring it during installation. During install, check Advanced and Logging Options from the Type and Networking tab, then select the radio button to preserve the given case from the Advanced Options tab.

ref: Can't set lower_case_table_names in MySQL 8.x on Windows 10

Upvotes: 2

Husam Ebish
Husam Ebish

Reputation: 6768

2023

1- open my.ini, in my case this file located in: C:\xampp\mysql\bin\

2- Under [mysqld] add lower_case_table_names=2

enter image description here

3- Restart the server

When rename or create new database / table:

enter image description here

Upvotes: 0

sunny
sunny

Reputation: 86

In MySQL 8, lower_case_table_names setting is not allowed once installation is done. To enable the setting , you have to re-install mysql

Upvotes: 0

ATorras
ATorras

Reputation: 4303

Read the following chapter in the official MySQL documentation: Identifier Case Sensitivity.

Then add the following system variable to the server section, [mysqld], inside the my.ini configuration file and restart the MySQL service:

lower_case_table_names=2

Upvotes: 56

Evgeny Chashnik
Evgeny Chashnik

Reputation: 91

On Windows put lower_case_table_names=2 at the end of the
C:\ProgramData\MySQL\MySQL Server 5.7\my.ini file.

Upvotes: 8

Krishnam
Krishnam

Reputation: 849

Add this property to lower_case_table_names=2 to my.ini

Upvotes: 1

Hatem Badawi
Hatem Badawi

Reputation: 546

i add this line and it solve the different case problem

lower_case_table_names=1

in /etc/my.cnf

you can see how to set mysql variable "lower_case_table_names" form this page : https://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_lower_case_table_names

Upvotes: 0

Augusto
Augusto

Reputation: 29907

Unfortunately there's no way of making MySQL on windows to behave 100% as in Linux. What you can do, is to run a minimal VM on Virtual Box or VMware player with TurnKey - MySQL Appliance.

In my personal experience, I've found quite useful to have a VM with a configuration similar to the deployment environment to diagnose problems.

Upvotes: 4

Devart
Devart

Reputation: 121922

Have a look at this article - http://dev.mysql.com/doc/refman/5.1/en/identifier-case-sensitivity.html

Mode 2 allows to store tables with specified lettercase, but anyway, name comparisons won't be case sensitive and you won't be able to store table1 and Table1 at the same time.

Upvotes: 8

Related Questions