MySql case sensitive table names issue on Windows/Linux

Posted on by By Nikhilesh, in Business Intelligence, Databases | 0

MySql case sensitive table names issue on Windows/LinuxTableNameIssue


 

Moving a MySQL database from Windows to Linux .Many times we faced the problem that on Linux the names of the tables are case sensitive due to that we can’t find tables.

Make data easy with Helical Insight.
Helical Insight is the world’s best open source business intelligence tool.

Grab The 30 Days Free Trail

Solution :-

Note:

Just altering the “lower_case_table_names” setting isn’t enough. It needs to be done before you import your database(s).

Location:

/etc/mysql/my.cnf file (add the row lower_case_table_names=1)

Note:

lower_case_table_names=0 >  table names are stored as specified and comparisons are case sensitive.

lower_case_table_names=1 >   table names are stored in lowercase on disk and comparisons are not case sensitive.

lower_case_table_names=2 >   table names are stored as given but compared in lowercase.

This option also applies to database names and table aliases.

 

The MySQL 5.1 documentation lists a procedure for moving between Windows and Linux/UNIX. This will ensure that your desired rules for enforcing case sensitivity are followed. Take a look and verify that you did these steps in the correct order:

 

To convert one or more entire databases, dump them before setting lower_case_table_names, then drop the databases, and reload them after setting lower_case_table_names:

 

Step- 1

Use mysqldump to dump each database:

mysqldump –databases db1 > db1.sql

mysqldump –databases db2 > db2.sql

… Do this for each database that must be recreated.

 

Step -2

Use DROP DATABASE to drop each database.

 

Step -3

Stop the mysql-server, set lower_case_table_names in the [mysqld] section of your \etc\mysql\my.cnf file, and restart the server.

 

Make data easy with Helical Insight.
Helical Insight is the world’s best open source business intelligence tool.

Lets Register For Live Demo

Step-4

Reload the dump file for each database. Because lower_case_table_names is set, each database and table name will be converted to lowercase as it is recreated:

mysql < db1.sql

mysql < db2.sql

Thanks for reading : Pushpraj Kumar ( Helical IT Solutions )

logo

Best Open Source Business Intelligence Software Helical Insight is Here

logo

A Business Intelligence Framework

0 0 votes
Article Rating
Subscribe
Notify of
0 Comments
Inline Feedbacks
View all comments