Confluence MySQL database migration causes content_procedure_for_denormalised_permissions does not exist error
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
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:
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:
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 --routines parameter:
mysqldump commandmysqldump -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