Running SQL commands in a HSQL database
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 > Connect
and entering in the absolute path toJIRA_HOME/database/jiradb
in theURL box and clicking Ok.