I don’t think that I can use HSQLDB?

I just wrote a blog posting last night and given the comments I think I should have wrote this one first.

On the project I’m working on we use HSQLDB when developing, but I ran into some issues yesterday that makes me reconsider using it at all.

Basically, I was doing some acceptance testing for a feature I was working on that required me to verify an update in the database.

After updating a record on the GUI and hitting save, a query to HSQLDB still showed the old identifier value of 1111122222

What happened to my update?

If I look into the HSQL files directly I can see the update in the testdb.log file but not in the testdb.script file:

Here’s the actual update in the testdb.log notice the update to the new identifier 1111122222333

It appears as though HSQLDB is caching some of it’s operations in the .log file and synchronizing them with the .script file at some later time? (Although I’m no expert?)

I don’t really want to dig into this .log file every time I want to confirm my application is doing the right thing! So I played around with some settings to try to force the sync.

I ran into this comment on the HSQLDB FAQsite:

After the program is finished, almost all statements are in the *.log file, and the *.data file is almost empty. Why?

The database was not shut down properly. When you restart the database, the *.log file will be processed and an automatic checkpoint will be performed. No committed data will be lost. To avoid this, use the SQL command "SHUTDOWN" when your application has finished with the database.

The statements that make up the database are saved in the *.script file (mostly CREATE statements and INSERT statements for memory tables). Only the data of cached tables (CREATE CACHED TABLE) is stored in the *.data file. Also all data manipulation operations are stored in the *.log file (mostly DELETE/INSERT) for crash recovery. When the SHUTDOWN or CHECKPOINT command is issued to a database, then the *.script file is re-created and becomes up-to-date. The .log file is deleted. When the database is restarted, all statements of the *.script file are executed first and new statements are appended to the .log file as the database is used.
  • This gave me hope…maybe I can run the CHECKPOINTcommand to initiate the synchronization between the .log and .script files?:
    • Turns out I can execute this command from the HSQLDB client

Unfortunately this didn’t work! It did clear out the .log file but the script file wasn’t updated it just had the old value 1111122222

    • Maybe there’s some corruption in that .log file for some reason? Notice all the nul nul nul’s in the screenshot above?

Here’s a few more things I tried:

  • Setting “shutdown=true”in my jdbc url
    • This did sync more often but this particular update still never showed up in the hsqldb file.
      • Worse! The performance was terrible in my app
  • Setting “write_delay=false”in my jdbc url:
    • This did make writing to the .log file quick but didn’t seem to effect the .script sync at all.

Not sure if there’s something that someone can suggest but this coupled with a few of the comments I have encountered make me question whether there’s a win to using HSQLDB in development.

It's only fair to share...
Share on Facebook
Tweet about this on Twitter
Share on LinkedIn

Leave a Reply