6

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 :)

Antti Kolehmainen
  • 1,071
  • 1
  • 11
  • 23
  • What version of HSQLDB are you using? JUnit imposes a heavy transaction rollback load on the database and you're probably running into either a bug or a known engineering tradeoff made to keep HSQLDB fast and small. Or maybe a combination of your database settings and how Eclipselink configures the identity table and manages it. Always a good idea to post version numbers. – Old Pro Apr 25 '12 at 06:46
  • I will edit the version numbers to the original post. I am using HSQLDB 2.2.8 Eclipselink 2.3.0 and OpenEJB 4.0.0-beta2. Also the Entity is not using an IDENTITY column, instead the strategy is AUTO which I think is using the TABLE strategy on HSQLDB. – Antti Kolehmainen Apr 25 '12 at 06:53
  • How much do you care about finding the problem versus working around it? Switching from TABLE (or AUTO, which is TABLE for Eclipselink) to Sequence or IDENTITY will probably get rid of the problem. Figuring out why it's happening will involve lots of painful digging through transaction and isolation and rollback settings and caching bugs and on and on. – Old Pro Apr 25 '12 at 07:06
  • That's the current solution. It just means that a 100+ entities are originally using the TABLE trategy and few exceptions that need these kinds of tests, are now IDENTITY. I would like some consistency on this part but it surely sounds like a very time-consuming problem to solve. Also changing an ID column generation strategy just for some random tests' sake sounds a bit wrong when the implementation has been working on an application server... Oh well. – Antti Kolehmainen Apr 25 '12 at 07:12
  • What happens if you use a data file instead of memory? Eg. with the connection string like this jdbc:hsqldb:file:data/db/db;shutdown=true;hsqldb.write_delay_millis=0 See also: http://stackoverflow.com/questions/8105618/entitymanager-does-not-write-to-database – anttix Mar 13 '14 at 22:22
  • Another idea: According to http://hsqldb.org/web/hsqlFAQ.html the default transaction isolation level is READ COMMITTED I would try adding hsqldb.tx_level=serializable to the connection URL to force a more conservative isolation level to see if it makes a difference. – anttix Mar 13 '14 at 22:57
  • need to wipe perhaps first? – rogerdpack Apr 02 '15 at 22:00

3 Answers3

0

Most likely, you are running out of memory while importing lots of rows into a MEMORY table.

You should increase the memory allocation or define this particular table as a CACHED table.

Update: CACHED tables can be used in persistent databases, not in all-in-memory databases:

CREATE CACHED TABLE mytable ...

or for an existing table:

SET TABLE mytable TYPE CACHED

UPDATE:

If this is not caused by OOM, as changing the generation strategy confirms, then it seems the generating strategy might not be incrementing the generated primary key value at some point. The identity strategy relies on the database to create the generated value, which works fine.

dgimenes
  • 892
  • 11
  • 23
fredt
  • 24,044
  • 3
  • 40
  • 61
  • Might be worth a try. How can I define a single table as cached in HSQLDB? – Antti Kolehmainen Apr 16 '12 at 12:14
  • 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 though. – Antti Kolehmainen Apr 17 '12 at 07:21
0

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

myqyl4
  • 301
  • 1
  • 4
  • Interestingly enough, increasing the allocationSize from 25 to, say 50 seems to solve the problem in the JUnit tests - even when creating hundreds of entities per test. It kind of sucks to make compromises because of tests. – Kimi Mar 14 '14 at 12:10
0

For integrity constraint violation: unique constraint or index violation If you're a debugger freak, you can rebuild hsqldb in debug mode and set a breakpoint in org.hsqldb.index.IndexAVLMemory#insert at a line where variable compare has been assigned with a condition on the breakpoint compare == 0.

The faulty row (the duplicate one for instance) will be the one passed as argument.

Alain Pannetier
  • 9,315
  • 3
  • 41
  • 46