Running SQL commands in a HSQL database

Locate the hsqldb directory

hsql stores its database in text files on the filesystem. Typically these will be in a database subdirectory of your JIRA Home Directory:

[jira-home-directory ~]$ ls -l database/
total 108
-rw-r--r--    1 jturner  jturner         0 Jul 28 09:12 jiradb.data
-rw-r--r--    1 jturner  jturner       343 Jul 28 09:12 jiradb.properties
-rw-r--r--    1 jturner  jturner     72272 Jul 28 10:02 jiradb.script
[jira-home-directory ~]$

Locate the hsqldb jar

The hsqldb binary is usually located in the lib sub-directory of your JIRA Installation Directory:

[jira-installation-directory ~]$ ls lib/hsql*
lib/hsqldb-1.8.0.5.jar

Shut down JIRA/Confluence

If you haven't already, shut down any apps using the database.

Run the console

Run the following command from the directory that contains the database directory (JIRA 4.0+):

In versions of JIRA before JIRA 4.1 the jar file was in common/lib

The hsqldb console should load, listing tables in the database in the left panel. You can run SQL commands in the top panel:

hsqldb_console.png

Once you have finished running SQL queries, shut down the console before starting JIRA/Confluence.