How to restore missing favorites after import from XML
This Knowledge Base article was written specifically for the Atlassian Server platform. Due to the Functional differences in Atlassian Cloud, the contents of this article cannot be applied to Atlassian Cloud applications.
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:
- The current Confluence database
- A previous backup of the Confluence database
- A known user who is reporting missing favourites in the new instance
- The ability to run queries against both the production and the backup database
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>';
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>;
- 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-36348Getting issue details... STATUS .
Alwaysyour data before performing any modifications to the database. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.
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:
Extract the favorites from the previous database with the following SQL query:
SELECT * FROM LABEL WHERE NAME='favourite'
- 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.
- Copy old_favourites.csv to the test server running your new Confluence database.
- 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.
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 );
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;
- You may need to modify the query depending on the format of the CSV file generated in step #2
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' );
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);
- To make sure the indexes also contain the updated information, I suggest you rebuild your index from scratch:
- 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)
- Remove the <confluence-home>/index directory.
- 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.
- Restart Confluence.
- 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.