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+):

Depending on how JIRA's install and home directories were setup, the above command may not work.  You can use the following command to bring up the 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:

hsqldb_console.png

 If you can successfully bring up the console, but no tables are shown, the jdbc:hsqldb: path 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, then clicking ok.

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