ScriptRunner CopyProject & Native Create project failures due to PostGreSQL sequences

[Gabriel MOSCALU]

In order to test some API scripts before running in production, I took an XML backup of our production JIRA Software & JIRA ServiceDesk and restored it in a VM on my PC.

I Cleaned the XML files, imported, reindexed, aligned the plugins between the PROD & my VM, bounced the JIRA instance, reindexed again...

When i tried to run the SR Copy Project (via REST API) I got:

$ jq "." APLODK.PROJECT_CLONE.out
{
 "message": "An error occurred while notifying that a project was created",
 "status-code": 500,
 "stack-trace": "java.lang.RuntimeException: An error occurred while notifying that a project was created\n\tat com.atlassian.jira.bc.project.DefaultProjectService.notifyHandlersOfProjectCreated(DefaultProjectService.java:397)\n\tat com.atlassian.jira.bc.project.DefaultProjectService.createProjectInternal(DefaultProjectService.java:379)\n\tat com.atlassian.jira.bc.project.DefaultProjectService.createProject(DefaultProjectService.java:349)\n\tat com.atlassian.jira.bc.project.ProjectService$createProject$1.call(Unknown Source)\n\tat com.onresolve.scriptrunner.canned.jira.admin.CopyProject.doCopyProject(CopyProject.groovy:414)\n\tat com.onresolve.scriptrunner.canned.jira.admin.CopyProject$doCopyProject$0.callCurrent(Unknown Source)\n\tat com.onresolve.scriptrunner.canned.jira.admin.CopyProject.doScript(CopyProject.groovy:377)\n"
}

When trying to create a project from GUI , i got an error saying to refresh the page & try again.

However, when checking the atlassian-jira.log, i noticed errors about the sequences, all similar (only the sequence name & value were changing):

2018-02-13 16:23:27,379 http-nio-8080-exec-20 ERROR g.moscalu 983x6370x1 10rvxy1 192.168.56.1 /rest/scriptrunner/1.0/canned/com.onresolve.scriptrunner.canned.jira.admin.CopyProject [c.a.jira.project.ProjectCreateRegistrarImpl] The handler with id servicedesk-project-create-handler threw an exception while handling a notification about a project being created
com.atlassian.activeobjects.internal.ActiveObjectsSqlException: There was a SQL exception thrown by the Active Objects library:
Database:
 - name:PostgreSQL
 - version:9.3.16
 - minor version:3
 - major version:9
Driver:
 - name:PostgreSQL Native Driver
 - version:PostgreSQL 9.1 JDBC4 (build 903)
org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "AO_54307E_QUEUECOLUMN_pkey"
 Detail: Key ("ID")=(11901) already exists.

I updated 4-5 sequences, but i fear i'min for the week (and it's only Tuesday) on updating sequences values.

Any idea how the hack can I move on without manually updating all sequences in the DB with values from all the tables in the schema?

Thanks in advance,

Gabriel

[Gabriel MOSCALU · Accepted solution]

i tried to re-import the XML backup. during import, I noticed that all sequences were dropped. 

select count(*), max("ID")
from "AO_54307E_QUEUECOLUMN"

count    max

7097

11868

1 row(s)

NameStart valueLast valueIncrement byMax valueMin valueCache valueLog countCan cycle?Will increment last value before returning next value (is_called)?

AO_54307E_QUEUECOLUMN_ID_seq

1

11869

1

9223372036854775807

1

1

0

No

No

after this, it seems it's working. not sure if the reindex or re-import caused the sequence re-alignment