How can I connect to the database that the JIRA standalone installs?

Question

I installed JIRA using the standalone installer (not from the war distribution). As I understand it the standalone version installs a database and all deps at once. Now I need to get access to that database but I dont know what the default db user and password and I am not sure how to connect to it. I have access to the machine running JIRA as root. Any ideas?

Answer 1

The accepted answer doesn't give actual instructions and the link has moved.

A more complete answer would have saved me time, so I'm adding a detailed answer just in case it helps someone else:

1. Find the database directory
   • "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"
   • i.e. path/to/JIRA/HOME/database
   • Note: the file jiradb.script will be located in this directory so you can try just searching for that file. On Mac/Unix: find . -name "*jiradb.script"
2. Shutdown JIRA if it is running
   • while JIRA runs, it locks the DB so you have to kill the server, first
   • to do this, simply run path/to/JIRA/bin/stop-jira.sh
3. Run the HSQLDB jar
   • this is located in the /lib folder (i.e. path/to/JIRA/lib)
   • use the following command to run the jar (all on one line):

     java -cp lib/hsqldb-1.8.0.5.jar org.hsqldb.util.DatabaseManager -user sa -url jdbc:hsqldb:HOME/database/jiradb

   • note that the /HOME and /lib folder both live at the root of the JIRA installation. So to get this working I had to cd to that the root directory, first, which for me was something like /Users/mymachine/Downloads/atlassian-jira-6.4.9-standalone
   • thejiradb seen in the command above simply tells the DatabaseManager class which file prefix to use. The actual database lives in jiradb.script

Command-line only

If you don't have access to a graphical environment, such as on a headless Unix system, then step 3 above will not work. The command will complain that the DISPLAY variable is unset, or that it cannot connect to X11 instance.

Here's an alternate HSQLDB command, which will work in a non-graphical setting:

1. Download the latest zip file. This contains lib/sqltool.jar, lib/hsqldb.jar, and sample/sqltool.rc, all of which you will need.

2. Set up an rc file with an appropriate connection string (named jira in my example). The personal entry from the sample rc file is a good starting point: just change the filepath to the location of the jira database.

   urlid jira
   url jdbc:hsqldb:file:${user.home}/tmp/jiradb;shutdown=true
   username SA
   password
   transio TRANSACTION_READ_COMMITTED
   

3. Run sqltool.jar instead of hsqldb.jar. You can now run SQL commands against this database

       $ java -jar sqltool.jar jira
   

Answer 2

The DB that comes with JIRA is an HSQL DB. It can be accessed using an HSQL console. You can find instructions in JIRA's wiki. http://confluence.atlassian.com/display/JIRA/Running+SQL+commands+in+a+HSQL+database