Where to change the value of lower_case_table_names=2 in XAMPP on Windows?
MysqlXamppMysql Problem Overview
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?
Mysql Solutions
Solution 1 - Mysql
Do these steps:
- open your MySQL configuration file: [drive]\xampp\mysql\bin\my.ini
- look up for:
# The MySQL server [mysqld]
- add this right below it:
lower_case_table_names = 2
- save the file and restart MySQL service
From: http://webdev.issimplified.com/2010/03/02/mysql-on-windows-force-table-names-to-lowercase/
Solution 2 - Mysql
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.
Solution 3 - Mysql
Try adding/editing lower_case_table_names = 2 in my.ini
or my.cnf
Solution 4 - Mysql
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:
- Open c:\xampp\mysql\bin\my.ini in editor.
- 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.
Solution 5 - Mysql
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 -
- look up for: # The MySQL server [mysqld]
- add this right below it: lower_case_table_names = 2
- 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
Solution 6 - Mysql
If you have the file my-default.ini
rename it to my.ini
Solution 7 - Mysql
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.
Solution 8 - Mysql
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
Solution 9 - Mysql
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