CODEWITHSUNDEEP
javahsqldb
NPras
NPras

Reputation: 4125

HSQLDB times out upgrading large DB from 1.8 to 2.4.1

So I've inherited a legacy application, running on HSQLDB 1.8.0.10 which was having the occasional recovery issue after power losses. With all the improvements to stability and persistence in the last 10 years, I am looking at upgrading it to the latest 2.4.1.

I have multiple databases to test on. From a few KBs to the largest being 2GB in size.

Serving up the second largest DB which sits at 270MB (unfortunately, nearly one-tenth of the next size up) posed no problems; Starting the server on the 2GB file however, caused HSQL to just sit there forever attempting to load the file.

This wasn't an issue on v1.8, which happily started in mere seconds. On v2.4 I've waited up to 10 minutes and gave up (even if it did work in 10m, it's still pretty much unacceptable to have the server take that long to start).

I tried turning on the trace:

HsqlProperties p = new HsqlProperties();
p.setProperty("server.database.0", "db/TheDb");
p.setProperty("server.dbname.0", "TheDb");
p.setProperty("server.port", port);
p.setProperty("server.silent", "false");
p.setProperty("server.trace", "true");
Server server = new Server();
server.setProperties(p);
server.start();

Resulting in the following:

: run() entered
  ...
: server.root=.
: openServerSocket() entered
: Got server socket: ServerSocket[addr=0.0.0.0/0.0.0.0,localport=37011]
: Server socket opened successfully in 6 ms.
: openServerSocket() exiting
: openDatabases() entered
: Opening database: [file:db/TheDb]
// Stuck here forever

The DB consists of the following files:

$ ls
 417028772  TheDb.backup
2147483608  TheDb.data
    430165  TheDb.log
       444  TheDb.properties
    196938  TheDb.script

And the .properties file is currently as follows:

#HSQL Database Engine 1.8.0.10
hsqldb.script_format=0
runtime.gc_interval=0
sql.enforce_strict_size=false
hsqldb.cache_size_scale=8
readonly=false
hsqldb.nio_data_file=true
hsqldb.cache_scale=14
version=1.8.0
hsqldb.default_table_type=cached
hsqldb.cache_file_scale=1
hsqldb.log_size=200
modified=no
hsqldb.cache_version=1.7.0
hsqldb.original_version=1.8.0
hsqldb.defrag_limit=50
hsqldb.compatible_version=1.8.0

No properties are set in the .script file. (with the other successful upgraded DBs I did notice that props are mostly moved away from .properties and into .script)

I was wondering if anyone else had performed a similar feat, and/or seen a similar behaviour with large pre-v2 files. Is there a way to have more visibility in the trace to identify problems when loading the data file?

Thanks in advance.

Upvotes: 1

Views: 321

Answers (1)

fredt
fredt

Reputation: 24372

It is recommended to perform SHUTDOWN SCRIPT with version 1.8 before opening the database with version 2.x of the database engine. The data file is regenerated in version 2.x format with this method. This takes a long time but future startups will be fast.

Upvotes: 1

Related Questions