About

Purpose

On rare occasions, you may wish to run raw SQL queries on a JIRA application internal database which is used for evaluation purposes. This page describes how to obtain a SQL console for HSQLDB databases, which are built into JIRA applications for evaluation purposes.

Solution

Step 1: Locate HSQLDB directory How to obtain an HSQL Console

HSQL stores its database as text files in the filesystem. Typically these files will be in a database subdirectory of your JIRA application 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 ~]$

Step 2: Locate HSQLDB jar

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

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

Step 3: Shut down your JIRA application

Step 4: Run The Console

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

Window:

java -cp "C:/Program Files/Atlassian/JIRA/lib/hsqldb-1.8.0.5.jar" org.hsqldb.util.DatabaseManager -user sa -url "jdbc:hsqldb:C:/Program Files/Atlassian/Application Data/JIRA/database/jiradb;shutdown=true"

Unix:

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

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

Step 5: Shutdown The Console

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

Troubleshooting

No tables are shown

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

• JIRA application/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.

Maybe you have an H2 embedded database (i.e. url is jdbc:h2:file:), please refer to Accessing JIRA's H2 embedded database .