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

      The import process misplaces Favourites on the database and they might disappear for one user and appear under another user.

      The problem happens randomly when migrating Confluence because it depends on the order of the objects in the entities.xml file of the export. It affects Confluence Cloud and Server.

      Steps to reproduce:

      1. Create 2 users with 2 favourite pages each
      2. Export Confluence Site
      3. Import Site

      Expected: each of the 2 users have 2 favourites
      Result: one user will have all favourites and the other none.

      If it didn't reproduce: (how to manually make it fail)

      1. Open entities.xml inside the export
      2. Move object class="ConfluenceUserImpl" below the objects class="Label"
      3. Import

      Confluence logs show the following message:

      2015-05-20 17:44:51.109723500 2015-05-20 17:44:51,105 WARN [Long running task: Importing data] [atlassian.confluence.importexport.DefaultImmutableImportProcessorSummary] newInstance Cannot add old key = class=class com.atlassian.confluence.labels.Label,id=819202,<null> and new key = class=class com.atlassian.confluence.labels.Label,id=6422529,<null> to the summary. One of them is a duplicate.
      

      where 819202 is the id of the label that doesn't get inserted.

      Workaround 1:

      • Open entities.xml with a text editor and move all the object class="ConfluenceUserImpl" before the object class="Label" and try importing again.

      Workaround 2:
      The following steps will work for MySQL, you may need to change the syntax for other databases.

      1. Extract the favorites from the previous database with the following SQL query:
        SELECT * FROM LABEL WHERE NAME='favourite'
      2. As we want to transfer that data to a different database, I suggest you to create a CSV file with the output of the previous command which we will call old_favourite.csv.
      3. Copy old_favourite.csv to the server running your new Confluence database.
      4. Shutdown your current instance of Confluence to avoid data inconsistencies while we modify the database.
        Please make sure you backup your data before you do any changes so we can roll back in case it is needed.
      5. Let's create a temporary table:
        CREATE TABLE LABEL_OLD LIKE LABEL;
        
      6. Let's now dump the content of the CSV file into the temporary table. In PostgreSQL you should be able to do this with the following command:
        load data local infile '/full/path/to/old_favourite.csv' into table tblUniq fields terminated by ','
        enclosed by '"'
        lines terminated by '\n'
        (uniqName, uniqCity, uniqComments)
        

        You may need to modify the query depending on the format of the CSV file generated in step #2

      7. Now we will move the missing labels to the correct table in Confluence:
        INSERT LABEL SELECT * FROM LABEL_OLD;
        
      8. Let's update the table CONTENT_LABEL to make sure it contains the correct information. We will need to run the following queries separately in MySQL:
        CREATE TABLE test (id BIGINT);
        
        INSERT INTO test
        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;
        
        UPDATE CONTENT_LABEL CL JOIN LABEL L ON CL.OWNER = L.OWNER 
        SET CL.LABELID = L.LABELID 
        WHERE CL.ID IN (SELECT id FROM test);
        
      9. Clean up:
        DROP TABLE IF EXISTS test;
        
        DROP TABLE IF EXISTS LABEL_OLD;
        
      10. To make sure the indexes also contain the updated information, I suggest you to rebuild your index from scratch:
        1. Make a backup of your <confluence-home>/index/plugin directory if it exists. This is where the Usage Tracking plugin stores it's index for the usage statistics and it cannot be rebuilt.
        2. Remove the <confluence-home>/index directory.
        3. If in step #9.1, 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.
        4. Restart server.
        5. Now perform the manual re-indexing of your site to build a brand new index from scratch

          Form Name

            [CONFSERVER-36348] Favourites missing after importing

            Daniel Hostrup Eriksen added a comment - - edited

            Just had this happen in Confluence 7.6.2 - our staging server works, but the production instance has missing saves for some users.

            edit: We upgraded from 6.11.0 to 7.6.2

            Daniel Hostrup Eriksen added a comment - - edited Just had this happen in Confluence 7.6.2 - our staging server works, but the production instance has missing saves for some users. edit : We upgraded from 6.11.0 to 7.6.2

            Willy B added a comment - - edited

            Although this was great and got us on the right track(THANK YOU!!), we still encountered issues.  Wanted to share our variance in case it helps anyone else.   (ORACLE)

            1) TARGET DB*

            CREATE TABLE CONFLUENCE.LABEL_OLD
             (
             labelid number(19) NOT NULL,
             name varchar2(255),
             owner varchar2(255),
             namespace varchar2(255),
             creationdate timestamp,
             lastmoddate timestamp
             );

            2) IN SOURCE DB ONLY THIS STEP! export as inserts and then run as

            inserts in TARGET
            select * from CONFLUENCE.LABEL where name = 'favourite';

            — export as insert statements with sql developer
             — or expdp
            {code:java}
            expdp confluence/XXXX schemas=CONFLUENCE query='CONFLUENCE.LABEL:"WHERE name='favourite'"' directory=XXXXX dumpfile=XXXXX.DMP logfile=XXXXX.LOG*

             
            3) insert TARGET
            — Run the inserts or import from the previous command, against CONFLUENCE.LABEL_OLD on TARGET DB

             
            4) insert TARGET

            INSERT INTO CONFLUENCE.LABEL(LABELID, NAME, OWNER, NAMESPACE, CREATIONDATE, LASTMODDATE)
             SELECT LABELID, NAME, OWNER, NAMESPACE, CREATIONDATE, LASTMODDATE
             FROM CONFLUENCE.LABEL_OLD
             WHERE NAME='favourite'
             AND LABELID NOT IN (
             SELECT LABELID
             FROM CONFLUENCE.LABEL
             WHERE NAME='favourite'
             );

            5) create comparison table*

            CREATE TABLE CONFLUENCE.TEST AS SELECT distinct CL.ID
             FROM CONFLUENCE.CONTENT_LABEL cl
             INNER JOIN CONFLUENCE.LABEL l ON cl.labelid = l.labelid
             where L.name = 'favourite' and CL.OWNER != L.OWNER;

            --apply fix
            --repeat this section until only null items remain.*
            ----======================================================================================
            6) Find bad data

            SELECT OWNER, count(*) 
             FROM CONFLUENCE.LABEL L
             where l.name = 'favourite'
             group by owner
             having COUNT(*) > 1
             order by 2 desc;

            7) using owner from previous query find parent

            select * from confluence.label where owner = 'XXXXXXXX'

            8) delete the version from previous query that doesn't have the parent / child relationship.*

            delete from CONFLUENCE.LABEL
             where LABELID = XXXXXXX;

            ----======================================================================================

            9) once the above is satisfied, run the update to resolve the differences.*

            UPDATE CONFLUENCE.CONTENT_LABEL CL
             SET CL.LABELID = (SELECT L.LABELID FROM CONFLUENCE.LABEL L WHERE CL.OWNER = L.OWNER and L.NAME = 'favourite')
             where CL.OWNER in (select C2.OWNER from CONFLUENCE.LABEL C2)
             AND CL.ID IN (SELECT id FROM CONFLUENCE.TEST);

            --now bounce the confluence app (systemctl stop/start confluence) as root

            Willy B added a comment - - edited Although this was great and got us on the right track(THANK YOU!!), we still encountered issues.  Wanted to share our variance in case it helps anyone else.   (ORACLE) 1) TARGET DB* CREATE TABLE CONFLUENCE.LABEL_OLD ( labelid number(19) NOT NULL, name varchar2(255), owner varchar2(255), namespace varchar2(255), creationdate timestamp, lastmoddate timestamp ); 2) IN SOURCE DB ONLY THIS STEP! export as inserts and then run as inserts in TARGET select * from CONFLUENCE.LABEL where name = 'favourite'; — export as insert statements with sql developer — or expdp {code:java} expdp confluence/XXXX schemas=CONFLUENCE query= 'CONFLUENCE.LABEL: "WHERE name=' favourite '" ' directory=XXXXX dumpfile=XXXXX.DMP logfile=XXXXX.LOG*   3) insert TARGET — Run the inserts or import from the previous command, against CONFLUENCE.LABEL_OLD on TARGET DB   4) insert TARGET INSERT INTO CONFLUENCE.LABEL(LABELID, NAME, OWNER, NAMESPACE, CREATIONDATE, LASTMODDATE) SELECT LABELID, NAME, OWNER, NAMESPACE, CREATIONDATE, LASTMODDATE FROM CONFLUENCE.LABEL_OLD WHERE NAME= 'favourite' AND LABELID NOT IN ( SELECT LABELID FROM CONFLUENCE.LABEL WHERE NAME= 'favourite' ); 5) create comparison table* CREATE TABLE CONFLUENCE.TEST AS SELECT distinct CL.ID FROM CONFLUENCE.CONTENT_LABEL cl INNER JOIN CONFLUENCE.LABEL l ON cl.labelid = l.labelid where L.name = 'favourite' and CL.OWNER != L.OWNER; --apply fix --repeat this section until only null items remain.* ----====================================================================================== 6) Find bad data SELECT OWNER, count(*) FROM CONFLUENCE.LABEL L where l.name = 'favourite' group by owner having COUNT(*) > 1 order by 2 desc; 7) using owner from previous query find parent select * from confluence.label where owner = 'XXXXXXXX' 8) delete the version from previous query that doesn't have the parent / child relationship.* delete from CONFLUENCE.LABEL where LABELID = XXXXXXX; ----====================================================================================== 9) once the above is satisfied, run the update to resolve the differences.* UPDATE CONFLUENCE.CONTENT_LABEL CL SET CL.LABELID = (SELECT L.LABELID FROM CONFLUENCE.LABEL L WHERE CL.OWNER = L.OWNER and L.NAME = 'favourite' ) where CL.OWNER in (select C2.OWNER from CONFLUENCE.LABEL C2) AND CL.ID IN (SELECT id FROM CONFLUENCE.TEST); --now bounce the confluence app (systemctl stop/start confluence) as root

            I'm facing the issue when migrating from Confluence Cloud 1000.0.0-0132b55157d to Confluence Server 6.13. Really don't know why Atlassian don't fix it.

            Tran Tuan Duong added a comment - I'm facing the issue when migrating from Confluence Cloud 1000.0.0-0132b55157d to Confluence Server 6.13. Really don't know why Atlassian don't fix it.

            Lei Wang added a comment -

            In case you are interested in Oracle solution, see the steps below.

            1. Create a temporary table - LABEL_OLD
              CREATE TABLE LABEL_OLD
              (
                labelid number(19) NOT NULL,
                name varchar2(255),
                owner varchar2(255),
                namespace varchar2(255),
                creationdate timestamp,
                lastmoddate timestamp
              );
              
            2. Import the old_favourite.csv to the temporary table LABEL_OLD using DBVisualizer
            3. 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'
              );
              
            4. Update the table CONTENT_LABEL to make sure it contains the correct information:
              CREATE TABLE TEST AS 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;
              

              then run this: (this part is important, Oracle does not support join in update statement)

              UPDATE CONTENT_LABEL CL
              SET CL.LABELID = (SELECT L.LABELID FROM LABEL L WHERE CL.OWNER = L.OWNER)
              WHERE CL.ID IN (SELECT id FROM TEST)
              
            5. Clean up
              DROP TABLE LABEL_OLD;
              DROP TABLE TEST;
              
            6. Perform the reindex

            Lei Wang added a comment - In case you are interested in Oracle solution, see the steps below. Create a temporary table - LABEL_OLD CREATE TABLE LABEL_OLD ( labelid number(19) NOT NULL, name varchar2(255), owner varchar2(255), namespace varchar2(255), creationdate timestamp, lastmoddate timestamp ); Import the old_favourite.csv to the temporary table LABEL_OLD using DBVisualizer 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: CREATE TABLE TEST AS 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; then run this: (this part is important, Oracle does not support join in update statement) UPDATE CONTENT_LABEL CL SET CL.LABELID = (SELECT L.LABELID FROM LABEL L WHERE CL.OWNER = L.OWNER) WHERE CL.ID IN (SELECT id FROM TEST) Clean up DROP TABLE LABEL_OLD; DROP TABLE TEST; Perform the reindex

            For anyone affected by this (anyone migrating a non trivial data set in the last 2.5 years?), here is my modified workaround.

            I put together an XSL that will extract a CSV of the favourites from the entities.xml (run via "xsltproc transform.xsl entities.xml"):

            <?xml version="1.0" encoding="utf-8"?>
            <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
              <xsl:output method="text" encoding="utf-8" />
              <xsl:param name="delim" select="','" />
              <xsl:param name="quote" select="'&quot;'" />
              <xsl:param name="break" select="'&#xA;'" />
              <xsl:template match="/">
                <xsl:text>labelid,name,owner,namespace,creationdate,lastmoddate</xsl:text><xsl:value-of select="$break" />
                <xsl:for-each select="//object[@class = 'Label']">
                  <xsl:if test="property[@name='name'] = 'favourite'">
                    <xsl:value-of select="concat($quote, id, $quote, $delim)" />
                    <xsl:value-of select="concat($quote, property[@name = 'name'], $quote, $delim)" />
                    <xsl:value-of select="concat($quote, property[@name = 'owningUser']/id, $quote, $delim)" />
                    <xsl:value-of select="concat($quote, property[@name = 'namespace'], $quote, $delim)" />
                    <xsl:value-of select="concat($quote, property[@name = 'creationDate'], $quote, $delim)" />
                    <xsl:value-of select="concat($quote, property[@name = 'lastModificationDate'], $quote, $break)" />
                  </xsl:if>
                </xsl:for-each>
              </xsl:template>
            </xsl:stylesheet>
            

            And fixed the SQL mentioned above to actually work (postgres):

            CREATE TABLE label_old (like label);
            \COPY label_old FROM '/home/ec2-user/favourites_old.csv' WITH CSV HEADER DELIMITER ',' QUOTE '"';
            DELETE FROM label_old WHERE labelid IN (SELECT labelid FROM label);
            INSERT label (SELECT * FROM label_old);
            CREATE TABLE test (id BIGINT);
            INSERT INTO test (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);
            UPDATE content_label SET labelid = label.labelid FROM label WHERE content_label.owner = label.owner AND content_label.id IN (SELECT id FROM test);
            DROP TABLE IF EXISTS test;
            DROP TABLE IF EXISTS label_old;

            Joe Hohertz added a comment - For anyone affected by this (anyone migrating a non trivial data set in the last 2.5 years?), here is my modified workaround. I put together an XSL that will extract a CSV of the favourites from the entities.xml (run via "xsltproc transform.xsl entities.xml"): <?xml version= "1.0" encoding= "utf-8" ?> <xsl:stylesheet version= "1.0" xmlns:xsl= "http: //www.w3.org/1999/XSL/Transform" > <xsl:output method= "text" encoding= "utf-8" /> <xsl:param name= "delim" select= " ',' " /> <xsl:param name= "quote" select= " '&quot;' " /> <xsl:param name= " break " select= " '&#xA;' " /> <xsl:template match= "/" > <xsl:text>labelid,name,owner,namespace,creationdate,lastmoddate</xsl:text><xsl:value-of select= "$ break " /> <xsl: for -each select= " //object[@class = 'Label' ]" > <xsl: if test= "property[@name= 'name' ] = 'favourite' " > <xsl:value-of select= "concat($quote, id, $quote, $delim)" /> <xsl:value-of select= "concat($quote, property[@name = 'name' ], $quote, $delim)" /> <xsl:value-of select= "concat($quote, property[@name = 'owningUser' ]/id, $quote, $delim)" /> <xsl:value-of select= "concat($quote, property[@name = 'namespace' ], $quote, $delim)" /> <xsl:value-of select= "concat($quote, property[@name = 'creationDate' ], $quote, $delim)" /> <xsl:value-of select= "concat($quote, property[@name = 'lastModificationDate' ], $quote, $ break )" /> </xsl: if > </xsl: for -each> </xsl:template> </xsl:stylesheet> And fixed the SQL mentioned above to actually work (postgres): CREATE TABLE label_old (like label); \COPY label_old FROM '/home/ec2-user/favourites_old.csv' WITH CSV HEADER DELIMITER ',' QUOTE '"' ; DELETE FROM label_old WHERE labelid IN (SELECT labelid FROM label); INSERT label (SELECT * FROM label_old); CREATE TABLE test (id BIGINT); INSERT INTO test (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); UPDATE content_label SET labelid = label.labelid FROM label WHERE content_label.owner = label.owner AND content_label.id IN (SELECT id FROM test); DROP TABLE IF EXISTS test; DROP TABLE IF EXISTS label_old;

            From cloud current as of Sept 20th to 6.4.0, looks to be affected.

            Neither suggested workaround really seems viable.

            1) Manipulating the XML as described would need some fairly specific and non-trivial tooling developed to re-order the records at the scale of data we have, and the suggestion glosses over the other dependencies we'd need to get right to not introduce new problems.

            2) Post import manipulation of the database requires getting an export done at the same time as our backup, and we don't get to query postgres on the cloud anyways,

            I  may try generating the CSV for a post-import fix by parsing it out of the entities.xml, as that might be the only path available, and should be a lot less demanding in terms of tooling that works around this issue than the sorting of the XML.

             

            Joe Hohertz added a comment - From cloud current as of Sept 20th to 6.4.0, looks to be affected. Neither suggested workaround really seems viable. 1) Manipulating the XML as described would need some fairly specific and non-trivial tooling developed to re-order the records at the scale of data we have, and the suggestion glosses over the other dependencies we'd need to get right to not introduce new problems. 2) Post import manipulation of the database requires getting an export done at the same time as our backup, and we don't get to query postgres on the cloud anyways, I  may try generating the CSV for a post-import fix by parsing it out of the entities.xml, as that might be the only path available, and should be a lot less demanding in terms of tooling that works around this issue than the sorting of the XML.  

            Roland Stahn added a comment - - edited

            I just experienced the issue during a server migration 6.0.6 => 6.2.3. Why is this not fixed after 2 years?

            Roland Stahn added a comment - - edited I just experienced the issue during a server migration 6.0.6 => 6.2.3. Why is this not fixed after 2 years?

            5.6.1 > 5.6.6.  same issue. Painful. 

            Betsson Licensing added a comment - 5.6.1 > 5.6.6.  same issue. Painful. 

            Павел added a comment -

            Павел added a comment - https://confluence.atlassian.com/confkb/how-to-restore-missing-favorites-after-import-from-xml-744719060.html?src=jira  not working 5.6.1 -> 6.0.5

            John Fernandes added a comment - - edited

            We're having the same problem with one of our clients, either workarounds are not practical at all.

            John Fernandes added a comment - - edited We're having the same problem with one of our clients, either workarounds are not practical at all.

              Unassigned Unassigned
              mfernandezbadii Mauro Badii (Inactive)
              Affected customers:
              101 This affects my team
              Watchers:
              96 Start watching this issue

                Created:
                Updated: