Update on jira DB

Hello,
We want to set up a jira archive instance. and switch issues from the archive to live and from live to archive.
For this we have designed a solution that requires an update in the database, specifically we want to update the ISSUENUM column (Not pkey, Not ID) in the JIRAISSUE table. we know that the updates in the database are outcast.
we did some tests, everything went well.
we wanted to ask if there is any risk in the long term ?
thank's
Best regards

3 answers

1 vote

Yes, corruption of your database is a significant risk, although the worst it will do is break the project where you do this.

I assume this is a one-off change to get you started though?  Not something you do when Jira is running? 

What do you mean by break the project ? 

The project will stop allowing issue creation, you might not be able to search it, and you'll get errors displaying project reports and boards.

What other SQL are you running as part of this update?  I ask because you might do this safely, but your question doesn't mention the other things you must do if you're going to mess with the numbers, so I'm a bit paranoid (having had to rescue so many systems where someone touched the database)

what we want to do is only restore the original key of the issues, there will be in no case two issues with the same key, and as we use jjupin we noticed that using the routine " admReindex () " on the issue, the indexes are restored and search works perfectly. 

Ok, so you're not updating the counters?

Can you confirm you are making these changes when Jira is completely offline?  And ideally after you've taken a database backup?

No, no need to updating the counters ! 

what exactly is the risk if we make the changes with jira up. because we did it and it did not cause any problem?

Best case - you lose some of your writes.  Worst case, you lose entire issues or even a project.

Never write to an active Jira database.  It's pretty much guaranteed to go wrong.

Whomever decided this was even a remotely sane approach is wrong.  Use the API to make updates.

If i understand , by offline you just want to say, no one is making any operation on jira. not that the jira service need to be down ? 

The idea is to stock the original key in a customfield and to restore it once the import done. there's no way to do it with an API or the SDK. Do you have any idea ? 

No, the Jira service/process must not be running at all.  It has to be completely shut down.

You can do this in the API, you'll need to write code in an add-on to do it.

Ok for the service,

one last question, does setnumber with setproject (https://docs.atlassian.com/software/jira/docs/api/7.2.2/com/atlassian/jira/issue/MutableIssue.html)  allow me to change the key of the issue ? 

Not change it, but you can set it.  You've said you are inserting issues which is effectively "create" where you can set it.

No, i said i will import issues and after that i will change their ISSUENUM 

Ah.  Then no.  And that's going to make a mess of your numbers if you try it in the database without updating other tables.

It sounds to me like you've come up with some very poor scheme for achieving some perceived need that doesn't sound right to me.

maybe you're right :) 

if you allow me to go back to the first question. if i want to change the ISSUENUM of an issue, let's say keyp-12 and put it to 5 knowing that there is no keyp-5 issue and after that we re-index the issue.
what is the impact on the integrity of the database knowing that ISSUENUM in the table JIRAISSUE is not a primary key or foreign key ?

I know perfectly that the changes in the database are forbidden

Jira does not use a database as a database, it uses it as a datastore.  You won't do a lot of "integrity" damage if you are using the word to mean "database integrity", but you will find a little bit sometimes.  However, you can do massive damage to the integrity of your data.  Most of the "integrity" is logical and done in the code, not the database.  When the database is not in the shape the code the expects it to be , you can lose all sorts of stuff in very interesting ways. 

I can't list the possible impacts, I've only seen a few, but I can tell you that they are absolute disasters. 

Please, have another look at what you're trying to do.

the part of the update was banned.

But I do not share your opinion, we must first understand the context and how the updates will be operated. 
if the structure and consistency of the database is correct and the indexes are updated there is no reason in IT! so that it doesn't work well.

thank you very much for your help Nic. 

Regards.

@Walid Djamaif you are going to do this you must run integrity check after.

And there is no any guarantee that you will have no problem.

Ivan

>if the structure and consistency of the database is correct 

1.  If you are doing SQL inserts into a Jira database, you must be absolutely 100% consistent.  Your approach, so far, appears to be missing things, so you fail that test.

2.  Doing this on a database when Jira is running guarantees that you will, at some point, break the consistency.  So the test fails again.

Ivan is correct too, even when you get database changes 100% correct, you must re-index the whole system and run the integrity check (indexing individual issues or even projects will cover most of it, but you really need to do a complete index after restarting)

1 vote
Claudio Ombrella Community Champion Jan 24, 2018

I wonder what is the sense of archiving projects in your instance. What you are trying to do has more than technical implications:

  1. Pay a second license of Jira.
  2. Pay for plugins
  3. Maintain 2 servers
  4. Risk of messing up the data structure in the DB.

I don't know how many issues you have in your Jira system, but in my company our Jira instance has 2.1 million issues and when we archive we do it only by applying a permission schema as full read only.

I would love to get the rational here to be able to better help.

0 vote

I would tend to agree with @Nic Brough [Adaptavist] on this one as well.  Making direct changes to SQL for Jira's database is fraught with complications that can easily cause Jira to break and not behave as expected.

If you are wanting to have two or more Jira instances and then control what issues are synced between the two instances, there already exists a number of different plugins to Jira that could help with that use case scenario.  One of these is the Issue SYNC - Synchronization for Jira.  That particular plugin will let you choose which issues/projects to sync between Jira instances.  So perhaps that might be useful here as an alternative to manipulating SQL directly to achieve your end goal.

Suggest an answer

Log in or Sign up to answer
How to earn badges on the Atlassian Community

How to earn badges on the Atlassian Community

Badges are a great way to show off community activity, whether you’re a newbie or a Champion.

Learn more
Community showcase
Published Sunday in Agility

You asked for it, so we delivered: images on issues have arrived

A picture tells a thousand words. And agility boards have just released their latest feature: cover images on issues – so now your board can tell a story at first glance. Upload attachmen...

184 views 1 11
Read article

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you