After manual inserting records in table I get ERROR: duplicate key value violates unique constraint

Question

I've manual inserted records in table customfieldvalue using sql.

for id value used construction "select max(id) from .."

db - postgresql

After inserting if i try to change some values in customfields in jira issues using jira web -interface, i get an error:

(SQL Exception while executing the following:INSERT INTO public.customfieldvalue (ID, ISSUE, CUSTOMFIELD, UPDATED, PARENTKEY, STRINGVALUE, NUMBERVALUE, TEXTVALUE, DATEVALUE, VALUETYPE) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) (ERROR: duplicate key value violates unique constraint "pk_customfieldvalue" Detail: Key (id)=(17600) already exists.))

It seems, jira have counter or sequence for it's table 'customfieldvalue', but how to set new actual correct value for this counter/sequence? How to get the name for this sequence?

Nic Brough -Adaptavist- · Answer

The process for updating or adding to a Jira database with SQL is, at an absolute minimum:

* Stop Jira
* Backup the database
* Restore the backup to a (temporary) test installation
* Test the copy to ensure that you have a good backup
* Make your SQL changes
* Restart Jira
* Run a full locking re-index (there are some changes you can make that don't need this, but generally, you will)
* Test the affected areas to make sure you've got your changes done, and nothing is broken

Randomly jamming data into tables without a complete understanding of the database structure and how the code handles all the relationships is always going to go wrong.

You are right in recognising that there are sequences you have missed updates on.  There could be one or several when you are adding custom field data, it depends entirely on the type of field.

It would be better if you could explain why you are trying to do this?  We can probably give you a safe and easier route to achieve your goal if we knew what it is.

Jeff Tomband · Answer

Yeah, Agreed (I wouldn't say never, but its risky).
But since he seems to have his mind set on it, or he has a good reason.

I'm also assuming he's doing it on a staging system first and performs a BU before actually running the inserts.

Joe Pitt · Answer

Hopefully @Jeff Tomband solution will work. You should NEVER directly interact with the database, it will usually require restoring a backup to fix the problem