Importing a space fails with 'could not execute statement' error in Confluence Server

お困りですか?

アトラシアン コミュニティをご利用ください。

コミュニティに質問


プラットフォームについて: Server と Data Center のみ - この記事は、サーバーおよびデータセンター プラットフォームのアトラシアン製品にのみ適用されます。

要約

Importing a space into Confluence Server or Confluence Cloud fails with a "Could not execute statement" error. 

The following error appears in the atlassian-confluence.log:

ERROR: duplicate key value violates unique constraint "uk_jp1ad5yufsih5r7lqrygakpug"
OR
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Violation of UNIQUE KEY constraint 'UK_jp1ad5yufsih5r7lqrygakpug'

環境

This problem was identified in Confluence server and datacenter version 6. It could also affect other versions of Confluence.

原因

This problem is due to a bug (CONFSERVER-45278: Space import failing with the error message "could not execute statement") which introduced inconsistencies in your database which then caused information from multiple spaces to be included in a single space export. This issue only affects space export and import.  

診断

Use the following query to confirm whether your Confluence database has been affected by this issue.  

Note that you run this query on the Confluence Server or Data Center instance that you exported the space from, not the one you are attempting to import into.  As always, you should take a full backup of the database, and perform this on a test or staging instance first. 

SELECT COUNT(*)
FROM CONTENT
JOIN CONTENT xpage ON CONTENT.PAGEID = xpage.CONTENTID
WHERE CONTENT.SPACEID != xpage.SPACEID
UNION ALL
SELECT COUNT(*)
FROM CONTENT
JOIN CONTENT xpage ON CONTENT.PARENTID = xpage.CONTENTID
WHERE CONTENT.SPACEID != xpage.SPACEID
UNION ALL
SELECT COUNT(*)
FROM CONTENT
JOIN CONTENT xpage ON CONTENT.PREVVER = xpage.CONTENTID
WHERE CONTENT.SPACEID != xpage.SPACEID;

If the query only returns zeroes, your database is not affected by this particular issue. You should contact Atlassian Support for help troubleshooting the export/import problem. 

If the query returns non-zero values, your database is affected

ソリューション

データベースの変更を行う場合は必ず事前にバックアップを取得してください。可能な場合は、まずステージング サーバーで SQL コマンドの変更、挿入、更新、または削除を行うようにします。

