How do I insert a record in customfieldvalue table?

[Sorin Sbarnea (Citrix)]

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.

[Nic Brough -Adaptavist-]

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

[Sorin Sbarnea (Citrix)]

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

[Nic Brough -Adaptavist-]

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

[S KHADHAR MOHAIDEEN]

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

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.

[Igor Bustiuc]

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]

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

[Sorin Sbarnea (Citrix)]

Solved by using a line like this:

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