Update on jira DB

[Walid Djama]

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

[Claudio Ombrella]

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.

[Nic Brough -Adaptavist-]

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? 

[Walid Djama]

What do you mean by break the project ? 

[Nic Brough -Adaptavist-]

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.

[Nic Brough -Adaptavist-]

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)

[Walid Djama]

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. 

[Nic Brough -Adaptavist-]

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?

[Walid Djama]

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?

[Nic Brough -Adaptavist-]

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.

[Walid Djama]

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 ? 

[Nic Brough -Adaptavist-]

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.

[Walid Djama]

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 ? 

[Nic Brough -Adaptavist-]

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

[Walid Djama]

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

[Nic Brough -Adaptavist-]

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.

[Walid Djama]

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 ?

[Walid Djama]

I know perfectly that the changes in the database are forbidden

[Nic Brough -Adaptavist-]

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.

[Walid Djama]

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.

[Ivan Punko]

@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

[Nic Brough -Adaptavist-]

>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)

[Walid Djama]

Hi,
Sorry for the long time.

just to know, i developed a plugin to restore the original keys of the issues by using the method "setNumber" which is in the library "com.atlassian.jira.issue.MutableIssue".

In the first time i saved the original keys into a custom field before importing issues in archive server.

there is some screenshot:

I remain available for more information.

Regards.

[TIS Admin]

Hi Walid Djama,

I'm really interesting in your solution. Could you share more details with me ?

My use case: migrating projects from multiple Jira Cloud instances to a central Jira Cloud instance. I have to keep the history, so simple export/import doesn't work. Unfortunately, there were few next-gen projects, so according to Atlassian articles, I had to create new classic projects first, move the issues from next-gen to classic, otherwise no chance to make a backup and continue the migration. Obviously, it changed the ISSUENUM during the move process. Fortunately, I have a mapping of old ISSUENUM to new ISSUENUM, and I have a local Jira SERVER where I restored the Cloud backup and can manipulate the ISSUENUM before uploading to the destination Jira cloud. 

Why restoring the ISSUENUM is so critical? Jira is not the only tool in the development process. e.g. Bitbucket is tightly integrated. The commits, pull requests, etc is using the issue key reference. In my particular case, wrong commits are displayed in the Issue Detail screen (it's based on the old issue keys), the issue keys in the pull requests (old) lost the reference too, so it's a showstopper for us. 

Really appreciate your updates!

[Nic Brough -Adaptavist-]

Walid was doing bad things to a Server instance.  You don't have access to do these bad things to Cloud systems.

[TIS Admin]

Nic, with my full respect, please focus on the use case and respect others. Try to provide better option and move the ball forward instead of repeating this mantra. Believe me, I have been looking this issue from hundreds of views, I read the full story, having constant discussion with support and this is the final option to do. I believe Walid had a very good reason too to invest many time into his code as well.

The vendor doesn't have support for my use case. I cannot migrate projects between their cloud instances as-is, especially when I ate their candy and used the next-gen project.

If you read my post again then you will recognize I have a local Jira server where I have full control. This local Jira server is part of the Jira cloud migration process, especially when you need to migrate projects from multiple clouds to one. 

[Nic Brough -Adaptavist-]

You're right, I did not focus on the use-case.  I concentrated at the utterly utterly broken "solution" and tried to explain why it was broken and a terrible way to do it.  It's not only the wrong approach to archiving, it's also trying to use a technical process which is complex and breaks things.

I have plenty of respect for others.  Even when they are doing bad things.  If I had no respect, I would not have bothered to explain so much.

But, being blunt, this whole solution to this slightly dubious use-case is quite simply, wrong in every way.

[Andy Heinzer]

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.