Summary

Upgrading a Confluence instance with a large number of audit entries (stored in the  AUDITRECORD  table) takes a long time to complete. This is due to an upgrade task called  UpgradeTask1MigrateLegacyEvents  introduced in Confluence 7.5.  Sample log messages that will be seen in the  atlassian-confluence.log  are given below:

2021-08-15 00:24:07,239 INFO [lifecycle:thread-1] [service.audit.migration.ConfluenceAuditEntityMigrator] migrate Found 93032298 audit records to migrate.
2021-08-15 00:24:24,640 INFO [audit-migrator:thread-6] [service.audit.migration.ConfluenceAuditEntityMigrator] accept Processed 10000 / 93032298 audit records. Success: 10000. Error: 0. Percentage: 0.01 %
2021-08-15 00:24:32,195 INFO [audit-migrator:thread-4] [service.audit.migration.ConfluenceAuditEntityMigrator] accept Processed 20000 / 93032298 audit records. Success: 20000. Error: 0. Percentage: 0.02 %
2021-08-15 00:24:40,247 INFO [audit-migrator:thread-7] [service.audit.migration.ConfluenceAuditEntityMigrator] accept Processed 30000 / 93032298 audit records. Success: 30000. Error: 0. Percentage: 0.03 %
2021-08-15 00:24:48,367 INFO [audit-migrator:thread-2] [service.audit.migration.ConfluenceAuditEntityMigrator] accept Processed 40000 / 93032298 audit records. Success: 40000. Error: 0. Percentage: 0.04 %
2021-08-15 00:25:00,007 INFO [audit-migrator:thread-7] [service.audit.migration.ConfluenceAuditEntityMigrator] accept Processed 50000 / 93032298 audit records. Success: 50000. Error: 0. Percentage: 0.05 %

Cause

Starting version 7.5, Confluence is shipped with  Advanced Audit Log feature  - because of which a new upgrade task had to be factored in to migrate audit log data in the legacy format and tables to the new Advanced Logs Format.
 This audit record migration process (via upgrade task) is responsible for migrating data from the following 3 tables to the new Advanced Audit Log's table - AO_C77861_AUDIT_ENTITY in batches (by default 1000 records at a time).

- AUDITRECORD 
- AUDIT_AFFECTED_OBJECT 
- AUDIT_CHANGED_VALUE 

Should the Confluence instance has a large number of records in the AUDITRECORDS table, the process would then take a longer time to be completed - resulting in an additional chunk of time in the Confluence upgrade process. 

Workaround

1. Modify the default configuration of Legacy Audit Migrator's Threads (audit-migrator)

The  UpgradeTask1MigrateLegacyEvents upgrade task has 2 system properties to control its behavior. These JVM parameters would need to be added and configured in setenv.sh   or  setenv.bat  file before attempting to upgrade Confluence. See  Configuring System Properties to find out how to set system properties in your environment. 

- legacy.audit.migrator.num.threads

- legacy.audit.migrator.batch.size  

2. Data Minimisation 

Reducing the total number of data set to be migrated would also minimize the total time taken to complete the UpgradeTask1MigrateLegacyEvents  upgrade task. We could follow the steps below to do this, before attempting to upgrade Confluence:

1. Go to  Audit Log, then choose  Export to export the Legacy Audit Log records.

   The output file would be in .csv  file format

   Alternative method should you run into slow audit log export process

   1.1. Stop Confluence

   1.2. Create a full table backup and/or table export for each of the three affected tables; AUDITRECORD,   AUDIT_AFFECTED_OBJECT and   AUDIT_CHANGED_VALUE.  Please engage your Database Administrator for further assistance on this. 

   1.3. Start up Confluence

   1.4. Go to  Audit Log, and continue with Step no. 2 below

2. On the same page, click on Settings to shorten the retention period of the Audit Log to e.g. 1 day.

   Set the retention period to 0 to delete the entire Audit Log records available

3. Then, navigate to the Scheduled Jobs page.
4. Run the  Clean Old Audit Records job to clean the Legacy Audit Log records.

3. Postpone the UpgradeTask1MigrateLegacyEvents Execution

We could also run the upgrade task (UpgradeTask1MigrateLegacyEvents) after performing the Confluence upgrade, by following the steps below:

1. Stop Confluence

2. Disable the UpgradeTask1MigrateLegacyEvents by running the SQL queries below:

   PostgreSQL

   // To find the BandanaID value to be set in the next query
   SELECT MAX(bandanaid)
   FROM bandana;
   
   // To disable the UpgradeTask1MigrateLegacyEvents process
   INSERT INTO bandana (bandanaid, bandanacontext, bandanakey, bandanavalue) VALUES ('<Max_BandanaID_From_The_Query_Above_+1>', '_GLOBAL', 'com.atlassian.audit.atlassian-audit-plugin:build', '<string>1</string>');

   MSSQL Server

   // To find the BandanaID value to be set in the next query
   SELECT MAX(BANDANAID)
   FROM BANDANA;
   
   // To disable the UpgradeTask1MigrateLegacyEvents process
   INSERT INTO BANDANA (BANDANAID, BANDANACONTEXT, BANDANAKEY, BANDANAVALUE) VALUES ('<Max_BandanaID_From_The_Query_Above_+1>', '_GLOBAL', 'com.atlassian.audit.atlassian-audit-plugin:build', '<string>1</string>');

3. Perform the Confluence upgrade

4. Once the Confluence upgrade had been performed successfully, trigger the UpgradeTask1MigrateLegacyEvents manually by following the steps below:

   4.1. Navigate to  Audit Log, then choose  Settings  to get the current retention settings configured
   

   Please take note of this as the retention settings would be reset back to the default values with this manual upgrade task operation

5. 4.2. Stop Confluence

   4.3. Run the following SQL queries:

   PostgreSQL

   // To find the BandanaID value to be set in the next query
   SELECT bandanaid
   FROM bandana
   WHERE bandanakey = 'com.atlassian.audit.atlassian-audit-plugin:build';
   
   // To trigger the UpgradeTask1MigrateLegacyEvents process manually
   DELETE FROM BANDANA 
   WHERE bandanaid = '<BandanaID_From_The_Query_Above>';

   Click here to expand...

   // To find the BandanaID value to be set in the next query
   SELECT BANDANAID
   FROM BANDANA
   WHERE BANDANAKEY = 'com.atlassian.audit.atlassian-audit-plugin:build';
   
   // To trigger the UpgradeTask1MigrateLegacyEvents process manually
   DELETE FROM BANDANA 
   WHERE BANDANAID = '<BandanaID_From_The_Query_Above>';

   4.4. Start up Confluence

6.   Confluence will start the data migration here and will finish startup after data is migrated, assure you have a window available according to your data size.

7. 
   

   4.5. Navigate to the Audit Log page again, then select more options   >   Settings. Please reapply the audit logs' retention settings as per what's been configured in Step 4.1.

It is possible to delay the migration for a longer period after the upgrade if it is not feasible to do it right away. The only side-effect is that old audit records from before the upgrade will be inaccessible since they are still stored on the old tables. Those records will become accessible again once the migration is performed.