Run SQL commands against an HSQL database

Still need help?

The Atlassian Community is here for you.

Ask the community

About

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

This article only applies to Atlassian's server products. Learn more about the differences between cloud and server.


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:

Window:

For example: C:/Program Files/Atlassian/Application Data/JIRA/database/jiradb

Unix:
[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:
 

Window:

For example: C:/Program Files/Atlassian/JIRA/lib/hsqldb-1.8.0.5.jar

Unix:
[jira-installation-directory ~]$ ls lib/hsql*
lib/hsqldb-1.8.0.5.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_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'

In versions of JIRA prior to 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:

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 .

Description 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.
Product Jira
Platform Server


Last modified on Sep 25, 2019

Was this helpful?

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