Confluence MySQL database migration causes content_procedure_for_denormalised_permissions does not exist error
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
After migrating the Confluence MySQL database to a different server with mysqldump command, Confluence was unable to start up or you are unable to edit/update any pages.
Environment
7.11.0 and onward
MySQL database
Diagnosis
The below error message appears in atlassian-confluence.log:
atlassian-confluence.log
1
ERROR [http-nio-8090-exec-3] [engine.jdbc.spi.SqlExceptionHelper] logExceptions PROCEDURE confluence.content_procedure_for_denormalised_permissions does not existNo results when reviewing with the below SQL:
MySQL
1
SHOW PROCEDURE STATUS WHERE DB = '<target_DB_name>';Cause
Since Confluence 7.11.0, we have introduced the content denormalised function. This is a new feature for faster permissions service to improve page load times.
mysqldump will backup by default all the triggers but NOT the stored procedures/functions which will cause those errors.
Solution
Shutdown the source Confluence
Re-generate the Confluence MySQL database dump with --routinesparameter:
mysqldump command
1 2 3mysqldump -p conf7111 --routines > conf71112.sql # OR mysqldump -p conf7111 -R > conf71112.sqlℹ️https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html#option_mysqldump_routines
Shutdown the target Confluence
Drop off the existing target database
Recreate and import the database dump
Startup target Confluence
Was this helpful?