Database table definitions

[Solitonman]

I'm wondering if there is a reference for the database tables in JIRA. I'd like to understand what I'm looking at when I'm searching on tables, and how the various fields relate tables to each other.

Specifically I'm interested in the SECURITY field in the jiraissue table. We're seeing a problem wherein NO ONE can view a set of issues, and they are in an open status, unresolved, with a SECURITY value of 10001. Kind of clueless here as to what that means. We get a Permission Violation error when trying to access the issues. But when I force the SECURITY field to NULL, the issues are viewable just fine.

If anyone can direct me to a database reference or has some info on the issue I've described, I'd be grateful to hear about it.

[Nic Brough -Adaptavist- · Accepted solution]

It's better not to mess with the database. The security field on the issues is telling you that there is an issue level security scheme in place, and it's that which is preventing you seeing them.

You just need to amend the security scheme, open it up to allow your users more access. See http://confluence.atlassian.com/display/JIRA/Configuring+Issue+Level+Security

[Solitonman]

Hi Nic, thanks for the reply. Unfortunately this isn't the case, there is no issue security scheme set for this project. I doubt we'll ever really understand how the issues have reached this state, our JIRA installation has been configured and re-configured many times over the last three years. These issues date from 2008, so I'm closing them in the database and will tweak whatever I have to until I can view them again. Hopefully once viewable I'll be able to edit them normally - my initials tests seem promising.

[Nic Brough -Adaptavist-]

Nevertheless, that's the problem - it sounds like someone has removed the issue security scheme in the database, and I really do mean "it's better not to mess with the database".

You mention "closing" the issues by using database access - you do understand that you have to amend doezens fo tables, while the database is offline, in order to do this and it's very easy to get wrong?

If you must mess with the database, keep it to a minimum - strip the security levels off and do NOT do anything else with it. Bear in mind to execute any SQL changes, you really do need Jira offline, and you shoudl re-index afterwards, before doing anything else.

[Solitonman]

I found that by setting the SECURITY field in the jiraissue table from 10001 to NULL for the specific issues in question, I was able to gain access to the issues in the JIRA interface and handle them from there. But this kind of brings me back to my original question, which is about getting database documentation that describes all of the tables, fields and relationships. Do you know if anything like that exists?

[Nic Brough -Adaptavist-]

Oh, sorry, yes, in the standard docs, http://confluence.atlassian.com/display/JIRA041/Database+Schema . It's not complete, but that's good enough for most, because it changes often enough not to be worth being complete, and frankly, if you think you need a full definition, you're probably trying to do something WAY too complex.