In versions of JIRA before JIRA 4.1 the jar file was in common/lib
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. |
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 ~]$ |
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 |
If you haven't already, shut down any apps using the database. |
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:
Once you have finished running SQL queries, shut down the console before starting JIRA/Confluence.
If you can successfully bring up the console, but no tables are shown, the jdbc:hsqldb: the usual reasons are
File > Connect and entering in the absolute path to JIRA_HOME/database/jiradb in the URL box and clicking Ok.