Once you have confirmed your database is affected by this problem (see Diagnosis above), you will need to fix the inconsistencies in the database.

  1. Take a full backup of your database.  We strongly recommend applying this fix in a staging or test environment first, before applying it to your production database. We used PostgreSQL and MySQL, but the SQL statements should work for any supported database.
  2. Confluence を停止します。 

  3. Execute the following SQL statements on your database. 

    Postgres のクエリ
    UPDATE CONTENT set SPACEID = subquery.validSpaceId
    FROM
    (SELECT CONTENT.CONTENTID as contentId, xpage.SPACEID as validSpaceId
    FROM CONTENT
    JOIN CONTENT xpage ON CONTENT.PARENTID = xpage.CONTENTID
    WHERE CONTENT.SPACEID != xpage.SPACEID
    AND xpage.PARENTID IS NULL) subquery
    WHERE CONTENT.CONTENTID = subquery.CONTENTID;
    
    UPDATE CONTENT set SPACEID = subquery.validSpaceId
    FROM
    (SELECT CONTENT.CONTENTID as contentId, xpage.SPACEID as validSpaceId
    FROM CONTENT
    JOIN CONTENT xpage ON CONTENT.PARENTID = xpage.CONTENTID
    WHERE CONTENT.SPACEID != xpage.SPACEID
    AND xpage.PARENTID IS NOT NULL) subquery
    WHERE CONTENT.CONTENTID = subquery.CONTENTID;
    
    UPDATE CONTENT set SPACEID = subquery.validSpaceId
    FROM
    (SELECT CONTENT.CONTENTID as contentId, xpage.SPACEID as validSpaceId
    FROM CONTENT
    JOIN CONTENT xpage ON CONTENT.PAGEID = xpage.CONTENTID
    WHERE CONTENT.SPACEID != xpage.SPACEID
    AND xpage.PAGEID IS NULL) subquery
    WHERE CONTENT.CONTENTID = subquery.CONTENTID;
    
    UPDATE CONTENT set SPACEID = subquery.validSpaceId
    FROM
    (SELECT CONTENT.CONTENTID as contentId, xpage.SPACEID as validSpaceId
    FROM CONTENT
    JOIN CONTENT xpage ON CONTENT.PAGEID = xpage.CONTENTID
    WHERE CONTENT.SPACEID != xpage.SPACEID
    AND xpage.PAGEID IS NOT NULL) subquery
    WHERE CONTENT.CONTENTID = subquery.CONTENTID;
    
    UPDATE CONTENT set SPACEID = NULL, PARENTID = NULL
    FROM
    (SELECT CONTENT.CONTENTID as contentId, xpage.SPACEID as validSpaceId
    FROM CONTENT
    JOIN CONTENT xpage ON CONTENT.PREVVER = xpage.CONTENTID
    WHERE CONTENT.SPACEID != xpage.SPACEID) subquery
    WHERE CONTENT.CONTENTID = subquery.CONTENTID;
    
    SELECT COUNT(*)
    FROM CONTENT
    JOIN CONTENT xpage ON CONTENT.PAGEID = xpage.CONTENTID
    WHERE CONTENT.SPACEID != xpage.SPACEID
    UNION ALL
    SELECT COUNT(*)
    FROM CONTENT
    JOIN CONTENT xpage ON CONTENT.PARENTID = xpage.CONTENTID
    WHERE CONTENT.SPACEID != xpage.SPACEID
    UNION ALL
    SELECT COUNT(*)
    FROM CONTENT
    JOIN CONTENT xpage ON CONTENT.PREVVER = xpage.CONTENTID
    WHERE CONTENT.SPACEID != xpage.SPACEID;
    MySQL のクエリ
    /* Create a set of tables to process the content updated */
    DROP TABLE IF EXISTS UPDATE_CONTENT_TMP;
    
    /* Query block 1 */
    CREATE TABLE UPDATE_CONTENT_TMP AS (SELECT CONTENT.CONTENTID as contentId, xpage.SPACEID as validSpaceId
    FROM CONTENT
    JOIN CONTENT xpage ON CONTENT.PARENTID = xpage.CONTENTID
    WHERE CONTENT.SPACEID != xpage.SPACEID
    AND xpage.PARENTID IS NULL);
    
    UPDATE CONTENT, UPDATE_CONTENT_TMP set CONTENT.SPACEID = UPDATE_CONTENT_TMP.validSpaceId
    WHERE CONTENT.CONTENTID = UPDATE_CONTENT_TMP.CONTENTID;
    
    DROP TABLE UPDATE_CONTENT_TMP;
    
    /* Query block 2 */
    CREATE TABLE UPDATE_CONTENT_TMP AS (SELECT CONTENT.CONTENTID as contentId, xpage.SPACEID as validSpaceId
    FROM CONTENT
    JOIN CONTENT xpage ON CONTENT.PARENTID = xpage.CONTENTID
    WHERE CONTENT.SPACEID != xpage.SPACEID
    AND xpage.PARENTID IS NOT NULL);
    
    UPDATE CONTENT, UPDATE_CONTENT_TMP set CONTENT.SPACEID = UPDATE_CONTENT_TMP.validSpaceId
    WHERE CONTENT.CONTENTID = UPDATE_CONTENT_TMP.CONTENTID;
    
    DROP TABLE UPDATE_CONTENT_TMP;
    
    /* Query block 3 */
    CREATE TABLE UPDATE_CONTENT_TMP AS (SELECT CONTENT.CONTENTID as contentId, xpage.SPACEID as validSpaceId
    FROM CONTENT
    JOIN CONTENT xpage ON CONTENT.PAGEID = xpage.CONTENTID
    WHERE CONTENT.SPACEID != xpage.SPACEID
    AND xpage.PAGEID IS NULL);
    
    UPDATE CONTENT, UPDATE_CONTENT_TMP set CONTENT.SPACEID = UPDATE_CONTENT_TMP.validSpaceId
    WHERE CONTENT.CONTENTID = UPDATE_CONTENT_TMP.CONTENTID;
    
    DROP TABLE UPDATE_CONTENT_TMP;
    
    /* Query block 4 */
    CREATE TABLE UPDATE_CONTENT_TMP AS (SELECT CONTENT.CONTENTID as contentId, xpage.SPACEID as validSpaceId
    FROM CONTENT
    JOIN CONTENT xpage ON CONTENT.PAGEID = xpage.CONTENTID
    WHERE CONTENT.SPACEID != xpage.SPACEID
    AND xpage.PAGEID IS NOT NULL);
    
    UPDATE CONTENT, UPDATE_CONTENT_TMP set CONTENT.SPACEID = UPDATE_CONTENT_TMP.validSpaceId
    WHERE CONTENT.CONTENTID = UPDATE_CONTENT_TMP.CONTENTID;
    
    DROP TABLE UPDATE_CONTENT_TMP;
    
    /* Query block 5 */
    CREATE TABLE UPDATE_CONTENT_TMP AS (SELECT CONTENT.CONTENTID as contentId, xpage.SPACEID as validSpaceId
    FROM CONTENT
    JOIN CONTENT xpage ON CONTENT.PREVVER = xpage.CONTENTID
    WHERE CONTENT.SPACEID != xpage.SPACEID);
    
    UPDATE CONTENT, UPDATE_CONTENT_TMP set CONTENT.SPACEID = NULL, CONTENT.PARENTID = NULL
    WHERE CONTENT.CONTENTID = UPDATE_CONTENT_TMP.CONTENTID;
    
    DROP TABLE UPDATE_CONTENT_TMP;
    
    /* Final check */
    SELECT COUNT(*)
    FROM CONTENT
    JOIN CONTENT xpage ON CONTENT.PAGEID = xpage.CONTENTID
    WHERE CONTENT.SPACEID != xpage.SPACEID
    UNION ALL
    SELECT COUNT(*)
    FROM CONTENT
    JOIN CONTENT xpage ON CONTENT.PARENTID = xpage.CONTENTID
    WHERE CONTENT.SPACEID != xpage.SPACEID
    UNION ALL
    SELECT COUNT(*)
    FROM CONTENT
    JOIN CONTENT xpage ON CONTENT.PREVVER = xpage.CONTENTID
    WHERE CONTENT.SPACEID != xpage.SPACEID;
    Microsoft SQL Server のクエリ
    /* Create a set of tables to process the content updated */
    DROP TABLE IF EXISTS UPDATE_CONTENT_TMP;
    
    /* Query block 1 */
    SELECT CONTENT.CONTENTID as CONTENTID, xpage.SPACEID as validSpaceId
    INTO UPDATE_CONTENT_TMP
    FROM CONTENT
    JOIN CONTENT xpage ON CONTENT.PARENTID = xpage.CONTENTID
    WHERE CONTENT.SPACEID != xpage.SPACEID
    AND xpage.PARENTID IS NULL;
    
    UPDATE CONTENT 
    set CONTENT.SPACEID = UPDATE_CONTENT_TMP.validSpaceId
    FROM CONTENT
    INNER JOIN UPDATE_CONTENT_TMP
    ON CONTENT.CONTENTID = UPDATE_CONTENT_TMP.CONTENTID
    WHERE CONTENT.CONTENTID = UPDATE_CONTENT_TMP.CONTENTID;
    
    DROP TABLE UPDATE_CONTENT_TMP;
    
    /* Query block 2 */
    SELECT CONTENT.CONTENTID as CONTENTID, xpage.SPACEID as validSpaceId
    INTO UPDATE_CONTENT_TMP
    FROM CONTENT
    JOIN CONTENT xpage ON CONTENT.PARENTID = xpage.CONTENTID
    WHERE CONTENT.SPACEID != xpage.SPACEID
    AND xpage.PARENTID IS NOT NULL;
    
    UPDATE CONTENT
    set CONTENT.SPACEID = UPDATE_CONTENT_TMP.validSpaceId
    FROM CONTENT
    INNER JOIN UPDATE_CONTENT_TMP
    ON CONTENT.CONTENTID = UPDATE_CONTENT_TMP.CONTENTID
    WHERE CONTENT.CONTENTID = UPDATE_CONTENT_TMP.CONTENTID;
    
    DROP TABLE UPDATE_CONTENT_TMP;
    
    /* Query block 3 */
    SELECT CONTENT.CONTENTID as CONTENTID, xpage.SPACEID as validSpaceId
    INTO UPDATE_CONTENT_TMP
    FROM CONTENT
    JOIN CONTENT xpage ON CONTENT.PAGEID = xpage.CONTENTID
    WHERE CONTENT.SPACEID != xpage.SPACEID
    AND xpage.PAGEID IS NULL;
    
    UPDATE CONTENT
    set CONTENT.SPACEID = UPDATE_CONTENT_TMP.validSpaceId
    FROM CONTENT
    INNER JOIN UPDATE_CONTENT_TMP
    ON CONTENT.CONTENTID = UPDATE_CONTENT_TMP.CONTENTID
    WHERE CONTENT.CONTENTID = UPDATE_CONTENT_TMP.CONTENTID;
    
    DROP TABLE UPDATE_CONTENT_TMP;
    
    /* Query block 4 */
    SELECT CONTENT.CONTENTID as CONTENTID, xpage.SPACEID as validSpaceId
    INTO UPDATE_CONTENT_TMP
    FROM CONTENT
    JOIN CONTENT xpage ON CONTENT.PAGEID = xpage.CONTENTID
    WHERE CONTENT.SPACEID != xpage.SPACEID
    AND xpage.PAGEID IS NOT NULL;
    
    UPDATE CONTENT
    set CONTENT.SPACEID = UPDATE_CONTENT_TMP.validSpaceId
    FROM CONTENT
    INNER JOIN UPDATE_CONTENT_TMP
    on CONTENT.CONTENTID = UPDATE_CONTENT_TMP.CONTENTID
    WHERE CONTENT.CONTENTID = UPDATE_CONTENT_TMP.CONTENTID;
    
    DROP TABLE UPDATE_CONTENT_TMP;
    
    /* Query block 5 */
    SELECT CONTENT.CONTENTID as CONTENTID, xpage.SPACEID as validSpaceId 
    INTO UPDATE_CONTENT_TMP
    FROM CONTENT
    JOIN CONTENT xpage ON CONTENT.PREVVER = xpage.CONTENTID
    WHERE CONTENT.SPACEID != xpage.SPACEID;
    
    UPDATE CONTENT
    set CONTENT.SPACEID = NULL, CONTENT.PARENTID = NULL
    FROM CONTENT
    INNER JOIN UPDATE_CONTENT_TMP
    ON CONTENT.CONTENTID = UPDATE_CONTENT_TMP.CONTENTID
    WHERE CONTENT.CONTENTID = UPDATE_CONTENT_TMP.CONTENTID;
    
    DROP TABLE UPDATE_CONTENT_TMP;
    
    /* Final check */
    SELECT COUNT(*)
    FROM CONTENT
    JOIN CONTENT xpage ON CONTENT.PAGEID = xpage.CONTENTID
    WHERE CONTENT.SPACEID != xpage.SPACEID
    UNION ALL
    SELECT COUNT(*)
    FROM CONTENT
    JOIN CONTENT xpage ON CONTENT.PARENTID = xpage.CONTENTID
    WHERE CONTENT.SPACEID != xpage.SPACEID
    UNION ALL
    SELECT COUNT(*)
    FROM CONTENT
    JOIN CONTENT xpage ON CONTENT.PREVVER = xpage.CONTENTID
    WHERE CONTENT.SPACEID != xpage.SPACEID;
    Oracle queries
    MERGE INTO CONTENT c
    USING (
    SELECT CONTENT.CONTENTID as contentId, xpage.SPACEID as validSpaceId
    FROM CONTENT
    JOIN CONTENT xpage ON CONTENT.PARENTID = xpage.CONTENTID
    WHERE CONTENT.SPACEID != xpage.SPACEID
    AND xpage.PARENTID IS NULL
    ) subquery
    ON (c.CONTENTID = subquery.CONTENTID)
    WHEN MATCHED THEN 
    UPDATE SET c.SPACEID = subquery.validSpaceId;
    
    MERGE INTO CONTENT c
    USING (
    SELECT CONTENT.CONTENTID as contentId, xpage.SPACEID as validSpaceId
    FROM CONTENT
    JOIN CONTENT xpage ON CONTENT.PARENTID = xpage.CONTENTID
    WHERE CONTENT.SPACEID != xpage.SPACEID
    AND xpage.PARENTID IS NOT NULL
    ) subquery
    ON (c.CONTENTID = subquery.CONTENTID)
    WHEN MATCHED THEN 
    UPDATE SET c.SPACEID = subquery.validSpaceId;
    
    MERGE INTO CONTENT c
    USING (
    SELECT CONTENT.CONTENTID as contentId, xpage.SPACEID as validSpaceId
    FROM CONTENT
    JOIN CONTENT xpage ON CONTENT.PAGEID = xpage.CONTENTID
    WHERE CONTENT.SPACEID != xpage.SPACEID
    AND xpage.PAGEID IS NULL
    ) subquery
    ON (c.CONTENTID = subquery.CONTENTID)
    WHEN MATCHED THEN 
    UPDATE SET c.SPACEID = subquery.validSpaceId;
    
    MERGE INTO CONTENT c
    USING (
    SELECT CONTENT.CONTENTID as contentId, xpage.SPACEID as validSpaceId
    FROM CONTENT
    JOIN CONTENT xpage ON CONTENT.PAGEID = xpage.CONTENTID
    WHERE CONTENT.SPACEID != xpage.SPACEID
    AND xpage.PAGEID IS NOT NULL
    ) subquery
    ON (c.CONTENTID = subquery.CONTENTID)
    WHEN MATCHED THEN 
    UPDATE SET c.SPACEID = subquery.validSpaceId;
    
    MERGE INTO CONTENT c
    USING (
    SELECT CONTENT.CONTENTID as contentId, xpage.SPACEID as validSpaceId
    FROM CONTENT
    JOIN CONTENT xpage ON CONTENT.PREVVER = xpage.CONTENTID
    WHERE CONTENT.SPACEID != xpage.SPACEID
    ) subquery
    ON (c.CONTENTID = subquery.CONTENTID)
    WHEN MATCHED THEN 
    UPDATE SET SPACEID = NULL, PARENTID = NULL;
    
    SELECT COUNT(*)
    FROM CONTENT
    JOIN CONTENT xpage ON CONTENT.PAGEID = xpage.CONTENTID
    WHERE CONTENT.SPACEID != xpage.SPACEID
    UNION ALL
    SELECT COUNT(*)
    FROM CONTENT
    JOIN CONTENT xpage ON CONTENT.PARENTID = xpage.CONTENTID
    WHERE CONTENT.SPACEID != xpage.SPACEID
    UNION ALL
    SELECT COUNT(*)
    FROM CONTENT
    JOIN CONTENT xpage ON CONTENT.PREVVER = xpage.CONTENTID
    WHERE CONTENT.SPACEID != xpage.SPACEID;
    
    


    If any non-zero values are returned, repeat this step, and execute the statements again. 

    If only zeros are returned, the issues have been fixed, and you can move to the next step.

  4. Confluence を再起動します。
  5. Go to  > General Administration > Cache management and flush the Content Objects cache.  


You can now attempt to re-export the space from your Confluence Server site and import it into another Confluence site.  

回避策

It may be possible to import this space XML in a new instance of Confluence, you'll notice that more than one space will be imported. After the import completes, go to the specific space we want and run an export, it should contain only that space. You can confirm by opening its entities.xml file and search for the space object:

<object class="Space" package="com.atlassian.confluence.spaces">
<id name="id">11111111</id>
<property name="name"><![CDATA[Space name]]></property>
<property name="key"><![CDATA[spacekey]]></property>

We should have only one space object in the entities.xml file. With this confirmed, proceed with the space import in the destination instance.


最終更新日 2021 年 9 月 7 日

この内容はお役に立ちましたか?

はい
いいえ
この記事についてのフィードバックを送信する
Powered by Confluence and Scroll Viewport.