Run SQL commands against an HSQL database
Do not use an HSQL database for production. While this database is suitable for evaluation purposes, it is susceptible to data loss during system crashes. If you are looking to connect to a H2 embedded database, please refer to Accessing JIRA's H2 embedded database
Platform Notice: Server and Data Center Only. This article only applies to Atlassian products on the server and data center platforms.
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.
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:
For example: C:/Program Files/Atlassian/Application Data/JIRA/database/jiradb
[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:
For example: C:/Program Files/Atlassian/JIRA/lib/hsqldb-188.8.131.52.jar
[jira-installation-directory ~]$ ls lib/hsql* lib/hsqldb-184.108.40.206.jar
Step 3: Shut down your JIRA application
If you haven't already, shut down any apps using the database.
Step 4: Run The Console
Use the following command to bring up the HSQLDB console (replacing the
JIRA_INSTALL with the absolute paths)
java -cp "C:/Program Files/Atlassian/JIRA/lib/hsqldb-220.127.116.11.jar" org.hsqldb.util.DatabaseManager -user sa -url "jdbc:hsqldb:C:/Program Files/Atlassian/Application Data/JIRA/database/jiradb;shutdown=true"
java -cp JIRA_INSTALL/lib/hsqldb-18.104.22.168.jar org.hsqldb.util.DatabaseManager -user sa -url 'jdbc:hsqldb:JIRA_HOME/database/jiradb;shutdown=true'
In versions of JIRA prior to JIRA 4.1 the jar file was in
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.
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 > Connectand entering in the absolute path to
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 .