HSQLDB Constraint Violation & SQL Query Log for an HSQLDB in-memory setup

Question

We have a setup where we are using an embedded HSQLDB for backing Hibernate/JPA unit tests in java, and we are using the in-memory database mode since we simply want the database thrown away after the test run. My problem is that one of the tests is failing due to a constraint violation and HSQLDB lists the column as SYS_CT_286, and the query that appears in the log is the prepared statement where I cant see what the actual parameter values are (they are replaced by '?'). My questions are:

1- Is there a way in which I can see the actual SQL being executed? (like the mysql query log for example?).

2- What exactly is SYS_CT_286? it is not one of my columns, is it a generated column? is there something obvious that may be wrong with it?

Thanks.

Answer 1

HSQLDB keeps a redo log, which might be useful for debugging what sql has been run, but I'm not sure if it does this for an in-memory db. If you change your db temporarily to a file-based db named test, the redo log should be named test.log, but it disappears on a clean shutdown.

SYS_CT_286 is most likely a constraint with a system-generated name. Again, if you make a file-based DB, you might be able to look at it and find out what it's a constraint for. And if it's a constraint you're defining, you might even be able to change your mapping so it gets a sensible name. I know you can do this with foreign key constraints anyway.

Answer 2

Is there a way in which I can see the actual SQL being executed?

I'm not sure HSQLDB allows to log the SQL statements (like select) being executed but you can use a proxy JDBC driver like P6Spy for this (already mentioned in this answer).

What exactly is SYS_CT_286?

This is a constraint (and I would bet on a unique constraint).