mysqldump drop create use case sensitivity

Question

Dumped sql from MySQL is showing inconsistencies in the name of the database.

Using the following bog standard method to dump a database:

mysqldump --add-drop-database -u someuser --databases Geo-ASty

I get the following output:

...
/*!40000 DROP DATABASE IF EXISTS `Geo-ASty`*/;

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `geo-asty` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */;

USE `Geo-ASty`;
...

Note the case for the database which changes for the CREATE DATABASE statement. I kid you not.

I wouldn't care if it worked but importing this sql back in causes errors:

ERROR 1049 (42000) at line 26: Unknown database 'Geo-ASty'

I'm using MySQL 5.5 and exporting from a Mac.

Answer 1

Set lower_case_table_names=1 on the Linux server before importing, this will convert all databases to lower case and new queries will use lower case, too. You can see it here.

Your problem arises from the fact that the default value on Mac is 2 (names are not case sensitive when queried, but they are when stored) but 0 on Unix/Linux (names are always case sensitive). This is because entities like tables and databases names depend on ON files and directories, which have different properties on different systems.

To avoid further problems I would recommend you following a standard (usually, only using lower case identifiers for tables, databases and aliases -column names are always case insensitive) and stick to it.