Can I modify data in fields directly in the DB?

[Andreas Walsh]

Hi,

I want to update a large number of entries with some data. It is not possible to do this via the normal bulk update mechanism.

Can I safely perform an update directly on the data in the database?

This will not be creating any new entries but rather updating existing ones e.g. to prefix a bunch of story names with a derived identifier which was missing during initial load.

At the moment I don't see any other way to do this other than direct data manipulation.

However, I'm concerned that JIRA's history and audit features may get confused by any direct manipulation.

Should I be worried?

Thanks for your help

Robert

[Vishnukumar Vasudevan · Accepted solution]

As Nic said, there is nothing to worry to edit the data as long as the Application is stopped. I performed this many times in my Oracle DB.

Better you do a re-indexing right after the activity.

[Nic Brough -Adaptavist-]

It's a high risk operation. I'd look at using the Jira CLI, or bulk updates if feasible.

If you must do it with SQL, then the routine is

1. Stop Jira
2. Get a good, proven backup of the database
3. Run the SQL updates
4. Restart Jira
5. Re-index the whole system from the admin menu

You absolutely must NOT try this while Jira is running.

You are right to worry about the history - it's going to look very odd, and if you do historical reporting, it could struggle - I'd look at going through the "changeitem" table, finding the history records that will be made inconsistent and then updating them.

If you are simply changing existing data and not adding any more, then there shouldn't be anything else to worry about (Counter entries and so-on)

[Andreas Walsh]

Thanks Nic.

I wasn't aware of the CLI as an option and thought I had to go with SQL.

Looking at the documentation it would appear that I should be able to do whatever I need as long as I can generate the appropriate command line commands.

Do you by any chance know if there are there any clear places where CLI and bulk update are not sufficient and direct SQL is your only option?

[Nic Brough -Adaptavist-]

CLI and bulk edit will suffice for pure data changes, although they both obey the permissions. The one that springs to mind here is the "can not edit" flag that is often set on certain status (i.e. closed!). You won't be able to update the fields on issues that are in a status with that edit flag set. Either remove it by editing the workflow, or add an "edit" transition that doesn't actually change the status, but has an edit screen.

There are other things too - e.g. using SQL to change a field type, but you are only worried about issue data, so I think you're ok.