Running SQL commands in a HSQL database
On rare occasions, one may wish to run raw SQL queries on a JIRA or Confluence 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 and Confluence for evaluation purposes.
Do not use HSQL database for production. While this database is suitable for evaluation purposes, it is susceptible to data loss during system crashes.
Locate HSQLDB directory
HSQL stores its database as text files in the filesystem. Typically these files 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 HSQLDB jar
The hsqldb jar file is located in the lib sub-directory of the JIRA Installation Directory:
[jira-installation-directory ~]$ ls lib/hsql*
lib/hsqldb-1.8.0.5.jar
Shutdown JIRA/Confluence
If you haven't already, shut down any apps using the database.
Run The Console
Use the following command to bring up the HSQLDB 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:
Shutdown The Console
Once you have finished running SQL queries, shut down the console before starting JIRA/Confluence.
Troubleshooting
If you can successfully bring up the console, but no tables are shown, the jdbc:hsqldb: the usual reasons are
- JIRA/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 toJIRA_HOME/database/jiradbin theURL box and clicking Ok.
