|
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 JIRA and Confluence Standalone editions use by default. Locate the hsqldb directoryhsql stores its database in text files on the filesystem. Typically these will be in a database directory in the JIRA/Confluence root: [atlassian-jira-professional-3.4.2-standalone ~]$ 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 [atlassian-jira-professional-3.4.2-standalone ~]$ In JIRA, the path is specified in conf/server.xml as '${catalina.home}/database/jiradb', where ${catalina.home} will be the directory atlassian-jira-professional-3.4.2-standalone in this instance. Locate the hsqldb jarThe hsqldb binary is usually located in the common/lib/ directory: [atlassian-jira-professional-3.4.2-standalone ~]$ ls common/lib/hsql* common/lib/hsqldb-1.7.1-patched.jar Shut down JIRA/ConfluenceIf you haven't already, shut down any apps using the database. Run the consoleRun the following command (JIRA 3.7+): java -cp common/lib/hsqldb-1.8.0.5.jar org.hsqldb.util.DatabaseManager -user sa -url jdbc:hsqldb:database/jiradb or this for JIRA 3.6.5 and earlier: java -cp common/lib/hsqldb-1.7.1-patched.jar org.hsqldb.util.DatabaseManager -user sa -url jdbc:hsqldb:database/jiradb 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
Except where otherwise noted, content in this space is licensed under a Creative Commons Attribution 2.5 Australia License.
Comments (2)
Dec 20, 2006
Neal Applebaum says:
I tried doing this on my 3.6.5 database and it failed (Out of memory). I followe...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.
May 18, 2007
Dave van 't Veld says:
You may want to add to the 'Run the console' section that the command needs to b...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.
Add Comment