Error restoring 5.9.2 backup XML to new 7.3 server...

[COP Help Desk]

We have an old (5.9.5) Confluence server that's been in use for some time; it is our IT office's documentation repository. When it was originally set up (not by me) it was set to synch with all users in AD (in read-only mode). (I am not sure why, that wasn't documented ...)

I've set up a new server to upgrade (both Confluence 7.3 and the underlying Linux OS) to the latest versions of both (I'm using CentOS8).

When I tried creating a backup of the old server and restoring it to the new one, it fails pretty quickly with an error:

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: SQLIntegrityConstraintViolationException: Duplicate entry '' for key 'unq_lwr_username'

What I've found online for this indicates I should find the offending user record in the XML entities file and delete it. The problem is there are over 1300 user entities in the xml file, and nothing I've found matches the key 'unq_lwr_username'  

https://confluence.atlassian.com/confkb/confluence-site-xml-import-fails-with-duplicate-key-error-438993638.html

Although this error does not quite match what I've found, because there is no mention of the table this constraint is applied to.

My thought was to reduce the sheer number of users, since it's only in use by a small group of people in our organization I modified the user Object filter to restrict LDAP lookup and sync to just that one group. But there are still all those people listed in the Users in the Administration GUI.

So I have three questions: 

1) Is LDAP directory setup required for AD SSO? (I'm assuming yes)

2) How do I remove all these un-needed records before exporting the XML backup?

3) Is this error even related to the AD LDAP? The log error that the GUI tells me to check isn't more informative: 

com.atlassian.confluence.importexport.ImportExportException: com.atlassian.confluence.importexport.ImportExportException: Unable to complete import because the data does not match the constraints in the Confluence schema. Cause: SQLIntegrityConstraintViolationException: Duplicate entry '' for key 'unq_lwr_username'

I'm pretty conversant in SQL so I have no qualms about delving into the MySQL database to do this.

[COP Help Desk · Accepted solution]

Just for historical record, the fix was this:

I moved the ad user objects for the desired users to a new nested OU in their existing OU.

I then prepended that OU to the OU Prefix in the directory editing page. This reduced the number of users to a manageable list, but I still ended up getting key violation errors that were totally inscrutable from the logs (sql=n/a value 12 violates key PRIMARY. *that* helped enormously, NOT!!) when trying to import the whole site.

I ended up creating the new server as a fresh, empty Confluence site, set up the AD directory properly, then exported and imported the spaces individually from the old server to the new one.

That worked. 

[Dave Theodore [Coyote Creek Consulting]]

I think you're barking up the wrong tree. Here's what I would do.

1. Install Confluence 5.9.2 on your test server and configure it to talk to a database.
2. Read the Confluence Upgrade Notes starting at 6.0 and each page up to 7.3.
3. Look for which interim versions you need to jump through.
4. Restore your XML backup to the 5.9.2 instance.
5. Upgrade the 5.9.2 instance to the first interim version.
6. Upgrade to each interim version until you get to 7.3.

[COP Help Desk]

Thanks. I'd read that you could go directly from my version to the latest, but that's be too easy :-) 

I have one question though: our license for the old version expired long ago, and we just got the license for our new server.

The upgrading guide mentions checking the licencing status for each upgrade

"If your support period has expired, follow the prompts to renew your license and reapply it before upgrading." 

Can I apply our license for 7.3 to each of these intermediate updates or can I use a free evaluation license to accomplish this. From the process to get the license it appears that our support license is strictly for the 7.3 version