Import issues - duplicate key value violates

[abhishek_singh29]

Hi All,

We have one code in Dev and same code in test with different naming conversation because we have 7 test environments to test this jobs. We are using the naming conv D_ in Dev. and E2E_D and CDL_D  (and etc) in test for some jobs. I have imported the object successfully in test with import rule set a d changed the name CDL_D* with following code:

{
for (java.util.Iterator it = jcsImportRuleSet.getObjects().iterator(); it.hasNext(); )
{
Object o = (Object) it.next();
if (o instanceof JobDefinition)
{
JobDefinition jd = (JobDefinition) o;
if (jd.getName().startsWith("D"))
{
jd.setName(jd.getName().replaceFirst("D","CDL_D"));
}
}
else if (o instanceof EventDefinition)
{
EventDefinition jd = (EventDefinition) o;
if (jd.getName().startsWith("D"))
{
jd.setName(jd.getName().replaceFirst("D","CDL_D"));
}
}
}

But when have done some changes in Dev. and I am reimporting the same object in test with few changes for code inside the few job. But import jobs are getting failed in test when it's rewriting the import rule set with import action.

I do know the changes will be overwritten for others jobs which don’t contain import rule actions but not sure about this.

So we are changing the jobs name while importing in TEST. For First import its fine but when we are doing some changes and re importing the object from DEV (i.e. D_* jobs again in TEST) with import rule set to change the change to CDL_* or E2E_*, it failing with this error.
JCS-122035: Unable to persist: JCS-XXXXX: SQLException (CODE=0, STATE=23505): ERROR: duplicate key value violates unique constraint "jcs_jobdef01"

As we have 7 test environment where this jobs need to be executed so we are following the naming conversation. Is there is any way to over rewrite code so import will not failed. I have some work around but is not suitable for mass import: 

It’s working fine for below:

1. 1)     If we will amend the car file manually, and change xml file for the jobs name CDL_ before importing it. It working fine.
2. 2)     If we delete that existing jobs in test and reimport that object, it will work fine.

Do we have any other options to short out this issues ? Or can we change it when we are exporting the object. (In mean time we want  to Write a little export script that does the rename during export)

Error details will be paste in next comment:

Regards,

Abhishek

[gmblom · Accepted solution]

Hello,

Renaming of objects during import can be done only once as you have experienced. The easiest way in my opinion is to do it during export as you have suggested.

Regards Gerben

[abhishek_singh29]

STDLOGS:

INFO 2015-08-10 14:28:50,848 Etc/UTC [Redwood Job Thread Pool: GLOBAL.System.System worker 0] job.System_Import_Archive.110494025 - Applying import ruleset to imported objects: DIRECT_LINE_GROUP_TST.E2E_IMPORT_RULE_TST_CUST_DATA

INFO 2015-08-10 14:28:52,108 Etc/UTC [Redwood Job Thread Pool: GLOBAL.System.System worker 0] job.System_Import_Archive.110494025 - Persisting imported definitions.

ERROR 2015-08-10 14:29:03,672 Etc/UTC [Redwood Job Thread Pool: GLOBAL.System.System worker 0] job.System_Import_Archive.110494025 - Error processing import

JCS-122035: Unable to persist: JCS-XXXXX: SQLException (CODE=0, STATE=23505): ERROR: duplicate key value violates unique constraint "jcs_jobdef01"

Detail: Key (f_partition, a_name, a_branchllp)=(49488025, E2E_D_CUB_CHAN1_0TED, -1) already exists.

Caused by: com.redwood.scheduler.persistence.api.PersistenceException$SQLError: JCS-XXXXX: SQLException (CODE=0, STATE=23505): ERROR: duplicate key value violates unique constraint "jcs_jobdef01"

Detail: Key (f_partition, a_name, a_branchllp)=(49488025, E2E_D_CUB_CHAN1_0TED, -1) already exists.

... 9 more

Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "jcs_jobdef01"

Detail: Key (f_partition, a_name, a_branchllp)=(49488025, E2E_D_CUB_CHAN1_0TED, -1) already exists.

