Urgent help needed: Problem after upgrade to Conlfuence v7.0.2, can't restore site export file

[Christopher Fournier]

I upgraded from an old version of Confluence v5.1.1 to v6.15.1 and then to v7.0.2. Site Full XML restores worked fine in v5.1.1, didn't test in v6.15.1. However in v7.0.2 I get the error below and can't perform a XML Full restore. Can I get assistance with how to resolve this issue? I do have a confluence log and the error from it is below. I was able to restore the database backup to get the Confluence website up and running. However, I can't perform a site XML Full restore. I performed the following MySQL query: select * from content where CONTENTID=8552785; which only comes back with one record. When I examine the enteries.xml file for duplicate records, I can't find any. It appears the entries are not being deleted properly from the DB prior to the restore, it fails immediately. This looks like a bug in the v7.0.2 to me. Thanks

Error from Confluence website after restoring site:

Import failed. Check your server logs for more information. com.atlassian.confluence.importexport.ImportExportException: Unable to complete import because the data does not match the constraints in the Confluence schema. Cause: MySQLIntegrityConstraintViolationException: Duplicate entry '8552785' for key 'PRIMARY'

Error from atlassian-confluence.log file:

2019-10-11 10:04:56,654 WARN [Long running task: Importing data] [atlassian.confluence.setup.DefaultHibernateConfigurator] executeAdditionalSql Error executing SQL during schema changes: create index c_si_ct_pv_cs_cd_idx on CONTENT (SPACEID, CONTENTTYPE, PREVVER, CONTENT_STATUS, CREATIONDATE), StatementCallback; bad SQL grammar [create index c_si_ct_pv_cs_cd_idx on CONTENT (SPACEID, CONTENTTYPE, PREVVER, CONTENT_STATUS, CREATIONDATE)]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Duplicate key name 'c_si_ct_pv_cs_cd_idx'
-- url: /longrunningtaskxml.action | referer: http://localhost:8090/admin/restore-local-file.action | traceId: c13fc0a59aaa076f | userName: itadmin | action: longrunningtaskxml
2019-10-11 10:05:00,427 ERROR [Long running task: Importing data] [engine.jdbc.spi.SqlExceptionHelper] logExceptions Duplicate entry '8552785' for key 'PRIMARY'
-- url: /longrunningtaskxml.action | referer: http://localhost:8090/admin/restore-local-file.action | traceId: c13fc0a59aaa076f | userName: itadmin | action: longrunningtaskxml
2019-10-11 10:05:00,513 ERROR [Long running task: Importing data] [confluence.importexport.xmlimport.DefaultImportProcessor] persist Error while saving object: Imported[com.atlassian.confluence.content.ContentProperty] [Id[id]=16952221, Property[name]=HIDDEN, Property[stringValue]=, Property[longValue]=0, Property[dateValue]=]
-- url: /longrunningtaskxml.action | referer: http://localhost:8090/admin/restore-local-file.action | traceId: c13fc0a59aaa076f | userName: itadmin | action: longrunningtaskxml
2019-10-11 10:05:00,517 ERROR [Long running task: Importing data] [confluence.importexport.xmlimport.BackupImporter] importEntities Cannot import the entities:
-- url: /longrunningtaskxml.action | referer: http://localhost:8090/admin/restore-local-file.action | traceId: c13fc0a59aaa076f | userName: itadmin | action: longrunningtaskxml
com.atlassian.confluence.importexport.ImportExportException: Unable to complete import because the data does not match the constraints in the Confluence schema. Cause: MySQLIntegrityConstraintViolationException: Duplicate entry '8552785' for key 'PRIMARY'

[Christopher Fournier · Accepted solution]

I found a resolution. I could only get it to to work by going through the upgrade process of 5.1.1 > 6.15.1 > 7.0.2 and performing a full site backup to an XML. Then I had to perform a fresh install on another server and restore from backup (Full XML). It only works in this manner only, then I could backup and restore the site with no problems. Too bad I couldn't find a solution on this from the Confluence forms, or google. However, at least I can help others at this point.

[Petr Vaníček]

Hi,

that's great you solved it by yourself, but you are little bit wrong with - "Too bad I couldn't find a solution on this from the Confluence forms, or google" - as this is mentioned in upgrade notes (see link below).

https://confluence.atlassian.com/doc/upgrading-confluence-4578.html#UpgradingConfluence-1.Determineyourupgradepath

Also you can't expect that data format in XML backup from 6 years old version will be compatible with current version - you jumped over over 2 platform upgrades.

So I mentioned this just to be fair to Atlassian documentation. ;-)

[Christopher Fournier]

Thanks Peter for your input. However, I already followed that article to upgrade as stated in my notes. I feel you didn’t truly read what I documented. I stated I upgraded as follows from: 5.1.1 -> 6.15.1 -> 7.0.2. Unfortunately any XML full site back (from v7.0.2) fails when I try to restore regardless. My answer has the solution, so there is no point writing it again. I feel your answer was not helpful at all. Thanks for your time. 

[Petr Vaníček]

I read both - your question and your own solution, don't worry.

I commented your anwer only for other users (with source linked to KB), because it's possible to find all related information in Atlassian documentation. Upgrades of any apps over more than one platform release can lead to problems. Good practise is to do this first on test environment.

You mean XML full site backup from 7.0.2 is not working on another 7.0.2? Or full site backup from 5.1 is not working on 7.0.2?

[Christopher Fournier]

I performed all this in a lab first, good advise! I upgraded from 5.1.1 -> 6.15.1 -> 7.0.2 per the documentation. I performed a full site XML backup of the v7.0.2 after upgraded and tested a restore and it failed no matter what.

I setup a new production environment and installed MySQL v5.7.27 and Confluence v7.0.2. I restored the XML site backup after the install from the v7.0.2 backup (from the lab server, upgrade server) and it worked. I then could perform a full XML site backup and restore with no problem. It would just fail on the upgraded server (lab) with the same backup. Weird issue, however, this is how I got it to work successfully. Everything is working perfectly now.

Thanks for you advise and the upgrade article link, it was a great topic to include.