Problem

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.

Diagnosis

Requirements

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.

Cause

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 .

Workaround

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.
   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. Restart 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.

Resolution

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