External Data import directly in JIRA associated database

Hi all,

Is this possible to recover data from an external source, in order to enrich the contents of a listbox field whatever the issue is?

1) Because, I saw the plugin JIRA plugin importer. But, with this one, you have to mention, at least, the summary field, in order to add some values to listbox component.

2) I saw too nFeed plugin, but I have the impression, that a permanent link will be established between JIRA and the external source, in order to see the different values of the listbox... and my need is to add/update some values on a specific multivalue field whatever the issue is...

Any other plugin can do that ?

Perhaps, could I try to add my values directly with an INSERT SQL command directly in my MySQL associated database, in the right table ?

For example :
I have a select list with operating system, which contains Windows, Linux, Mac.
Somewhere on the network, I have a textfile which contain several lines with one operating system by line.
Is this possible to recover 'Unix' text from this textfile ?

Thank you very much,

Christophe

3 answers

1 accepted

1 votes

First, you probably want to drop the idea of inserting data into the Jira database. You will break it - it can be done safely, but only when Jira is not running (and a lot of database work requires you to re-index too. Adding options to lists does not, but you still need downtime)

I've had success with simple scripting and Jelly to poke values into lists without interrupting services, and it's possible to write fields that draw from sources outside Jira (although I'd avoid it if possible, and prefer importing to internal data). The only plugins I've seen widely available are the ones you have already mentioned though.

Is there a way to invoke a Jelly script (or groovy script runner script) from an external source/event/system? I need a separate system to create customfield values in Jira when attributes are created in another system. The other system is an MDM/PIM application and it has an event driven API.

Not really. I'd drop the idea of Jelly too - it's deprecated and being removed from JIRA. The most simple case would be to use a simple url scrape/response with curl or wget to call a script-runner script.

Well, I think it could be very interesting (and more safe) for us to insert directly data into the JIRA database, after disable it temporarily to all users.

Do you think there are only concerned tables to update, or any other tables (like configuration tables for example) ?

So, if I understood :

1) I close JIRA, and open JIRA associated database (in our case, MySQL database)

2) I try to find tables where my existing data is stored for a specific listbox called ('Type' for example, which contains 'Simple' value and 'Complex' value).

3) I try to add/update some new data (for example : 'Unique' value)

4) I reopen JIRA, and create an issue, clicking on the concerned listbox to see if new value is/are present or not (in my above example : 'Unique' value)

Thanks a lot.

Best regards.

That's suitable for a one-off load, although I'd tend to avoid it.

For your process:

1) Yes, shut down Jira completely. I ALWAYS bang on about getting a tested database backup here because fiddling with the database can wreck a Jira install.

2) It sounds to me like you just want to add a load of options to a select list drop-down (I'm not 100% sure of that, hence I'm re-stating it to make sure I'm not going to tell you the wrong thing). If that is correct, then you will only need to look at two tables.

Your new options need to be added to customfieldoption - you'll need to go and look up the custom field id, the config schemes and so-on, but your options are inserts in there.

The other table is SequenceValueItem which holds a counter for options - that counter needs to be increased to be higher than the highest ID in customfieldoption

3) I would not attempt to rename data in the database. I always keep SQL changes to a minimum to keep it safe and simple. (Also, if you want to do these with SQL, check your version of Jira - are you below version 5.1?)

4) Yes, restart Jira and see if your new options appear.

Suggest an answer

Log in or Join to answer
Community showcase
Sarah Schuster
Posted Jan 29, 2018 in Jira

What are common themes you've seen across successful & failed Jira Software implementations?

Hey everyone! My name is Sarah Schuster, and I'm a Customer Success Manager in Atlassian specializing in Jira Software Cloud. Over the next few weeks I will be posting discussion topics (8 total) to ...

2,980 views 12 18
Join discussion

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
Atlassian Team Tour

Join us on the Team Tour

We're bringing product updates and pro tips on teamwork to ten cities around the world.

Save your spot