Java – HSQLDB primary key violation error in JUnit tests

eclipselinkhsqldbjakarta-eejavajunit

We have a testing framework using JUnit, OpenEJB, Eclipselink and HSQLDB. Everything has worked fine so far, and testing the service-tier is a breeze. Now however, we are running into problems when doing mass imports on a table (using the service-tier,entitymanager) or for example persisting entities to a list multiple times in a service method.

THIS IS THE WEIRD PART: Our tests seem to only break if tests are run on a fast enough workstation from the command line with Maven. When I run the tests through Eclipse IDE, everything is fine but sometimes, randomly, it also fails. We suspect it might have something to do with the speed the tests are run with, as weird as it sounds. The exception is simple enough because basically it tells us we are trying to add an entity with an already existing id. We have multiple times checked our test data and the hsqldb database. There are no pre-existing rows with id's we are trying to use. Still hsqldb throws the primary key exception at some point. From our logs we can see that the conflicting ID is not always the same, it might be 300015 or 300008.

We are at our wit's end here. Could it have something to do with HSQLDB's transactions or something else causing stale data?

We are using HSQLDB 2.2.8, Eclipselink 2.3.0 and OpenEJB 4.0.0-beta2.

The relation we are trying to add entities to is mapped as following:

@OneToMany(mappedBy = "invoice", cascade = CascadeType.PERSIST)
private List<InvoiceBalance> getInvoiceBalanceHistory() {
    if (invoiceBalanceHistory == null) {
        this.invoiceBalanceHistory = new ArrayList<InvoiceBalance>();
    }
    return invoiceBalanceHistory;
}

The root exception is:

Caused by: java.sql.SQLIntegrityConstraintViolationException: integrity constraint violation: unique constraint or index violation; SYS_PK_10492 table: INVOICEBALANCE
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCPreparedStatement.fetchResult(Unknown Source)
at org.hsqldb.jdbc.JDBCPreparedStatement.executeUpdate(Unknown Source)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:831)
... 82 more
Caused by: org.hsqldb.HsqlException: integrity constraint violation: unique constraint or   index violation; SYS_PK_10492 table: INVOICEBALANCE
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.Constraint.getException(Unknown Source)
at org.hsqldb.index.IndexAVLMemory.insert(Unknown Source)
at org.hsqldb.persist.RowStoreAVL.indexRow(Unknown Source)
at org.hsqldb.TransactionManager2PL.addInsertAction(Unknown Source)
at org.hsqldb.Session.addInsertAction(Unknown Source)
at org.hsqldb.Table.insertSingleRow(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)

EDIT:

I changed the primary key generation strategy from GenerationType.AUTO (that seems to use the TABLE-strategy by default) to IDENTITY. After this, our mass persists seem to work without fail. I still don't know why HSQLDB goes "out of sync" with the TABLE-strategy. I wouldn't want to change our jpa entities just because our testing framework is buggy 🙂

Best Answer

It might be possible that your allocationSize is defining a bottleneck on relatively fast platforms or occasionally. i.e. When defaulted to GenerationType.AUTO which defaulyts to table EclipseLink will cache ID upto the allocated value. It will then look up generator to confirm its last allocated value. If a lookup happened around the edge of the allocationSize before the next set of ID is cached, then you might run into a race condition where eclipse link allocates the last id in the cache twice before it updates the cache and tries to use both for insert and both inserts fail and are rolledback. If you can you should check to see if this happens around when your allocation cache should be incremented, but perhaps that kind of check might change the behaviour

Related Topic