Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

Next challenges

Recent achievements

  • Global
  • Personal

Recognition

  • Give kudos
  • Received
  • Given

Leaderboard

  • Global

Trophy case

Kudos (beta program)

Kudos logo

You've been invited into the Kudos (beta program) private group. Chat with others in the program, or give feedback to Atlassian.

View group

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

How do I insert a record in customfieldvalue table?

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

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

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

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

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.

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.

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.

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

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
TAGS

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you