HSQLDB Database Files and Recovery

The Standalone and Client/Server modes will in most cases use files to store all data to disk in a persistent and safe way. This document describes the meaning of the files, the states and the procedures followed by the engine to recover the data.

A database named 'test' is used in this description. The database files will be 'test.script', 'test.properties', 'test.data' and 'test.backup'.

Database Files

• test.properties
  Contains the entry 'modified'. If the entry 'modified' is set to 'yes' then the database is either running or was not closed correctly (because the close algorithm sets 'modified' to 'no' at the end).
• test.script
  This file contains the SQL statements that makes up the database up to the last checkpoint - it is in synch with test.backup
• test.data
  This file contains the (binary) data records for CACHED tables only
• test.backup
  This is compressed file that contains the complete backup of the old test.data file at the time of last checkpoint.
• test.log
  This file contains the extra SQL statements that have modified the database since the last checkpoint (something like the 'Redo-log' or 'Transaction-log', but just text)

  In the above list, a checkpoint results from both a CHECKPOINT command and a SHUTDOWN command.

States

Database is closed correctly

This is the state after using the command, SHUTDOWN

• The test.data file is fully updated
• The test.backup contains the compressed test.data file
• The test.script contains the information in the database, excluding data for CACHED and TEXT tables.
• The test.properties contains the entry 'modified' set to 'no'
• There is no test.log file

Database is closed correctly with SHUTDOWN SCRIPT

This is the state after using the command, SHUTDOWN SCRIPT

• The test.data file does not exist; all CACHED table data is in the test.script file
• The test.backup does not exist.
• The test.script contains the information in the database, including data for CACHED and TEXT tables.
• The test.properties contains the entry 'modified' set to 'no'
• There is no test.log file

Database is aborted

This may happen by sudden power off, Ctrl+C in Windows, but may be simulated using the command SHUTDOWN IMMEDIATELY.

• The test.properties still containes 'modified=yes'
• The test.script contains a snapshot of the database at the last checkpoint and is OK
• The test.data file may be corrupt because the cache in memory was not written completely.
• The test.backup file contains a snapshot of test.data that corresponds to test.script
• The test.log file contain all information to re-do all changes since the snanapshot. As a result of abnormal termination, this file may be partially corrupt.

Procedures

Database is closed correctly

• The test.data file is written completely
• The test.backup.new is created (containing the compressed test.data file)
• The file test.script.new is created using the information in the database (and thus shrinks because no 'update' and 'delete' statements; only simple 'inserts').
• The entry 'modified' is set to 'yes-new-files'
• The file test.script is deleted
• The file test.script.new is renamed to test.script
• The file test.backup is deleted
• The file test.backup.new is renamed to test.backup
• The entry 'modified' is set to 'yes'
• The file test.log is deleted

Database is opened

• Check if the database files are in use (by checking a sepecial test.lck file).
• See if the test.properties file exists, otherwise create it.
• Check in the test.properties file if 'modified=yes'. This would mean last time it was not closed correctly, and thus the test.data file may be corrupted or incomplete. In this case the 'REPAIR' algorithm is executed (see below) otherwise
• If in the test.properties file 'modified=yes-new-files', then the (old) test.backup and test.script files are deleted and the new test.script.new file is renamed to test.script.
• Open the test.script file and execute the commands
• Create the empty test.log to append new commands

Repair

The current test.data file is corrupt, but with the old test.data (from the test.backup file and test.script) and the current test.log, the database is made up-to-date. The database engine takes these steps:

• Restore the old test.data file from the backup (uncompress the test.backup)
• Execute all commands in the test.script file.
• Exedute all commands in the test.log file. If due to corruption, an exception is thrown, the rest of the lines of command in the test.log file are ignored.
• Close the database correctly (including a backup).

This text is based on HypersonicSQL documentation, updated to reflect the latest version 1.7.2 of HSQLDB