Blog / 2010 / October / 27

Upgrading hsql databases from 1.8.0.4 to 2.0.0

October 27, 2010

We had a problem upgrading a customer database from hsqldb 1.8.0.4 to 2.0.0. This process had worked previously, but this particular customer was having trouble.

Bringing the database up resulted in the following error:

[Server@1d05c81]: [Thread[main,5,main]]: checkRunning(false) entered
[Server@1d05c81]: [Thread[main,5,main]]: checkRunning(false) exited
[Server@1d05c81]: Startup sequence initiated from main() method
[Server@1d05c81]: Loaded properties from [C:\path\to\server.properties]
[Server@1d05c81]: Initiating startup sequence...
[Server@1d05c81]: Server socket opened successfully in 16 ms.
[Server@1d05c81]: [Thread[HSQLDB Server @1d05c81,5,main]]: Database [index=0, db
=file:dashboard, alias=dashboard] did not open: org.hsqldb.HsqlException: error
in script file line: 2 invalid schema name: SYSTEM_SCHEMA in statement [CREATE M
EMORY TABLE SYSTEM_USERS(SERIAL INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH
0) NOT NULL PRIMARY KEY,VERSION INTEGER DEFAULT 0 NOT NULL,USERCODE VARCHAR(50)
NOT NULL,NAME VARCHAR(255),EMAIL VARCHAR(255),USER_PICTURE_URL VARCHAR(1024),PA
RENT_ID INTEGER,SURNAME VARCHAR(255),FORENAMES VARCHAR(255),ROLE INTEGER NOT NULL)]
[Server@1d05c81]: [Thread[HSQLDB Server @1d05c81,5,main]]: Shutting down because
 there are no open databases
[Server@1d05c81]: Initiating shutdown sequence...
[Server@1d05c81]: Shutdown sequence completed in 0 ms.
[Server@1d05c81]: 2010-10-27 13:32:03.122 SHUTDOWN : System.exit() is called next

The key part of the error is:

error in script file line: 2 invalid schema name: SYSTEM_SCHEMA

Looking at the .script file, it was still in hsql 1.8.0.4 format, not the new 2.0.0 format.

The problem was caused because we didn't copy the database's .properties file with the .script file. The .properties file specified that the file was a version 2.0.0 file, and so the database didn't attempt to upgrade it.

The problem was fixed by copying the database's .properties file in from the 1.8.0.4 install at the same time as copying the .script file. There was no .log file, as we had run a checkpoint and shutdown before upgrading - if you don't do this the database can't be upgraded.

Tags: hsqldb upgrade