Tests throw exception saying user lacks privilege or object not found: REFERENTIAL_INTEGRITY after update HSQLDB to 2.0.0

Question

Recently I upgraded my maven HSQL dependency to org.hsqldb:hsqldb:2.0.0 from version 1.8.1.2. However all the tests that used HSQLDB failed, with an exception saying:

org.hsqldb.HsqlException: user lacks privilege or object not found: REFERENTIAL_INTEGRITY

I googled it and found a few answers but I found them hard to understand. How can I fix my tests? Before the upgrade my tests worked OK.

Answer 1

This refers to the statement

SET REFERENTIAL_INTEGRITY { TRUE | FALSE }

This statement has changed to

SET DATABASE REFERENTIAL INTEGRITY { TRUE | FALSE}

Once released on Maven, you should use HSQLDB 2.1 as this has bug fixes and better backward compatibility. In the meantime you can use the latest 2.1 RC4 or the jar snapshots from http://hsqldb.org/support/