How do I insert a record in customfieldvalue table?

Sorin Sbarnea (Citrix)
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 17, 2013

In order to perform some complex offline migration I need to insert some custom field values directly inside the database but I just discovered that the ID column does not have auto-increment.

INSERT INTO customfieldvalue (customfield, issue, stringvalue) VALUES (10105, 10303 , '11');

What would be the appropiate way to do the insterts?

FYI, that's on PostgreSQL.

3 answers

2 votes
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 17, 2013

... and you remembered to do this with Jira offline, a proven backup, and re-indexed afterwards?

Sorin Sbarnea (Citrix)
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 18, 2013

Obviously... anyway if you fail to do this you will wonder why nothing changes. Jira is caching almost everything so any change in the DB will not be visible without restart and reindex. Fail to do this and you'll feel the boumerang....

Like Igor Bustiuc likes this
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 18, 2013

Oh, and you haven't mentioned the increment you needed to do in the sequence table as well. Did you remember that?

Like Igor Bustiuc likes this
S KHADHAR MOHAIDEEN October 29, 2020

Should we increment the sequence table for each record updated? or after the bulk update, should we update the table with the max(id), ? Please confirm. Thanks.

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.
October 29, 2020

You should NOT be doing this at all.

Please do not **** up your Jira by using SQL to do anything with it.

It's not just the sequence table now as well, you really need to not touch the database.

1 vote
Igor Bustiuc October 29, 2020

Hi,

It is recommended to do this with Jira servides topped, but works also with Jira running.

 

1. UPDATE sequence_value_item SET seq_id = seq_id + 1 WHERE seq_name = 'CustomFieldValue'

2. wait for a second (sleep, dilay, whatever). This step is executed if Jira is up and running.

3. SELECT seq_id FROM sequence_value_item WHERE seq_name = 'CustomFieldValue' obtained value is used for column customfieldvalue.id;

4. INSERT INTO customfieldvalue (id, issue, customfield, textvalue) Values ({seq_id from point 3.}, {Id from jiraissue table}, {id from customfield table}, {the text value you wnat to save})

 

In table customfieldvalue there are many fields for value wanted to save, in order to know which column should use to store the value do a test from UI and after check where it is saved. So, use this column. In my case was column textvalue.

S KHADHAR MOHAIDEEN October 29, 2020

Thank you Igor. Ofcourse yes I test in UAT, I keep Jira down, when I run the SQL queries. 

0 votes
Sorin Sbarnea (Citrix)
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 17, 2013

Solved by using a line like this:

INSERT INTO customfieldvalue (id, customfield, issue, stringvalue) VALUES ( (select max(id)+1 from customfieldvalue), %s, %s , '%s')

Suggest an answer

Log in or Sign up to answer