Why can't you update issues directly in the database?

Crystal Jones June 29, 2015

As we prepare for our official JIRA launch, our developers are beginning to ask questions about custom development for JIRA. I know that they should be using the JIRA API, but I do have SEVERAL developers who do not believe that they HAVE to use the API. They are INSISTING that there MUST be a way for them to add issues to the JIRA tables without having to go through the API. They are insisting that there MUST be another way to add data directly to the database tables. I need a clear answer that I can take back to them.

  • Why MUST developers use the API when creating custom interfaces?
  • Why can't they directly insert records into the JIRA tables? What is the risk if they do this??
  • Can we do some "trial" development while we're still using the HSQL evaluation database? Are there risks?

5 answers

1 accepted

Comments for this post are closed

Community moderators have prevented the ability to post new answers.

Post a new question

6 votes
Answer accepted
Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
June 29, 2015

Your developers need to learn some basic concepts such as "caching" and "indexing".  Tell them they are wrong, they don't know what they are doing and they absolutely can NOT have access to the database.  In fact, I've worked at a place which had software working in a similar way to JIRA and they simply sacked any developer who had to be told "it caches" more than once because they obviously didn't understand computers well enough to be allowed to write code for them.

Writing directly to a JIRA database bypasses the read/write of the caches and the index and hence breaks the system.  

Specifically

  • Why MUST developers use the API when creating custom interfaces?

Because it caches and indexes for them.  They don't strictly need to use the API when creating custom interfaces, but if they don't, then they need to code their own index and cache updates directly, and that means quite a lot of code inside JIRA (and they're re-inventing the wheel)

  • Why can't they directly insert records into the JIRA tables? What us the risk if they do this??

Because it caches and indexes stuff.  Direct inserts don't.  The risk ranges from "having to run reindexing while the system is locked, a lot" through to  "JIRA breaks and you lose everything"

  • Can we do some "trial" development while we're still using the HSQL database? Are there risks?

Yes, but don't write to the database.  HSQL can fail catastrophically and without warning in normal usage.  Trying to write to it while JIRA is running vastly increases that risk

 

In short, your developers are wrong.  They should use the API and stop being silly.

 

Joe Pitt
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
June 30, 2015

Well said. Too many developers think the know everything and don't know squat about how applications and databases really work in real life outside of their little test enclave.

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
June 30, 2015

... still can't +1 comments on Answers :-(

1 vote
Joe Pitt
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
June 29, 2015

The simple answer is you'll break it. I've dealt with people like that. They always think they can do whatever they want and their way is best. They will break your system beyond repair. Revoke their access to the database before they try something they "didn't think would hurt"

Crystal Jones June 30, 2015

Right.. and while I appreciate the sentiment, what I need here are details that I can take back to the developers so they UNDERSTAND WHY we're saying "no you can't do that".. I don't need to go into firefighting mode here.. No one has access to the database.. No one has done this.. We are just getting started with JIRA..

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
June 30, 2015

See the answers Monika and I have given - they have the "why" in there.

1 vote
Monika Danielsson June 29, 2015

As far as I know: JIRA seems to have a layer between the database and the actual queries, for optimization. This means JIRA might fetch things from the database before you ask for them, might allocate ids for insertion that might collide with any inserts you do directly in the database, etc, etc. As long as JIRA is solely responsible for the changes in the database (everything goes through the web interface or the API) this works fine. If the database changes without JIRA knowing about it, you might get all sorts of funny errors (such as not being able to insert data because there's already something in the database with the same id). 

For those not believing this, try snooping all queries JIRA makes against the database. This is not a realtime mirror of what you do in the JIRA. For an example, look at the ids in the jiraissues table; the ids start at a nice even 100 every time the JIRA is restarted.  

Then again, if you take down the JIRA, update the database, restart it, and do a reindex, everything should be fine if you know what you're doing. It's the on-the-fly database editing that might cause problems. 

As for risks if you insert things in the database anyway: as long as it's trial development, I assume you're not dependent on the database not becoming corrupt, so I guess it's really up to you if you think it's worth the problems. 

I am sure someone with more insights into the inside of JIRA can explain this in a better way. 

Monika Danielsson June 29, 2015

For a simple proof: Create an issue in the web interface. Then create an issue by entering it in the database (id as the previous + 1, issuenum ditto). Then try creating an issue in the web interface again. See Jira db interface complain.

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
June 29, 2015

Monika has reminded me of the other problem with direct inserts. Absolutely spot on that you *must* have the database offline (or the caches can damage your data or even crash), but also, you have to *know* what you're doing with the data. You need to *fully* understand the database. For example, "create an issue by entering it in the database" - I imagine your developers will have some stupid idea that they can just "put data in the table jiraissue". Absolutely wrong - there is an absolute *minimum* of two tables you have to update to do that, three if you do it properly, and a lot more if you want to put data in anything other than the really basic system fields. You also need to be aware of what fields *must* be filled for JIRA to handle it correctly.

0 votes
Daniel Wester
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
June 30, 2015

+1 to everything said above.  All Atlassian product databases should be treated like a black box (unless you're talking to Atlassian Support).

BUT I would probably go back to the developers and ask them what they're trying to do. They might have gotten confused with the Java api and the REST api. The REST api is a lot nicer...

0 votes
Crystal Jones June 30, 2015

"They should use the API and stop being silly"

That made me giggle as it sums up my thoughts as well.. I would love to give that as my answer, but I can't.. **lol**

The reason I asked the question is that telling developers "no you can't do that just use the API" was not working.. I KNEW that it was a no no, and I kinda knew WHY.. but I needed concrete DETAILED responses to give back to the developers, and thanks to you all here, I now have that..

Thanks Monika Danielsson and Nic Brough in particular for the longer well thought out responses. That gave me the answers I was seeking, and IS something I can take back to my devs.. I will share what I broadcast back to the developers here just so that if anyone stumbles across this thread they have the FULL picture and a COMPLETE answer to this question.. (which should be in an FAQ somewhere IMHO)

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
June 30, 2015

:-) I could have used some other words that sprung to mind, but I'm far too English to write them down! But, no, it's not a "why", the bits above it cover that. I hope those details are enough for your Devs to get it now.

Comments for this post are closed

Community moderators have prevented the ability to post new answers.

Post a new question

TAGS
AUG Leaders

Atlassian Community Events