JIRA and Confluence Database Schemas

/, How To, JIRA/JIRA and Confluence Database Schemas

Here you can find the database schemas of Atlassian JIRA 7.x and Confluence 6.x.

The GUI of JIRA and Confluence does not provide access to everything and does not show everything stored in databases. Sometimes you may get some unknown errors/exceptions in JIRA and Confluence logs, to troubleshoot them effectively you need to dive deeper than the GUI allows. That’s why you need to be aware of how the database schema looks like to perform direct queries against a database. Also it is quite useful to extract more details to understand how to improve performance and clean out stale things.

JIRA database schema

Follow the instructions from the official kb to generate your current database schema of JIRA. The database schema provided on Atlassian website does not contain a lot of important tables, for example, the table AO_60DB71_RAPIDVIEW which contains the data about boards, you can find below the instruction how to get the full schema of your JIRA instance.

Download

Install GraphViz to get the schema in PDF:

root@svelab:~# apt-get -y install graphviz
root@svelab:~# which dot
/usr/bin/dot

Install the jira-schema-diagram-generator-plugin plugin:

Follow instructions from the official kb.

Generate schema diagram:

To get a full database schema in PDF you have to remove all data from the Ignored Tables and Ignored Columns fields and provide an absolute path to GraphViz (in case of Debian, it is /usr/bin/dot).

Step 1: Remove all data from all fields, provide an absolute path to GraphViz, and click the Generate Schema button:
Generate JIRA Schema
Step 2: Here you go! Download the generated PDF:
JIRA database schema

Another way to get the database schema is to use utilities like mysqlviz:

apt-get -y install graphviz
wget https://github.com/globalcitizen/mysqlviz/archive/master.zip -O /var/tmp/mysqlviz.zip
cd /var/tmp
unzip mysqlviz.zip
cd /var/tmp/mysqlviz-master
mysqldump -u root -p --max_allowed_packet=1G jira_db > jira_$(date +"%Y-%m-%d").sql
./mysqlviz -f jira_$(date +"%Y-%m-%d").sql > jira_schema.dot
dot -Tpdf jira_schema.dot > jira_schema.pdf
Download

Confluence database schema

Official Confluence kb contains a truncated database schema, so better to use tools like mysqlviz (see above) to extract it. Below is the extracted Confluence database schema.

Download

Troubleshooting

To troubleshoot an issue, you need get the dump of a database and then parse it to find out where a value sits which you are looking for. Once you got it, go to your database and try to find out all dependencies, after that you should raise a ticket with Atlassian and ask them how can you safely remove/fix stale things.

See also

JIRA Boards Extractor
Confluence Page Views Extractor

Contact us

Please, feel free to contact us if you have any questions or suggestions. Post a comment below if you want to report a bug.

By |2018-04-28T21:48:09+00:00September 24th, 2017|Confluence, How To, JIRA|0 Comments

Leave A Comment