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

Space Export Containing Page Restriction Fails during import into Confluence with SQL Server

XMLWordPrintable

      NOTE: This bug report is for Confluence Server. Using Confluence Cloud? See the corresponding bug report.

      Steps to Reproduce:

      1. Setup Confluence with Microsoft SQL Server, PostGreSQL or MySQL
      2. Create a space
      3. Create a page
      4. Add restriction into the page you just created
      5. Export this space
      6. Import the exported space into Confluence instance that use Microsoft SQL Server as its database

      Expected Results:

      Confluence should be able to import this space as it could with Confluence that use PostGreSQL or MySQL database.

      Actual Results:

      Confluence with MS SQL server will failed to process the space import with the following stack traces:

      Caused by: org.xml.sax.SAXException: Error while importing backup: Hibernate operation: could not insert: [com.atlassian.confluence.security.ContentPermission#8323117]; SQL []; Violation of UNIQUE KEY constraint 'cp_unique_user_groups'. Cannot insert duplicate key in object 'dbo.CONTENT_PERM'. The duplicate key value is (8257543, View, <NULL>, <NULL>).; nested exception is java.sql.SQLException: Violation of UNIQUE KEY constraint 'cp_unique_user_groups'. Cannot insert duplicate key in object 'dbo.CONTENT_PERM'. The duplicate key value is (8257543, View, <NULL>, <NULL>).
org.springframework.dao.DataIntegrityViolationException: Hibernate operation: could not insert: [com.atlassian.confluence.security.ContentPermission#8323117]; SQL []; Violation of UNIQUE KEY constraint 'cp_unique_user_groups'. Cannot insert duplicate key in object 'dbo.CONTENT_PERM'. The duplicate key value is (8257543, View, <NULL>, <NULL>).; nested exception is java.sql.SQLException: Violation of UNIQUE KEY constraint 'cp_unique_user_groups'. Cannot insert duplicate key in object 'dbo.CONTENT_PERM'. The duplicate key value is (8257543, View, <NULL>, <NULL>).
	at com.atlassian.confluence.importexport.xmlimport.parser.BackupParser.endElement(BackupParser.java:53)

      Workaround 1

      Remove all page restrictions from the source Confluence before generating the space export. Then re-add the restrictions in the target instance after the import

      Workaround 2

      1. Drop the constraint in the target instance: 
        ALTER TABLE CONTENT_PERM DROP CONSTRAINT cp_unique_user_groups;
      2. Restore the Space
      3. Re-add the constraint: 
        ALTER TABLE CONTENT_PERM ADD CONSTRAINT [cp_unique_user_groups] UNIQUE NONCLUSTERED 
(
	[CPS_ID] ASC,
	[CP_TYPE] ASC,
	[USERNAME] ASC,
	[GROUPNAME] ASC
);

      Workaround 3

      Migrate your Confluence database server to other supported database.

            [CONFSERVER-33777] Space Export Containing Page Restriction Fails during import into Confluence with SQL Server

            James Whitehead added a comment -

            A fix for this issue is available in Confluence Server and Data Center 8.3.0.
            Upgrade now or check out the Release Notes to see what other issues are resolved.

            James Whitehead added a comment - A fix for this issue is available in Confluence Server and Data Center 8.3.0. Upgrade now or check out the Release Notes to see what other issues are resolved.

            David Metcalf added a comment -

            I receive the same key length warning as @Madhusudhan Matrubai (on SQL Server 2012). I expect this could lead to applications errors in future? Please confirm whether or not this error can be ignored.

            David Metcalf added a comment - I receive the same key length warning as @Madhusudhan Matrubai (on SQL Server 2012). I expect this could lead to applications errors in future? Please confirm whether or not this error can be ignored.

            Madhusudhan Matrubai added a comment -

            I was using the workaround - 2 and during the process of re-adding the constraint, I got the following warning message.

            13:27:11.346	Program	Warning! The maximum key length is 900 bytes. The index 'cp_unique_user_groups' has maximum length of 1049 bytes. For some combination of large values, the insert/update operation will fail.

            Can someone please advise if the above warning indicate if the constraints were not added and if yes, how to resolve it.

            Also what's the impact of the constraints not added back ?

            Madhusudhan Matrubai added a comment - I was using the workaround - 2 and during the process of re-adding the constraint, I got the following warning message. 13:27:11.346 Program Warning! The maximum key length is 900 bytes. The index 'cp_unique_user_groups' has maximum length of 1049 bytes. For some combination of large values, the insert/update operation will fail. Can someone please advise if the above warning indicate if the constraints were not added and if yes, how to resolve it. Also what's the impact of the constraints not added back ?

            Per Löfgren (Qlik) added a comment -

            Workaround 2 did the trick. I hope you will find a more permanent solution to this soon.

            Per Löfgren (Qlik) added a comment - Workaround 2 did the trick. I hope you will find a more permanent solution to this soon.

            Foo Sim (Inactive) added a comment -

            Hey Per,

            I've added a second workaround to this bug report. Can you give that a try? This workaround will preserve the restrictions (doesn't require you to remove restrictions in the source instance).

            Foo Sim (Inactive) added a comment - Hey Per, I've added a second workaround to this bug report. Can you give that a try? This workaround will preserve the restrictions (doesn't require you to remove restrictions in the source instance).

            per.lofgren added a comment -

            We have this issue but the Source and Target DB is both MS SQL so this could be far worse than it already is.

            per.lofgren added a comment - We have this issue but the Source and Target DB is both MS SQL so this could be far worse than it already is.

              ephillips@atlassian.com Edward
              scahyadiputra Septa Cahyadiputra (Inactive)
              Affected customers:
              3 This affects my team
              Watchers:
              17 Start watching this issue

                Created:
                Updated:
                Resolved:

                  Estimated:
                  Original Estimate - 7h
                  7h
                  Remaining:
                  Remaining Estimate - 7h
                  7h
                  Logged:
                  Time Spent - Not Specified
                  Not Specified