Search the JIRA 5.0.x Beta and RCs Documentation:

Index
Downloads (PDF, HTML & XML formats)
Other versions

This documentation relates to JIRA 5.0.x Beta and RCs only.
The latest official version is JIRA 4.4.x
If you are using JIRA 4.4.x either view this page in the JIRA 4.4.x documentation or visit the JIRA 4.4.x documentation home page.
Skip to end of metadata
Go to start of metadata
On rare occasions, one may wish to run raw SQL queries on a JIRA or Confluence database. This page describes how to obtain a SQL console for hsqldb databases, which are built into JIRA and Confluence for evaluation purposes.

Locate the hsqldb directory

hsql stores its database in text files on the filesystem. Typically these 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 the hsqldb jar

The hsqldb binary is usually located in the lib sub-directory of your JIRA Installation Directory:

[jira-installation-directory ~]$ ls lib/hsql*
lib/hsqldb-1.8.0.5.jar

Shut down JIRA/Confluence

If you haven't already, shut down any apps using the database.

Run the console

Run the following command from the directory that contains the database directory (JIRA 4.0+):

java -cp lib/hsqldb-1.8.0.5.jar org.hsqldb.util.DatabaseManager -user sa -url jdbc:hsqldb: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.

Labels:
None
Edit Labels

7 Comments

Hide/Show Comments

  1. Dec 20, 2006

    Neal Applebaum

    I tried doing this on my 3.6.5 database and it failed (Out of memory). I followed the lead of the Anonymizer syntax, and used this instead:

    java -Xms128m -Xmx256m -DentityExpansionLimit=1200000 -cp common/lib/hsqldb-1.7.1-patched.jar org.hsqldb.util.DatabaseManager -user sa -url jdbc:hsqldb:database/jiradb

    i.e. - added "-Xms128m -Xmx256m -DentityExpansionLimit=1200000"

    That worked! Hope it helps someone else with the same problem.

  2. May 18, 2007

    Dave van 't Veld

    You may want to add to the 'Run the console' section that the command needs to be run from within the installation directory. I'm running this on XP Pro as a stand alone.

    I've just created a batch file for ease-of-use. To create your own, start a text file in Notepad, paste in:

    CLS
    CD\
    CD C:\Program Files\JIRA-Professional-3.9
    java -cp common/lib/hsqldb-1.8.0.5.jar org.hsqldb.util.DatabaseManager -user sa -url jdbc:hsqldb:database/jiradb

    Replace C:\Program Files\JIRA-Professional-3.9 with your own installation directory (no quotes needed) and make sure that you're referencing the right jar file (JIRA 3.7+). Save as a .bat file and stick a shortcut to it in your start menu.

  3. Mar 01, 2010

    Nic Brough

    Version 4, on Windows, I couldn't get this to work the way it used to under 3.

    Needed Neals switches to up the amount of memory (medium sized Jira here - 50k issues), and then it opens a blank page, looks like it doesn't understand the last paramenter

    However, you can

    1. Select "connect"
    2. Change it to a file based database
    3. edit the connection string, removing the braces and replacing database/file with database/jiradb

    I'm sure it's just to do with the parameter being passed in.

    1. Oct 26, 2010

      Raju Prasad

      I tried with the steps you mentioned here, but I didn't get any issue table or project table, rather than those table I got item table, product table etc.

  4. Oct 28, 2010

    Anonymous

    Hi,

    I want to know , is there any shortcut key to execute a query wihout clicking on the Execute 

    button.

    Please let me know soon.

    Thanks in advance .

    1. Jun 18, 2011

      Anonymous

      CTRL+Enter works for me.

    2. Dec 19, 2011

      Shaun Clowes [Atlassian]

      Please note that Ctrl-J is required on Mac OS X in order to execute the SQL statement from the keyboard