Maintain case sensitivity when importing db into windows

Question

I have an export from a MYSQL database on a linux machine however when importing that database into MYSQL on windows all of the table names that were camel cased are now all lower case. The sql dump has the correct case in it but the importing via phpmyadmin seams to remove these.

How can I import it and keep the case?

Zeist

Answer 1

There is a setting for mysql to allow case differentiation in windows. You need to edit the my.cnf file and alter the setting:

lower_case_table_names=2

Then restart mysql.

Otherwise, this may be a case of phpmyadmin changing case in the way it passes queries to the server rather than a linux-to-windows problem. Have you tried importing the sql dump using another mysql manager such as SQLyog? (Tools -> Restore from SQL Dump...)

Hesitancy

Thanks for the help, but even with setting that command (which wasn't in the ini, had to add it after [mysqld] ), restarting mysql and then using the command line to import the data it still didn't keep the table name case. – Zeist

It may be that the variable needs to be 0 instead of 2, as Atonewell suggests, however the reference manual says this: " You should not set this variable to 0 if you are running MySQL on a system that has case-insensitive file names (such as Windows or Mac OS X). If you force this variable to 0 with --lower-case-table-names=0 on a case-insensitive file system and access MyISAM tablenames using different lettercases, index corruption may result. " – Hesitancy

Answer 2

It is probably worth reading the following page from the MySQL Reference Manual: http://dev.mysql.com/doc/refman/5.5/en/identifier-case-sensitivity.html

Based on that, I think you need to set to 0, not 2, which will ensure that the schema is stored using the same case as defined in your DDL.

lower_case_table_names=0

Neoplasticism

Answer 3

Go to C:\ProgramData\MySQL\MySQL Server x.x.x, Locate my.ini file add lower_case_table_names=2 at the bottom and restart Server.

Use lower_case_table_names=0 on Unix and lower_case_table_names=2 on Windows.

Pe