MySql case sensitive table names issue on Windows/Linux

MySql case sensitive table names issue on Windows/Linux

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.

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.

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 )

Best Open Source Business Intelligence Software Helical Insight is Here

A Business Intelligence Framework

Free
Download