Home > hibernate, Java, mysql, Uncategorized > Hibernate + MySQL + Mac = Foreign Key Nightmares. A painless solution to a painful problem

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.

About these ads
  1. George
    May 23, 2011 at 2:08 pm

    This must be a personal project if you’re using your Mac as a dev platform. Care to share what you’re working on?

    • i82much
      May 23, 2011 at 3:06 pm

      Nah, it is a work project. I use a Macbook Pro to develop.

  2. October 7, 2011 at 2:40 am

    Nice solution: I’ve had exactly the same issue when adding MySQL support to Syncope (http://www.syncope-idm.org) with some of my mac-powered colleagues. We solved that by modifying the default configuration on macs, but your solution is much more brilliant ;-)

    …even though I would propose the same solution I’ve proposed to my colleagues: why don’t you just switch to Linux? eh eh eh…

  1. July 6, 2012 at 9:32 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: