Accessing Jira HSQL Database Manager

Thanks to Andrew

Many Atlassian products such as Confluence and JIRA come with an embedded HSQL database so that you can quickly install the products for evaluation. As the database is embedded it’s more or less invisible to the end user as you don’t have to provide usernames, passwords, connection URLs, etc., but what if you run into issues (reset admin password, change usernames, etc.) that need you to access it to directly manipulate the data?

Find the Location of your HSQL Database

  1. Log in to Confluence as an Administrator
  2. Select Browse -> Confluence Admin -> Administration -> System Information -> Database Information
  3. Make a note of the Database Connection URL:

jira-database-connection

E.g. jdbc:hsqldb:/data/confluence414/database/confluencedb;hsqldb.tx=MVCC

Locate the HSQL JAR File

  1. Change directory to your <Atlassian Product Installation Directory>/WEB-INF/lib directory, e.g. /opt/atlassian/confluence/confluence/WEB-INF/lib
  2. Make a note of the specific version of the HSQL JAR file, e.g. hsqldb-2.2.4.jar

Shutdown your Atlassian Product

  1. HSQL only supports a single connection at any one time so you need to shutdown whichever Atlassian product you’re running, e.g. /opt/atlassian/confluence/bin/shutdown.sh

OR

7.you can download database from following location /var/atlassian/application-data/database and put on another linux machine

Launch the Database Management Utility

8.Using the database location you made a note of in step 3 and the JAR file you made a note of in step 5, launch the Database Management Utility with:

java -cp <Atlassian Product Installation Directory>/WEB-INF/lib/hsqldb-2.2.4.jar org.hsqldb.util.DatabaseManager -user sa -url jdbc:hsqldb:/data/confluence414/database/confluencedb;hsqldb.tx=MVCC

E.g.

java -cp /opt/atlassian/confluence/confluence/WEB-INF/lib/hsqldb-2.2.4.jar org.hsqldb.util.DatabaseManager -user sa -url jdbc:hsqldb:/data/confluence414/database/confluencedb;hsqldb.tx=MVCC

OR

java -cp /opt/atlassian/jira/lib/hsqldb-1.8.0.5.jar org.hsqldb.util.DatabaseManager -user sa -url jdbc:hsqldb:/var/atlassian/application-data/jira/database/jiradb;hsqldb.tx=MVCC

and you should see:

jira-database

You can now execute whichever SQL statements you need to in order to fix any issues you may be having with the product.

 

Advertisements
Posted in Linux

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: