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
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..
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.
Joined: Jan 13, 2006
It looks like my problem lies with closing the session before the transaction has time to finish. My code followed this pattern:
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.
subject: Trouble inserting with Hibernate2 and newer MySQL