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 that DEFINER:

  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.0

  The 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