[FIXED] Mysterious integrity constraint violation in HSQLDB database

Issue

We are designing our Java application to support HSQL database, in addition to enterprise databases such as SQL Server and Oracle. I was tasked with writing a comprehensive test suite which would cover the major database operations in the application. The test suite runs succesfully for SQL Server, but when I do the same test using HSQLDB, I keep getting a failure. Here is a portion of the stack trace:

Caused by: java.sql.SQLIntegrityConstraintViolationException: integrity constraint
violation: foreign key no parent; FKREQKDHUAI8BYVMWC5RMTJ3UFA table: FAVORITE_FILE
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCPreparedStatement.fetchResult(Unknown Source)
    at org.hsqldb.jdbc.JDBCPreparedStatement.executeUpdate(Unknown Source)
    at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:384)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:204)
    ... 91 more
Caused by: org.hsqldb.HsqlException: integrity constraint violation: foreign key no parent;
FKREQKDHUAI8BYVMWC5RMTJ3UFA table: FAVORITE_FILE
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.Constraint.getException(Unknown Source)
    at org.hsqldb.Constraint.checkInsert(Unknown Source)
    at org.hsqldb.StatementDML.performIntegrityChecks(Unknown Source)
    at org.hsqldb.StatementDML.insertSingleRow(Unknown Source)
    at org.hsqldb.StatementInsert.getResult(Unknown Source)
    at org.hsqldb.StatementDMQL.execute(Unknown Source)
    at org.hsqldb.Session.executeCompiledStatement(Unknown Source)
    at org.hsqldb.Session.execute(Unknown Source)
    ... 95 more

The really cryptic thing here is that the failure is taking place at a point in the test which has nothing to do with the FAVORITE_FILE table. Rather, I have observed this failure occurring when attempting to do an INSERT on a different table (OFFLINE_FILE), or when trying to delete a record from a different table.

My hope is that an expert will see this stack trace and use case, and can offer some insight as to what is happening. To reiterate, these errors do not appear when running on SQL Server. My hunch is that there is some problem/bug in the persistence layer of HSQLDB, but I cannot confirm this.

Solution

After more extensive testing we discovered that similar issues also occurred sproadically on SQL Server and Oracle. It turns out that we were not closing a transaction on the FAVORITE_FILE table. This Hibernate bug was the root cause of the error message we were getting. Closing the transaction caused the error to disappear.

This problem was a great learning experience for us because we realized that when facing errors with Hibernate we should question the correctness of our own code before questioning the database.

Answered By - Tim Biegeleisen
Answer Checked By - Robin (JavaFixing Admin)