Porting MySql from Ubuntu to MacOSX - Case sensitivity, foreign Key violation

Story:

My project is using Spring, Hibernate, and MySQL. I was trying to port a MySQL database from EC2 Ubuntu to my MacOSX for local development.

When I test the application, sometimes when an insert is triggered, I get a foreign key constraint failed exception.


SQL Error: 1452, SQLState: 23000
Cannot add or update a child row: a foreign key constraint fails


l made sure the foreign keys are not violated.

Then I ran the command in mysql:

show engine innodb status

I found the following:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
Foreign key constraint fails for table `store`.`checkoutevent`:
,
  CONSTRAINT `FKBD82C0D499A4F1ED` FOREIGN KEY (`id`) REFERENCES `TransactionEvent` (`id`)
But the parent table `store`.`TransactionEvent`
or its .ibd file does not currently exist!

I checked that the TransactionEvent table indeed exists.


Explanation:

After further investigation, I found that case sensitivity matters in different platforms.

Value	Meaning
0	Table and database names are stored on disk using the lettercase specified in the CREATE TABLE or CREATE DATABASE statement. Name comparisons are case sensitive. 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.
1	Table names are stored in lowercase on disk and name comparisons are not case sensitive. MySQL converts all table names to lowercase on storage and lookup. This behavior also applies to database names and table aliases.
2	Table and database names are stored on disk using the lettercase specified in the CREATE TABLE or CREATE DATABASE statement, but MySQL converts them to lowercase on lookup. Name comparisons are not case sensitive. This works only on file systems that are not case sensitive! InnoDB table names are stored in lowercase, as for lower_case_table_names=1.

Solution:

Export the database

mysqldump -u{username} -p --databases {database_name} > {database_name}.sql

Restart MySQL with --lower-case-table-names=1

mysqld --lower-case-table-names=1

Recreate the database (you need to drop the existing database first)

mysql -u{username} -p < {database_name}.sql