Running SQL commands in a HSQL database

Locate HSQLDB directory

HSQL stores its database as text files in the filesystem. Typically these files 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 HSQLDB jar

The hsqldb jar file is located in the lib sub-directory of the JIRA Installation Directory:

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

Shutdown JIRA/Confluence

Run The Console

Use the following command to bring up the HSQLDB console (replacing the JIRA_HOME and JIRA_INSTALL with the absolute paths)

java -cp JIRA_INSTALL/lib/hsqldb-1.8.0.5.jar org.hsqldb.util.DatabaseManager -user sa -url jdbc:hsqldb:JIRA_HOME/database/jiradb

 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:

Shutdown The Console

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

Troubleshooting

If you can successfully bring up the console, but no tables are shown, the jdbc:hsqldb: the usual reasons are

• JIRA/Confluence is still running. Ensure that the applications are shutdown before running the console.
• Path of the database file may be incorrect.  You can update this by navigating to File > Connect and entering in the absolute path to JIRA_HOME/database/jiradb in the URL box and clicking Ok.