External Data import directly in JIRA associated database

Christophe Leite September 2, 2013

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 vote
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.
September 2, 2013

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.

Brian Bishop
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 29, 2015

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.

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

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.

0 votes
Christophe Leite September 2, 2013

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.

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.
September 2, 2013

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.

0 votes
Udo Brand
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.
September 2, 2013

Suggest an answer

Log in or Sign up to answer