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 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 lib directory:

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

This is for JIRA 3.6.5 and earlier:

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

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