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:
Step 2: Here you go! Download the generated PDF:
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.pdfDownload
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
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.
Please, feel free to contact us if you have any questions or suggestions. Post a comment below if you want to report a bug.