Running SQL commands in a HSQL database

On this page

Still need help?

The Atlassian Community is here for you.

Ask the community

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.

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

(info) 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 to JIRA_HOME/database/jiradb in the URL box and clicking Ok.

Last modified on Mar 11, 2013

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.