Archive

Posts Tagged ‘hibernate’

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

May 23, 2011 4 comments

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.

Advertisements

Mono, NHibernate, MySQL – A complete example

May 2, 2011 6 comments

This article aims to show you how to use Mono, MonoDevelop 2.4.2 and NHibernate (3.2.0.Alpha2) to connect to a MySQL database on Mac OSX 10.6, Snow Leopard. (I’m assuming the solution can be adapted to other *NIX variants, but I’ve only tried it on Snow Leopard). The examples I found online were frequently Windows only. I ran into a lot of problems along the way, which I am documenting here to prevent others from having the same issues.

Step 1 – MonoDevelop:

Download and install MonoDevelop. I am using version 2.4.2.

Step 2 – NHibernate:

Download NHibernate zip (I am using version 3.2.0.Alpha2), extract to a location you will remember later (I put it on my Desktop)

Step 3 – Install MySQL

3a.) Download MySQL, and install it. I used the 64 bit DMG Archive for Mac. I opened the package, and ran all of the installers. Make sure you install the PrefPane support too. When you go into the System Preferences, you should see an entry for MySQL.

Pref pane

Pref pane

Go into the MySQL settings and choose Start MySQL Server. After this is done, launch a terminal window. Type mysql -u root in the window; if everything is configured correctly, you should get a command prompt that looks like the following:

$ mysql -u root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1553 Server version: 5.5.11 MySQL Community Server (GPL) Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> 

Type the following at the command line prompt to create a new database and user we will use later: (Note that in a production system you should not have a dev account with such a simple password, and certainly not with all privileges.)

mysql> CREATE DATABASE products; Query OK, 1 row affected (0.00 sec) mysql> CREATE USER 'dev'@'localhost' IDENTIFIED BY 'test_password'; Query OK, 1 row affected (0.00 sec) mysql> GRANT ALL PRIVILEGES ON *.* TO 'dev'@'localhost' WITH GRANT OPTION; Query OK, 1 row affected (0.00 sec) 

At this point, we now have an account named ‘dev’ with password ‘test_password’, and a database named ‘products’. We will use that information when connecting to the database using NHibernate.

Step 4 – Download MySQL Driver

In order to use MySQL with .NET, you need a database driver. From the MySQL Connectors page, choose Connector/Net, and then choose .NET & Mono from the dropdown. Download the file; it should be entitled mysql-connector-net–6.3.6-noinstall.zip.

When the file finishes downloading, unzip it and place the folder somewhere easy to get to; I put it on my Desktop.

Step 5 – Create Domain Objects / Hibernate configs

Follow the instructions on Your first NHibernate based application, up until the section “Test the Setup” to create all of the domain classes. We need to modify the hibernate.cfg.xml file to match what’s in this gist.

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
 <!-- an ISessionFactory instance -->
 <session-factory>
  <!-- properties -->
  <property name="connection.provider">
    NHibernate.Connection.DriverConnectionProvider
  </property> 
  <property name="connection.driver_class">
    NHibernate.Driver.MySqlDataDriver
  </property> 
  <property name="connection.connection_string">
    <!-- Found from http://www.connectionstrings.com/mysql; note that the 'dev' and 'test_password' 
    match the user we created, and 'products' matches the Database -->
Server=localhost;Database=products;Uid=dev;Pwd=test_password;
  </property>

  <!-- Will automatically create a fresh table each time we create an ISessionFactory object -->
  <property name="hbm2ddl.auto">create-drop</property>

  <property name="dialect">
    NHibernate.Dialect.MySQLDialect
  </property>
  <property name="show_sql">true</property>
 </session-factory>
</hibernate-configuration>

Note that the dialect, connection.driver_class, and connection.connection_string properties have all been changed; I also added an hbm2ddl.auto property. You can read more about that here.

Fix missing references

At this point, we need to ensure that all of the dlls we need are referenced correctly. Right click on the References node, and choose Edit references. Choose the .Net Assembly tab, and browse to where you saved the mysql-connector-net-6.3.6-noinstall folder; I placed mine on the desktop. Choose the v2 folder, select all of the dlls, and choose add.

At this point your screen should look something like the following:

Edit references

Edit references

edit references

If you don’t have an NHibernate.dll listed in your references, you will need to add this as well. Follow the same steps as previously listed, and add the NHibernate.dll found in NHibernate-3.2.0.Alpha2-src/lib/net/3.5/. I added all of the dlls in that folder, but that’s overkill.

Add main entry point

At this point, you’re almost ready to get started. Create a file named Main.cs at the same level as your hibernate.cfg.xml file. Use the following code:

using System;
using System.Collections.Generic;
using NHibernate;
using NHibernateTest.Domain;
using NHibernate.Tool.hbm2ddl;

namespace NHibernateTest
{
    class MainClass
    {
        public static void Main (string[] args)
        {
            IList products;

            // Don't need to use schema export because of the hbm2dll property.
            var cfg = new NHibernate.Cfg.Configuration();
            cfg.Configure();
            // ensure that mapping hbm.xml file is loaded
            cfg.AddAssembly(typeof(MainClass).Assembly);

            Product p = new Product() {Name="Captains of Crush Gripper #1", Category="fitness" };

            ISessionFactory factory =
                cfg.BuildSessionFactory();

            using (ISession session = factory.OpenSession())
            {
                session.Save(p);
                session.Flush();

                ICriteria sc = session.CreateCriteria();
                products = sc.List();
                Console.WriteLine(products[0].Name);
                session.Close();
            }
            factory.Close();

            Console.WriteLine( products.Count );

            Console.WriteLine ("Hello World!");
        }
    }
}

My namespace might be slightly different from yours; modify accordingly.

Build and run the project. You should get output like the following:

NHibernate: INSERT INTO Product (Name, Category, Discontinued, Id) VALUES (?p0, ?p1, ?p2, ?p3);?p0 = 'Captains of Crush Gripper #1' [Type: String (28)], ?p1 = 'fitness' [Type: String (7)], ?p2 = False [Type: Boolean (0)], ?p3 = a9f8e586-727f-49b9-98da-59f534163ae7 [Type: Guid (0)] NHibernate: SELECT this_.Id as Id0_0_, this_.Name as Name0_0_, this_.Category as Category0_0_, this_.Discontinued as Disconti4_0_0_ FROM Product this_ Captains of Crush Gripper #1 1 Hello World! Abort trap Press any key to continue... 

Conclusion/Troubleshooting

I hope this helps newbies like myself get up and running in a Mono environment with NHibernate and MySQL. The complete code can be found on Github.

MappingException

Unhandled Exception: NHibernate.MappingException: No persister for: NHibernateTest.Domain.Product 

If you get an exception like this, the Product.hbm.xml file is not being loaded correctly. Make sure that you have the following line:

cfg.AddAssembly(typeof(MainClass).Assembly); 

Conversely, make sure that your Product.hbm.xml file is set to be ‘embed as resource’. (Right click, choose Build Action, Embed as resource).

embed as resource

embed as resource

Could not compile the mapping document

Unhandled Exception: NHibernate.MappingException: Could not compile the mapping document: NHibernateTest.Mappings.Product.hbm.xml ---> System.InvalidOperationException: Could not find the dialect in the configuration 

If you get an error like this, you have inadvertently switched the order of your AddAssembly and Configure calls. Make sure you call Configure before you call AddAssembly.