69

I am using Windows 7 and XAMPP. I am trying to export my database and while in the process the table names are converted to lower case.

I have searched a lot, I know I have to change the value of lower_case_table_names from 0 to 2, but where do I have to change this value, in which file?

informatik01
  • 16,038
  • 10
  • 74
  • 104
John x
  • 4,031
  • 8
  • 42
  • 67

9 Answers9

113

Do these steps:

  1. open your MySQL configuration file: [drive]\xampp\mysql\bin\my.ini
  2. look up for: # The MySQL server [mysqld]
  3. add this right below it: lower_case_table_names = 2
  4. save the file and restart MySQL service

From: http://webdev.issimplified.com/2010/03/02/mysql-on-windows-force-table-names-to-lowercase/

Mohammad Saberi
  • 12,864
  • 27
  • 75
  • 127
  • Finally this solved the problem for me: I added lower_case_table_names = 2 in the mysqld section of my.ini. Then deleted and recreated the databases. Now in two of thee databases it works. BUT NOT IN THE THIRD!! This one contains only views. They are always created with lowercase names. Why? – Alex Sep 29 '14 at 15:26
  • I just realized: camelCaseNames are kept when I create the tables using a dumpfile or an sql file. BUT: The names of Views are ALWAYS forced to lowercase. In all databases. How can I solve this? – Alex Sep 29 '14 at 15:29
  • 1
    o.k., got it: In order to use camelCaseNames in tablenames and views, lower_case_table_names has to be 0 – Alex Sep 29 '14 at 15:51
  • It works but when connecting with MySQL Workbench v8.0.19 to my MySQL server v5.7.24 I get an error "Server configuration problems. 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. " However I can create tables like "Test" or "testTest". – Jan May 08 '20 at 06:31
11

On linux I cannot set lower_case_table_names to 2 (it reverts to 0), but I can set it to 1.

Before changing this setting, do a complete dump of all databases, and drop all databases. You won't be able to drop them after setting lower_case_table_names to 1, because any uppercase characters in database or table names will prevent them from being referenced.

Then set lower_case_table_names to 1, restart MySQL, and re-load your data, which will convert everything to lowercase, including any subsequent queries made.

Sam Barnum
  • 10,559
  • 3
  • 54
  • 60
8

Try adding/editing lower_case_table_names = 2 in my.ini or my.cnf

Detect
  • 2,049
  • 1
  • 12
  • 21
7

I have same problem while importing database from linux to Windows. It lowercases Database name aswell as Tables' name. Use following steps for same problem:

  1. Open c:\xampp\mysql\bin\my.ini in editor.
  2. look for

# The MySQL server

[mysqld]

3 . Find

lower_case_table_names

and change value to 2

if not avail copy this at the end of this [mysqld] portion.

lower_case_table_names = 2

This will surely work.

Jimil Choksi
  • 351
  • 4
  • 9
4

Also works in Wampserver. Click on the Green Wampserver Icon, choose MySql, then my.ini. This will allow you to open the my.ini file. Then -

  1. look up for: # The MySQL server [mysqld]
  2. add this right below it: lower_case_table_names = 2
  3. save the file and restart MySQL service

Important Note - add the lower_case_table_names = 2 statement NOT under the [mysql] statement, but under the [mysqld] statement

Reference - http://doc.silverstripe.org/framework/en/installation/windows-wamp

user3257693
  • 486
  • 3
  • 14
2

If you have the file my-default.ini rename it to my.ini

Yuan
  • 21
  • 1
2

ADD following -

  • look up for: # The MySQL server [mysqld]
  • add this right below it: lower_case_table_names = 1 In file - /etc/mysql/mysql.conf.d/mysqld.cnf

It's works for me.

Giacomo1968
  • 25,759
  • 11
  • 71
  • 103
Manoj Dharkar
  • 31
  • 3
  • 1
    Thanks for your answer. Are you sure "/etc/mysql/mysql.conf.d" will be the right path on a Windows-7 system? Can you include an example of the changed section of the mysqld.cnf file to may your answer clearer? – rwp Mar 30 '18 at 09:32
  • /etc/mysql is in linux. – Marcelo Agimóvel Nov 12 '18 at 01:34
1

On Mysql Server 8.0 Windows os, if you change lower_case_table_names=2 in mysql.ini the server will not start so you have to follow this

1: Backup all data / export to .sql or Dump all data 
2: Stop the server from service 
3: Delete the data folder from C:\Program Files\ Mysql Server 8.0\Data 
4: Open C:\Program Files\ Mysql Server 8.0\my.ini as an administrator using notepad 
5: find set lower_case_table_names= 2 if you want camelCase table name 
6: open mysql installer and open reconfigure then open ->show advanced and logging option -> Advanced Options -> use second option from the list 
7: then everything will be ok
JDOaktown
  • 4,262
  • 7
  • 37
  • 52
amanuel zerfu
  • 33
  • 1
  • 7
1

Look for a file named my.ini in your hard disk, in my system it's in

c:\program files\mysql\mysql server 5.1

If it's not my.ini it should be my.cnf

golimar
  • 2,419
  • 1
  • 22
  • 33
  • 2
    From http://stackoverflow.com/questions/17896829/mysqls-lower-case-table-names-wont-change, I found the file in this path: C:\ProgramData\MySQL\MySQL Server 5.6 – Irawan Soetomo Oct 10 '14 at 04:12