Why does Hibernate insert a parent row with a foreign key without inserting a child row?

Question

I hope someone comes across this problem sooner and can help me. Basically, Hibernate inserts a parent row (with an identifier pointing to a child row), but not inserting this child row with an associated identifier, which leaves the database in a bad state. Here is an example of an exception that is thrown when Hibernate tries to load an incorrectly saved object:

27 Jun 2011 13:55:31,380 ERROR [scheduler_Worker-4] - Job DEFAULT.queryScrubJobDetail threw an unhandled Exception: org.springframework.scheduling.quartz.JobMethodInvocationFailedException: Invocation of method 'doIt' on target class [XXX] failed; nested exception is org.springframework.orm.hibernate3.HibernateObjectRetrievalFailureException: No row with the given identifier exists: [XXX.DataProviderTransaction#60739703]; nested exception is org.hibernate.ObjectNotFoundException: No row with the given identifier exists: [com.idology.persist.DataProviderTransaction#2] 

This part of the application has three objects:

• The request that is the parent of the DataProviderTransactionReference and DataProviderTransaction
• DataProviderTransaction , which is a child of Query and a parent of DataProviderTransactionReference
• DataProviderTransactionReference , which has foreign keys pointing to DataProviderTransaction and Query

Here are the mappings:

From Request :

 @OneToMany(mappedBy = "query", cascade = { CascadeType.PERSIST, CascadeType.MERGE }, fetch = FetchType.LAZY) @Cascade(org.hibernate.annotations.CascadeType.SAVE_UPDATE) @JoinColumn(name = "query_id") public List<DataProviderTransactionReference> getDataProviderTransactionReferences() 

From DataProviderTransaction :

 @ManyToOne(fetch = FetchType.LAZY) @JoinColumn(name = "query_id") public Query getQuery() 

From DataProviderTransactionReference :

 @ManyToOne(cascade = { CascadeType.PERSIST, CascadeType.MERGE }, fetch = FetchType.EAGER) @JoinColumn(name = "data_provider_transaction_id") @Cascade(org.hibernate.annotations.CascadeType.SAVE_UPDATE) public DataProviderTransaction getDataProviderTransaction() { return mDataProviderTransaction; } 

The schema looks like this (excluding the query table, because it does not have foreign keys):

 data_provider_transaction +------------------+---------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------------+---------------+------+-----+---------+----------------+ | id | bigint(20) | NO | PRI | NULL | auto_increment | | query_id | bigint(20) | YES | MUL | NULL | | +------------------+---------------+------+-----+---------+----------------+ data_provider_txn_refs +------------------------------+------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------------------------+------------+------+-----+---------+----------------+ | id | bigint(20) | NO | PRI | NULL | auto_increment | | created_at | datetime | YES | | NULL | | | data_provider_transaction_id | bigint(20) | YES | MUL | NULL | | | query_id | bigint(20) | YES | MUL | NULL | | +------------------------------+------------+------+-----+---------+----------------+ 

So, as soon as we finish the query (represented by the Query object), we save it using Spring and Hibernate, using the following:

 getHibernateTemplate().saveOrUpdate(aQuery); 

The request is saved with the associated DataProviderTransaction and DataProviderTransactionReference objects. Except that sometimes it saves Query and DataProviderTransactionReference without an associated DataProviderTransaction. It puts the identifier in data_provider_transaction_id, but points to a row that does not exist in the data_provider_transaction table.

The next step is to add a foreign key constraint to cause the problem when we perform the initial save, and not when we try to load the object later.

We use Spring 2.5.6, Hibernate 3.3.2, and MySQL 5.0. I saw that the problem had occurred over the years with earlier versions of Spring and Hibernate.

Has anyone ever seen / solved this problem?

Answer 1

This sounds like an id distribution problem with MySQL. Hibernation can get confused if the generators are not declared correctly, or if you do strange things with your code.

Do you have an orphan DataProviderTransactions or DataProviderTransactionReferences, DataProviderTransaction, which has a request identifier that does not exist, or indicate an invalid request?

Are your generators declared as identifiers for your identifiers? (see Chapter 5. Basic O / R mapping, section 5.1.4 id . Usually this should be enough, but there may be other things that you "do that confuse sleep mode."

So for example:

 @Id @GeneratedValue(strategy = IDENTITY) @Column(name = "id", unique = true, nullable = false) 

To really keep track of this, you need to know why this is happening, who inserts these lines. You need a foreign key constraint in the database. It is also possible that something is deleting the DataProviderTransaction, and the database is not complaining because there is no foreign key.