Hibernate + MySQL + Mac = Foreign Key Nightmares. A painless solution to a painful problem

tl;dr summary: Avoid using mixed case table names when using MySQL on a Mac.  Use lowercase underscore separated table names instead.

I was using Hibernate to map my Java classes to MySQL tables and columns.  For most classes, inserts worked perfectly.  For other classes, I’d consistently get errors like

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

By running the command

show engine innodb status

in my mysql window, I found following clue:

110520 14:26:09 Transaction:
TRANSACTION 85B76, ACTIVE 0 sec, OS thread id 4530606080 inserting
mysql tables in use 1, locked 1
1 lock struct(s), heap size 376, 0 row lock(s)
MySQL thread id 3, query id 2175 localhost root update
insert into TableName (pk_Pdu) values (10)
Foreign key constraint fails for table `myproj`.`tablename`:
,
  CONSTRAINT `FKEC7DE11817B41BEB` FOREIGN KEY (`pk_Pdu`) REFERENCES `ParentClass` (`pk_Pdu`)
Trying to add to index `PRIMARY` tuple:
DATA TUPLE: 3 fields;
 0: len 8; hex 800000000000000a; asc         ;;
 1: len 6; hex 000000085b76; asc     [v;;
 2: len 7; hex 00000000000000; asc        ;;

But the parent table `myproj`.`ParentClass`
or its .ibd file does not currently exist!

I knew for a fact the table existed; I was able to query it and it showed up fine. Something else must be going on.

I finally stumbled onto the answer by way of a StackOverflow post:

However, I did rename the tables all to lowercase and that did make a difference. A quick search indicates I should maybe setting lower_case_table_names = 1 since I am using InnoDB. On Mac OS/X it is 2 by default (and I failed to mention I’m using a new box which may be why it isn’t working locally).

Sure enough, as soon as I renamed the table names to be all lowercase underscore separated, things worked perfectly. The default naming strategy in Hibernate names the tables in exactly the same way as the class names (e.g. in CamelCase as opposed to lower_case_underscore_separated). Fortunately the designers saw fit to make this naming convention overridable. All I had to do was add one line of code to fix my entire problem:


Configuration config = new Configuration();
// Name tables with lowercase_underscore_separated
config.setNamingStrategy(new ImprovedNamingStrategy());

Thanks to this blog post on ImprovedNamingStrategy for pointing the way. This post also helped me find the problem.

Conclusion

If you’re using Hibernate and a MySQL database running on MacOSX, make sure that your table names are all in lowercase.  This can be accomplished by using the ImprovedNamingStrategy class when configuring Hibernate.

This experience taught me a valuable lesson.  The first is, sometimes a problem can be caused by something that’s not directly your fault per se (i.e. I hadn’t incorrectly structured my Hibernate annotations, as I initially suspected), but rather due some quirk in the operating system or external tools you’re using.  The second is it’s crucial for cross platform libraries like Hibernate to provide the hooks for you to be able to swap out default behavior, precisely to be able to work around problems like these.  Thankfully Hibernate had built in just the hooks I needed to solve the problem.