Trouble inserting with Hibernate2 and newer MySQL

[Enrique Flores]

Hi,

I have a Hibernate 2 application that has worked continuously for about a year running on OS X with MySQL. Now we need to deploy it for a different customer on Windows, and it is failing, specifically, it can read from the database fine, but upon trying to insert an object with a one-to-many relation, I am getting an error from MySQL that there is a foreign key violation when inserting the child object.

The problem seems to be related to the MySQL version, because if while running on Windows I point hibernate to MySQL 4.1.13 running on a Linux box, it works, but on Windows I've tried both MySQL 4.1.9 and 4.1.15 and it fails (Can't find 4.1.13 on the MySQL site, and at any rate I want to fix the problem on my end). I am running mysql-connector-java-3.1.12 - I tried different older versions of the connector but didn't affect it.

I wonder if it may have something to do with the identity generator, because when I have it running against the Windows database, (I have a one to many relation beween Node -> LightConfig) it fails as it tries to save LightConfig, but on Linux, it prints out that it generated a native identity and then goes on to commit successfully.. in my mapping files I have the generator class set to 'native'.

MySql 4.1.14-nt on Windows XP:

00:37 DEBUG (EntityPersister.java:484) - Inserting entity: com.ocr.LightConfig (native id)
00:37 DEBUG (BatcherImpl.java:204) - about to open: 0 open PreparedStatements, 0 open ResultSets
00:37 DEBUG (BatcherImpl.java:230) - insert into LIGHT_CONFIGS (BRIGHT_FIELD, DARK_FIELD, SCORE, NODE_ID) values (?, ?, ?, ?)
00:37 DEBUG (BatcherImpl.java:253) - preparing statement
00:37 DEBUG (EntityPersister.java:382) - Dehydrating entity: [com.ocr.LightConfig#<null>]
00:37 DEBUG (NullableType.java:46) - binding '10' to parameter: 1
00:37 DEBUG (NullableType.java:46) - binding '15' to parameter: 2
00:37 DEBUG (NullableType.java:46) - binding '0.0' to parameter: 3
00:37 DEBUG (NullableType.java:46) - binding '0' to parameter: 4
00:37 DEBUG (BatcherImpl.java:211) - done closing: 0 open PreparedStatements, 0 open ResultSets
00:37 DEBUG (BatcherImpl.java:275) - closing statement
00:37 DEBUG (JDBCExceptionReporter.java:49) - could not insert: [com.ocr.LightConfig]
java.sql.SQLException: Cannot add or update a child row: a foreign key constraint fails


Mysql 4.1.13 on Suse Linux 10 :

03:58 DEBUG (EntityPersister.java:484) - Inserting entity: com.ocr.LightConfig (native id)
03:58 DEBUG (BatcherImpl.java:204) - about to open: 0 open PreparedStatements, 0 open ResultSets
03:58 DEBUG (BatcherImpl.java:230) - insert into LIGHT_CONFIGS (BRIGHT_FIELD, DARK_FIELD, SCORE, NODE_ID) values (?, ?, ?, ?)
03:58 DEBUG (BatcherImpl.java:253) - preparing statement
03:58 DEBUG (EntityPersister.java:382) - Dehydrating entity: [com.ocr.LightConfig#<null>]
03:58 DEBUG (NullableType.java:46) - binding '10' to parameter: 1
03:58 DEBUG (NullableType.java:46) - binding '15' to parameter: 2
03:58 DEBUG (NullableType.java:46) - binding '0.0' to parameter: 3
03:58 DEBUG (NullableType.java:46) - binding '0' to parameter: 4
03:58 DEBUG (AbstractEntityPersister.java:1234) - Natively generated identity: 8
03:58 DEBUG (BatcherImpl.java:211) - done closing: 0 open PreparedStatements, 0 open ResultSets
03:58 DEBUG (BatcherImpl.java:275) - closing statement
03:58 DEBUG (Cascades.java:506) - done processing cascades for: com.ocr.node.Node
03:58 DEBUG (JDBCTransaction.java:59) - commit

Since on Windows I'm not getting the log message about 'Natively generated identity', is it possible it's not able to generate one and it's just using 0? The only thing is, there is no error from hibernate up until the database throws the error..

thanks for any help

[Mark Soderquist]

I believe I am having the same trouble with Hibernate 3 on MySQL 4.1 and up (I haven't tried an earlier version). I am using Hibernate 3.1 (tried 3.0 also), Connector/J 3.1 (tried 3.0 also), and MySQL 4.1.16 (tried 4.1.13a and 5.0.18 also). All combinations seem to have the same result trying to save a new object. I do know that the native counter is being incremented because if I add a new row manually the id column has been incremented by the number of times I tried to get hibernate to increment it. It doesn't seem to matter what Hibernate id generator I use (tried native and increment) the object is not saved.

[Mark Soderquist]

It looks like my problem lies with closing the session before the transaction has time to finish. My code followed this pattern:

Session session = factory.openSession();
try {
// Do hibernate work here...
session.flush();
} catch( HibernateException exception ) {
// Log exception.
} finally {
session.close();
}

I changed it to do the following:

Session session = factory.openSession();
Transaction transaction = session.beginTransaction();
try {
// Do hibernate work here...
transaction.commit();
} catch( HibernateException exception ) {
transaction.rollback();
// Log exception.
} finally {
session.close();
}

The addition of the Transaction API has made the code run much more consistently. It seems that the faster the computer is, the more likely the non-transaction pattern seems to fail. It looks like flush() just doesn't get the work done before the session is closed. I'm not sure if I am using Hibernate incorrectly just yet or if there is a bug in the flush() method in Hibernate's Session class.