How to restore missing favorites after import from XML

お困りですか?

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

コミュニティに質問

この記事はアトラシアンのサーバー製品にのみ適用されます。クラウドとサーバー製品の違いについてはこちらをご確認ください。

問題

After Restoring from XML from an XML backup of your Confluence instance, users complain that their favorites are no longer present in the system.  Although the XML import was largely successful, some number of users do not have their favorites when they login to Confluence. There are no errors form the restore, but you can verify how many favorites a user previously had if you have retained a backup of the previous database for Confluence.

診断

要件

In order to diagnose the issue properly, you need to have on hand:

  1. The current Confluence database
  2. A previous backup of the Confluence database
  3. A known user who is reporting missing favourites in the new instance
  4. The ability to run queries against both the production and the backup database

Diagnostic Step

  1. Run the query below to check the favourites of the effected user, be sure to replace the <name_of_user> with the actual username:

    SELECT l.labelid, l.name, u.username
    FROM label l join user_mapping u on l.owner = u.user_key
    WHERE name like 'favourite'
    and u.username = '<name_of_user>';


  2. From the query above, use the label ID to gather the list of favorites for the user in question:

    SELECT u.username, l.name, c.contenttype, c.title
    FROM label l, content_label t, content c, user_mapping u
    WHERE l.labelid=t.labelid
    AND t.contentid=c.contentid
    AND t.owner = u.user_key
    AND t.labelid=<labelid_from_first_query>;
  3. Perform this query in the current and the previous Confluence databases for comparison. If there are differences, then you are affected by this issue and should perform the Workaround provided below.

原因

The root cause for this issue is that the import process misplaces Favourites within the database and they may work for one user, but not for another user. Although the Export is working correctly, the import does not restore the favorites properly for all users. This is issue  CONF-36348 - Getting issue details... STATUS .

回避策

データベースの変更を行う場合は必ず事前にバックアップを取得してください。

(warning) This workaround should be thoroughly tested in a Test environment prior to making changes within your production instance. This will provide a safety net should this not function as intended through any errors that may occur in the process.

The workaround process is as follows:

  1. Extract the favorites from the previous database with the following SQL query:

    SELECT * FROM LABEL WHERE NAME='favourite' 
  2. As you will want to transfer that data to a different database, I suggest creating a CSV file with the output of the previous command which we will call old_favourites.csv.
  3. Copy old_favourites.csv to the test server running your new Confluence database.
  4. Shutdown your test instance of Confluence to avoid data inconsistencies while modifying your database.
    (warning) Please make sure you backup your data before you do any changes so you can roll back in case it is needed.
  5. Create a temporary table:

    CREATE TABLE LABEL_OLD
    (
      labelid bigint NOT NULL,
      name character varying(255),
      owner character varying(255),
      namespace character varying(255),
      creationdate timestamp without time zone,
      lastmoddate timestamp without time zone
    ); 
  6. Dump the content of the CSV file into the temporary table. In PostgreSQL you should be able to do this with the following command:

    COPY LABEL_OLD FROM '/full/path/to/old_favourite.csv' WITH DELIMITER ',' CSV HEADER; 
  7. You may need to modify the query depending on the format of the CSV file generated in step #2
  8. Move the missing labels to the correct table in Confluence:

    INSERT INTO LABEL(LABELID, NAME, OWNER, NAMESPACE, CREATIONDATE, LASTMODDATE)
    SELECT LABELID, NAME, OWNER, NAMESPACE, CREATIONDATE, LASTMODDATE
    FROM LABEL_OLD
    WHERE NAME='favourite'
    AND LABELID NOT IN (
            SELECT LABELID
            FROM LABEL
            WHERE NAME='favourite'
    ); 
  9. Update the table CONTENT_LABEL to make sure it contains the correct information:

    UPDATE CONTENT_LABEL CL
    SET LABELID = L.LABELID
    FROM LABEL L
    WHERE CL.OWNER = L.OWNER AND CL.id IN (SELECT  cl.id
    	FROM content_label cl
    	INNER JOIN label l ON cl.labelid = l.labelid
    	where l.name = 'favourite' and cl.owner <> l.owner); 
  10. To make sure the indexes also contain the updated information, I suggest you rebuild your index from scratch:
  11. Make a backup of your <confluence-home>/index/plugin directory if this exists. This is where the Usage Tracking plugin stores it's index for the usage statistics (if needed)
  12. Remove the <confluence-home>/index directory.
  13. If you have the <confluence-home>/index/plugin directory, create the <confluence-home>/index directory and copy in the backup of <confluence-home>/index/plugin directory made in step 2.
  14. Confluence を再起動します。
  15. Now perform the manual re-indexing of your site to build a brand new index from scratch.

The favourites should now be restored within your instance.

ソリューション

Currently there is no resolution for this issue, please use the workaround provided above.

 

 

最終更新日 2017 年 4 月 6 日

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

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