Hibernate-MySQL Insert problem

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

well, if anyone cares, I narrowed it down to the fact that the Windows
mysql servers are by default using the InnoDB storage engine, which
supports foreign keys, whereas the Linux installations are using
MyISAM, which doesn't. Why Hibernate is generating SQL code that causes
foreign key violations is a problem for another day.

this might be your problem:

http://www.hibernate.org/116.html#A9

i


efloresc wrote:
> well, if anyone cares, I narrowed it down to the fact that the Windows
> mysql servers are by default using the InnoDB storage engine, which
> supports foreign keys, whereas the Linux installations are using
> MyISAM, which doesn't. Why Hibernate is generating SQL code that causes
> foreign key violations is a problem for another day.