Unable to import the XML backup file to the newly deployed JIRA server 8.3.3

[Sami Shaik]

Hi there,

I have configured JIRA 8.3.3 and trying to import old instance 8.1.0 XML backup file into the new one, but I am getting the following error 

Error importing data: org.xml.sax.SAXException: com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericEntityException: while inserting: [GenericEntity:Group][groupName,OSS Business Team][createdDate,2019-04-28 23:07:38.982][lowerGroupName,oss business team][active,1][directoryId,1][id,11307][updatedDate,2019-04-28 23:07:38.982][type,GROUP][local,0] (SQL Exception while executing the following:INSERT INTO cwd_group (ID, group_name, lower_group_name, active, local, created_date, updated_date, description, lower_description, group_type, directory_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) (Duplicate entry 'oss business team-1' for key 'uk_group_name_dir_id')) java.lang.Exception: com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericEntityException: while inserting: [GenericEntity:Group][groupName,OSS Business Team][createdDate,2019-04-28 23:07:38.982][lowerGroupName,oss business team][active,1][directoryId,1][id,11307][updatedDate,2019-04-28 23:07:38.982][type,GROUP][local,0] (SQL Exception while executing the following:INSERT INTO cwd_group (ID, group_name, lower_group_name, active, local, created_date, updated_date, description, lower_description, group_type, directory_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) (Duplicate entry 'oss business team-1' for key 'uk_group_name_dir_id'))

Please help me to resolve this issue ASAP.

We are using MySQL 5.7.

[Craig Castle-Mead · Accepted solution]

Hi @Sami Shaik 

the failure seems to indicate that the group “oss business team-1” is trying to be inserted twice in to the cwd_group table. 

While the source system shouldn’t allow the group to exist twice (in the same directory), I have had exports/imports fail cause the source system had a group named with a trailing space, same name with no trailing space which are cleaned up on import and then conflict. 

i would suggest:

- grep the entities file inside the zip for the string, see if that provides and insights

- check the source system for matching group names (using trim() to remove spaces - something like: SELECT directory_id, trim(lower_group_name), count(*) from cwd_group GROUP BY trim(lower_group_name) HAVING count(*) > 1;

CCM

[Sami Shaik]

Craig,

Thanks for the quick response, source system is using H2 database and now we are trying to import in new instance configured in MySQL.

What if we change the database to postgres and try to import or this issue is related to Database ? 

Thanks, 

SAS.

[Sami Shaik]

Craig,

We have resolved the issue, it was DUPLICATE group name "OSS business team" we have deleted the group and took a fresh backup XML file and tried to import and it succeeds.

Thank you so much for hinting us what is the issue with XML file.

Regards,

Sami Ahmed Shaik.

[Eric Sebian]

Hi. I'm having a similar issue, but mine states that it is a duplicate key value. Not sure what that means? Is that the project key? If so, I dont see a dup. How would I find it? Seer error below. thanks.

Error importing data: org.xml.sax.SAXException: java.lang.Exception: com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericEntityException: while inserting: [GenericEntity:SecurityProperty][propertyKey,rmi.socket.cluster.auth.secret.key][value,CnQ1EPZ/zS4NFN47NlLIw5k6+ZvCN+UUWJOJyBRUh82fl5chddzPnom61YL99ukYd4nlOhB2Kxl4cj299/Dwlw==] (SQL Exception while executing the following:INSERT INTO public.securityproperty (PROPERTY_KEY, propertyvalue) VALUES (?, ?) (ERROR: duplicate key value violates unique constraint "pk_securityproperty" Detail: Key (property_key)=(rmi.socket.cluster.auth.secret.key) already exists.)) java.lang.Exception: com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericEntityException: while inserting: [GenericEntity:SecurityProperty][propertyKey,rmi.socket.cluster.auth.secret.key][value,CnQ1EPZ/zS4NFN47NlLIw5k6+ZvCN+UUWJOJyBRUh82fl5chddzPnom61YL99ukYd4nlOhB2Kxl4cj299/Dwlw==] (SQL Exception while executing the following:INSERT INTO public.securityproperty (PROPERTY_KEY, propertyvalue) VALUES (?, ?) (ERROR: duplicate key value violates unique constraint "pk_securityproperty" Detail: Key (property_key)=(rmi.socket.cluster.auth.secret.key) already exists.))

[Martin Steglich]

Hi @Eric Sebian

I just ran in the same issue today.

My naive approach was to remove the full entry of rmi.socket.cluster.auth.secret.key from entities.xml file contained in the backup zip and create a new zip for import. Then import was running without any error and Jira 9.12.8 seems to be working so far as expected.

Maybe this helps, if the issue still persist on someone else's end.

Regards,
Martin

[Manoj Kumar S]

@Martin Steglich 

I am facing the same issue: (PROPERTY_KEY, propertyvalue) VALUES (?, ?) (ERROR: duplicate key value violates unique constraint "pk_securityproperty" Detail: Key (property_key)=(rmi.socket.cluster.auth.secret.key) already exists.))

It would be helpful if you could let me know more about the workaround you performed to fix the issue. 

Jira Version: 9.12.10

Regards,
Manoj 

[Martin Steglich]

@Manoj Kumar S , I simply extracted the backup zip file. Then opened entities.xml and removed the complete entry/line of propertyKey "rmi.socket.cluster.auth.secret.key". Then zipped the content again and used this altered zip file within the restore mechanism as usual.

I hope this helps.

[Manoj Kumar S]

Thank you @Martin Steglich for your response.