47

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?

Prix
  • 19,417
  • 15
  • 73
  • 132
Ehsan Khodarahmi
  • 4,772
  • 10
  • 60
  • 87
  • 3
    Surprisingly, upgrading from MySQL 5.1 to MySQL 5.5 solved my problem! Now I can use case-sensitive table name in windows environment, although its still impossible to create two tables with the same name that only differs in letter case(i.e table_1 and TABLE_1) and of course I never want to do this! – Ehsan Khodarahmi Jul 10 '11 at 18:34

9 Answers9

56

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

informatik01
  • 16,038
  • 10
  • 74
  • 104
ATorras
  • 4,073
  • 2
  • 32
  • 39
  • 1
    Mind you, this works only on case-insensitive file systems, e.g. Mac and Windows, but NOT Linux, because Linux's default fs is case-sensitive (however, if you create a case-insensitive fs on Linux and store the mysql data there, then it works - but that's not quite that simple, although possible with the help of google). Removed my upvote. – Stefan Steiger Nov 14 '13 at 07:34
  • Mode 2 on Windows does *not* actually compare the names in a case sensitive manner, you can't have ThisTable and thistable. – simontemplar Mar 07 '14 at 11:28
  • Thx that solve my issue with "SS [ SilverStripe ] table names and case sensitivity" – user956584 Mar 09 '14 at 12:37
  • 9
    No effect for InnoDB on windows, screw you MySQL! – Samuel Katz Mar 21 '14 at 10:15
  • 2
    On behalf of Patrick: On unix systems you'd use `/my.ini: lower_case_table_names=0` – yizzlez Mar 29 '14 at 14:29
  • Hmm, I'm on OSX and this is set to 2 (by default?), but still I don't get errors when using the wrong case in table names.. What could I be missing? I checked the value via `show variables;` – Joost Mar 08 '15 at 18:15
  • @Joost From the manuals: "This works only on file systems that are not case sensitive! InnoDB table names are stored in lowercase, as for lower_case_table_names=1". – ATorras Mar 10 '15 at 18:51
  • Which section in the file? [mysqld] or [client]? – Pete Alvin May 27 '16 at 20:33
8

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

informatik01
  • 16,038
  • 10
  • 74
  • 104
Evgeny Chashnik
  • 91
  • 1
  • 1
  • 1
    https://mariadb.com/kb/en/mariadb/identifier-case-sensitivity/ Note: mysql and mariadb are basically the same so this should apply `It is thus possible to make Unix-based systems behave like Windows and ignore case-sensitivity, but the reverse is not true, as the underlying Windows filesystem can not support this.` – EpiX Jul 01 '16 at 22:26
8

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.

Devart
  • 119,203
  • 23
  • 166
  • 186
4

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.

Augusto
  • 28,839
  • 5
  • 58
  • 88
1

Add this property to lower_case_table_names=2 to my.ini

Kris
  • 719
  • 1
  • 11
  • 19
1

By default, table names are case sensitive 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

Nayeem Bin Ahsan
  • 391
  • 4
  • 7
0

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

Hatem Badawi
  • 536
  • 4
  • 9
0

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

sunny
  • 66
  • 3
0

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

Husam Ebish
  • 4,893
  • 2
  • 22
  • 38