ERROR 2015-08-10 14:29:03,672 Etc/UTC [Redwood Job Thread Pool: GLOBAL.System.System worker 0] job.System_Import_Archive.110494025 - Error during import: JCS-122035: Unable to persist: JCS-XXXXX: @locale2@:(com.redwood.scheduler.exception.Class.persistence.api.PersistenceException$SQLError '0' '23505' 'ERROR: duplicate key value violates unique constraint "jcs_jobdef01"

Detail: Key (f_partition, a_name, a_branchllp)=(49488025, E2E_D_CUB_CHAN1_0TED, -1) already exists.')

ERROR 2015-08-10 14:29:03,672 Etc/UTC [Redwood Job Thread Pool: GLOBAL.System.System worker 0] job.System_Import_Archive.110494025 - Error during import: JCS-XXXXX: SQLException (CODE=0, STATE=23505): ERROR: duplicate key value violates unique constraint "jcs_jobdef01"

Detail: Key (f_partition, a_name, a_branchllp)=(49488025, E2E_D_CUB_CHAN1_0TED, -1) already exists.

ERROR 2015-08-10 14:29:03,672 Etc/UTC [Redwood Job Thread Pool: GLOBAL.System.System worker 0] job.System_Import_Archive.110494025 - Error during import: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "jcs_jobdef01"

Detail: Key (f_partition, a_name, a_branchllp)=(49488025, E2E_D_CUB_CHAN1_0TED, -1) already exists.

ERROR 2015-08-10 14:29:03,672 Etc/UTC [Redwood Job Thread Pool: GLOBAL.System.System worker 0] job.System_Import_Archive.110494025 - See stderr for more information.

---------------

ERROR 2015-08-10 14:29:03,672 Etc/UTC [Redwood Job Thread Pool: GLOBAL.System.System worker 0] job.System_Import_Archive.110494025 - Import failed, no objects have been imported.

-- JOB RUN STACK TRACE --

JCS-122035: Unable to persist: JCS-XXXXX: SQLException (CODE=0, STATE=23505): ERROR: duplicate key value violates unique constraint "jcs_jobdef01"

Detail: Key (f_partition, a_name, a_branchllp)=(49488025, E2E_D_CUB_CHAN1_0TED, -1) already exists.

at com.redwood.scheduler.model.SchedulerSessionImpl.writeDirtyListLocal(SchedulerSessionImpl.java:1012)

at com.redwood.scheduler.model.SchedulerSessionImpl.persist(SchedulerSessionImpl.java:938)

at com.redwood.scheduler.system.jobs.CronacleArchiveImport.executeImport(CronacleArchiveImport.java:757)

at com.redwood.scheduler.system.jobs.CronacleArchiveImport.executeImportRetry(CronacleArchiveImport.java:703)

at com.redwood.scheduler.system.jobs.CronacleArchiveImport.runInternal(CronacleArchiveImport.java:678)

at com.redwood.scheduler.system.jobs.CronacleArchiveImport.run(CronacleArchiveImport.java:287)

at com.redwood.scheduler.system.jobs.CommonSystemJob.execute(CommonSystemJob.java:52)

at com.redwood.scheduler.systemjobservice.impl.JobWorker.doWork(JobWorker.java:269)

at com.redwood.scheduler.infrastructure.workqueue.Worker.run(Worker.java:81)

at java.lang.Thread.run(Thread.java:745)

Caused by: com.redwood.scheduler.persistence.api.PersistenceException$SQLError: JCS-XXXXX: SQLException (CODE=0, STATE=23505): ERROR: duplicate key value violates unique constraint "jcs_jobdef01"

Detail: Key (f_partition, a_name, a_branchllp)=(49488025, E2E_D_CUB_CHAN1_0TED, -1) already exists.

at com.redwood.scheduler.persistence.impl.LowLevelPersistenceImpl.writeDirtyObjectListRetry(LowLevelPersistenceImpl.java:476)

at com.redwood.scheduler.persistence.impl.LowLevelPersistenceImpl.access$0(LowLevelPersistenceImpl.java:245)

at com.redwood.scheduler.persistence.impl.LowLevelPersistenceImpl$WriteDirtyObjectListUnitOfWork.execute(LowLevelPersistenceImpl.java:119)

at com.redwood.scheduler.persistence.impl.LowLevelPersistenceImpl.writeDirtyObjectList(LowLevelPersistenceImpl.java:187)

at com.redwood.scheduler.cluster.persistence.ClusteredLowLevelPersistence.writeDirtyObjectList(ClusteredLowLevelPersistence.java:67)

at com.redwood.scheduler.model.SchedulerSessionImpl.writeDirtyListLocal(SchedulerSessionImpl.java:987)

... 9 more

Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "jcs_jobdef01"

Detail: Key (f_partition, a_name, a_branchllp)=(49488025, E2E_D_CUB_CHAN1_0TED, -1) already exists.

at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)

at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)

at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)

at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)

at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)

at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:363)

at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:97)

at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:97)

at com.redwood.scheduler.persistence.impl.PreparedStatementImpl.executeUpdate(PreparedStatementImpl.java:629)

at com.redwood.scheduler.persistence.impl.LowLevelPersistenceImpl.populateAndExecute(LowLevelPersistenceImpl.java:1017)

at com.redwood.scheduler.persistence.impl.LowLevelPersistenceImpl.processCreation(LowLevelPersistenceImpl.java:869)

at com.redwood.scheduler.persistence.impl.LowLevelPersistenceImpl.writeDirtyObjectListRetry(LowLevelPersistenceImpl.java:367)

... 14 more