Uploaded image for project: 'Confluence Data Center'
  1. Confluence Data Center
  2. CONFSERVER-74738

XML import fails with a unique constraint violation when a user contains a space at the end of a duplicated username

XMLWordPrintable

      Issue Summary

      It's currently possible to synchronise users when their username ends with a space. Therefore, it makes it possible for these 2 users to exist in Confluence:

      • "admin" - (no space at the end)
      • "admin " - (with a space at the end)

      This is detected as 2 different users in Confluence and both users will have separate records in the cwd_user and user_mapping table.

      If you export the site to XML and attempt to re-import it later, the import will fail due to a unique constraint violation.

      Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "unq_lwr_username"
  Detail: Key (lower_username)=(admin) already exists.

      The import process in Confluence strips the space at the end of the username, which results in Confluence attempting to insert the same username into the user_mapping table twice.

      Steps to Reproduce

      1. Synchronise 2 users to Confluence with identical usernames but one of them has a space at the end of their username.
      2. Export the site to XML.
      3. Create a new instance of the same version.
      4. Import the XML backup.

      Expected Results

      The XML restoration should succeed with no errors.

      Actual Results

      The XML restoration fails with the below exception thrown in the atlassian-confluence.log file:

      2021-11-12 11:03:38,069 ERROR [Long running task: Importing data] [confluence.importexport.xmlimport.BackupImporter] importEntities Cannot import the entities: 
 -- url: /longrunningtaskxml.action | referer: http://localhost:8090/admin/restore.action?synchronous=false | traceId: 01ac10c83e84fca5 | userName: admin | action: longrunningtaskxml
com.atlassian.confluence.importexport.ImportExportException: Unable to complete import because the data does not match the constraints in the Confluence schema. Cause: PSQLException: ERROR: duplicate key value violates unique constraint "unq_lwr_username"
  Detail: Key (lower_username)=(admin) already exists.

      Workaround

      Ensure each user's username in Confluence is unique and not differentiated via a space character at the end of their username.

            [CONFSERVER-74738] XML import fails with a unique constraint violation when a user contains a space at the end of a duplicated username

            James Ponting added a comment -

            Hi All,

            A quick update on this one.

            In Confluence 8.3.0 we released a new and improved Import/Export implementation that is significantly more performant and memory efficient, as well as fixed a significant number of bugs and issues with the previous implementation.

            As part of reviewing outstanding issues, we reviewed this one and determined that it was fixed by our new Import/Export implementation, so I am closing this one as fixed in Confluence 8.3.0.

            Thanks,
            James Ponting
            Engineering Manager - Confluence Data Center

            James Ponting added a comment - Hi All, A quick update on this one. In Confluence 8.3.0 we released a new and improved Import/Export implementation that is significantly more performant and memory efficient, as well as fixed a significant number of bugs and issues with the previous implementation. As part of reviewing outstanding issues, we reviewed this one and determined that it was fixed by our new Import/Export implementation, so I am closing this one as fixed in Confluence 8.3.0. Thanks, James Ponting Engineering Manager - Confluence Data Center

            Thomas Deiler added a comment -

            Thanks Athirah, for raising this bug. This was a hard piece of work for both of us.

            Thomas Deiler added a comment - Thanks Athirah, for raising this bug. This was a hard piece of work for both of us.

              3061d200deff Arpan Agrawal
              azolkefli Athirah Zolkefli
              Affected customers:
              6 This affects my team
              Watchers:
              5 Start watching this issue

                Created:
                Updated:
                Resolved: