MySQL error 1449: The user specified as a definer does not exist
Platform Notice: Cloud, Server, and Data Center - This article applies equally to all platforms.
Symptoms
After moving database from a server running MySQL to other server running MySQL get the following error when trying to edit pages:
caused by: org.springframework.jdbc.UncategorizedSQLException: Hibernate operation: could not execute statement; uncategorized SQLException; SQL state [HY000]; error code [1449]; The user specified as a definer ('username'@'hostname') does not exist; nested exception is java.sql.SQLException: The user specified as a definer ('username'@'hostname') does not exist
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:89)
caused by: java.sql.SQLException: The user specified as a definer ('username'@'hostname') does not exist
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
Diagnosis and Cause
“The DEFINER
clause specifies the MySQL account to be used when checking access privileges at routine execution time for routines that have the SQL SECURITY DEFINER
characteristic.”
When a procedure is exported in a Dump File that will export procedure having username/account in it DDL.
Error will happen if that dump is imported into a Database without the same username/account and privileges granted.
Resolution
There are few possible solutions, it will all depend on your environment and which MySQL version you are running.
Solution One
Create the missing user/account and grant permission for 'username'@'hostname'
Even if you are not planning to use that user for Confluence to connect to MySQL, that user will be used by MySQL to run the Procedures with thatDEFINER
:CREATE USER IF NOT EXISTS <username> IDENTIFIED BY '<password>'; GRANT ALL PRIVILEGES ON <ConfluenceDatabase>.* TO '<username>'@'<hostname>';
Solution Two
Alter MySQL Dump File before import
As mentioned above, procedures were exported from previous MySQL with DEFINER set for '<username>'@'<hostname>'.
After export the Dump File, you should modify dump file, looking for previous username/hostname combination on Create Procedure statements and replace with new username and hostname. And finally import Dump File on new MySQL server.
Solution Three
Update Procedures Definer
Only if your new MySQL server is version 5.X (proc table was removed from MySQL 8.0)Updating the proc table Customer will be able to fix the issue changing the username
UPDATE `mysql`.`proc` p SET definer = '<NEWusername>@<NEWhostname>' WHERE definer='<username>@<hostname>' and db = '<ConfluenceDatabase>'
Solution Four
Alter Procedures
Only for MySQL 8.0The recommendation to alter procedures on MySQL 8.X is to drop them and recreate them with the changes needed.
Here is a DDL to do it for the current 5 Procedures:drop procedure content_perm_set_procedure_for_denormalised_permissions; create definer = <NEWusername>@`<NEWhostname>` procedure `content_perm_set_procedure_for_denormalised_permissions`(OUT isServiceDisabled tinyint(1)) BEGIN SET isServiceDisabled = TRUE; END; drop procedure content_permission_procedure_for_denormalised_permissions; create definer = <NEWusername>@`<NEWhostname>` procedure content_permission_procedure_for_denormalised_permissions(OUT isServiceDisabled tinyint(1)) BEGIN SET isServiceDisabled = TRUE; END; drop procedure content_procedure_for_denormalised_permissions; create definer = <NEWusername>@`<NEWhostname>` procedure content_procedure_for_denormalised_permissions(OUT isServiceDisabled tinyint(1)) BEGIN SET isServiceDisabled = TRUE; END; drop procedure space_permission_procedure_for_denormalised_permissions; create definer = <NEWusername>@`<NEWhostname>` procedure space_permission_procedure_for_denormalised_permissions(OUT isServiceDisabled tinyint(1)) BEGIN SET isServiceDisabled = TRUE; END; drop procedure space_procedure_for_denormalised_permissions; create definer = <NEWusername>@`<NEWhostname>` procedure space_procedure_for_denormalised_permissions(OUT isServiceDisabled tinyint(1)) BEGIN SET isServiceDisabled = TRUE; END;
Solution Five
Migrate Database
You can use the Documentation used to Migrate databases. This can be used to move Databases from one server to another, even if you are using same database on both server, like MySQL: Migrating to Another Database