GeeCON Prague 2014*
The moose likes Object Relational Mapping and the fly likes Trouble inserting with Hibernate2 and newer MySQL Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Trouble inserting with Hibernate2 and newer MySQL " Watch "Trouble inserting with Hibernate2 and newer MySQL " New topic
Author

Trouble inserting with Hibernate2 and newer MySQL

Enrique Flores
Greenhorn

Joined: Jan 04, 2006
Posts: 1
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
Greenhorn

Joined: Jan 13, 2006
Posts: 2
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
Greenhorn

Joined: Jan 13, 2006
Posts: 2
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.
 
GeeCON Prague 2014
 
subject: Trouble inserting with Hibernate2 and newer MySQL