Running SQL commands in a HSQL database

On rare occasions, one may wish to run raw SQL queries on a JIRA or Confluence database. This page describes how to obtain a SQL console for hsqldb databases, which JIRA and Confluence Standalone editions use by default.

Locate the hsqldb directory

hsql stores its database in text files on the filesystem. Typically these will be in a database directory in the JIRA/Confluence root:

[atlassian-jira-professional-3.4.2-standalone ~]$ 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
[atlassian-jira-professional-3.4.2-standalone ~]$

In JIRA, the path is specified in conf/server.xml as '${catalina.home}/database/jiradb', where ${catalina.home} will be the directory atlassian-jira-professional-3.4.2-standalone in this instance.

Locate the hsqldb jar

The hsqldb binary is usually located in the common/lib/ directory:

[atlassian-jira-professional-3.4.2-standalone ~]$ ls common/lib/hsql*
common/lib/hsqldb-1.7.1-patched.jar

Shut down JIRA/Confluence

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

Run the console

Run the following command (JIRA 3.7+):

or this for JIRA 3.6.5 and earlier:

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