MySQL error 1449: The user specified as a definer does not exist

Still need help?

The Atlassian Community is here for you.

Ask the community


Platform notice: Server and Data Center only. This article only applies to Atlassian products on the server and data center platforms.

 

Summary

After moving a MySQL database between MySQL servers we observe the following error on atlassian-confluence.log files 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)

Environment


This issue affects Confluence with MySQL version 5.7 and 8.0.

Diagnosis

We observe the following error on atlassian-confluence.log files 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)

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 into a Dump File, it is exported having username and hostname defined in its DDL. The combination of username and hostname is the DEFINER that has permissions to run that procedure.

Error will happen if that dump is imported into a Database without the combination of username and hostname with privileges granted on the new database.

Resolution

There are few possible solutions, it will all depend on your environment and which MySQL version you are running.

Always back up your data before performing any modifications to the database. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.

The following SQL statements should be run whilst Confluence is shutdown.

Solution 1

  • 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 2

  • 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 3

3.1. Fix up the Stored Procedures Definers:

For MySQL 5.x...
-- 1. Substitute <NEWusername> with the new SQL user
-- 2. Substitute <NEWhostname> with the new Confluence host
-- 3. Substitute <username> with the old SQL user
-- 4. Substitute <hostname> with the old Confluence host
-- 5. Substitute <ConfluenceDatabase> with the new Confluence database name
UPDATE `mysql`.`proc` p SET definer = '<NEWusername>@<NEWhostname>' WHERE definer='<username>@<hostname>' and db = '<ConfluenceDatabase>';
For MySQL 8.x...
  1. The recommendation to alter procedures on MySQL 8.X is to drop them and recreate them with the changes needed.
  2. Option 1: Running from mysql command line:
    1. Save this DDL to a file called fix_conf_definer.sql:

      -- 1. Drop the existing Stored Procedures
      drop procedure if exists content_perm_set_procedure_for_denormalised_permissions;
      drop procedure if exists content_permission_procedure_for_denormalised_permissions;
      drop procedure if exists content_procedure_for_denormalised_permissions;
      drop procedure if exists space_permission_procedure_for_denormalised_permissions;
      drop procedure if exists space_procedure_for_denormalised_permissions;
      
      DELIMITER //
      
      -- 2. Substitute <NEWusername> with the new SQL user
      -- 3. Substitute <NEWhostname> with the new Confluence host
      -- 4. Run each of these blocks (one at a time) as one SQL statement
      create
          definer = <NEWusername>@`<NEWhostname>` procedure `content_perm_set_procedure_for_denormalised_permissions`(OUT isServiceDisabled tinyint(1))
      BEGIN
          SET isServiceDisabled = TRUE;
      END//
      
      create
          definer = <NEWusername>@`<NEWhostname>` procedure content_permission_procedure_for_denormalised_permissions(OUT isServiceDisabled tinyint(1))
      BEGIN
          SET isServiceDisabled = TRUE;
      END//
      
      create
          definer = <NEWusername>@`<NEWhostname>` procedure content_procedure_for_denormalised_permissions(OUT isServiceDisabled tinyint(1))
      BEGIN
          SET isServiceDisabled = TRUE;
      END//
      
      create
          definer = <NEWusername>@`<NEWhostname>` procedure space_permission_procedure_for_denormalised_permissions(OUT isServiceDisabled tinyint(1))
      BEGIN
          SET isServiceDisabled = TRUE;
      END//
      
      create
          definer = <NEWusername>@`<NEWhostname>` procedure space_procedure_for_denormalised_permissions(OUT isServiceDisabled tinyint(1))
      BEGIN
          SET isServiceDisabled = TRUE;
      END//
      DELIMITER ;
    2. (warning) Make the substitutions of <NEWusername>  and <NEWhostname>  as described above into fix_conf_definer.sql 
    3. Apply the SQL using mysql :

      mysql -u <MYSQL_USER_NAME> <NewConfluenceDatabaseName> -p < fix_conf_definer.sql 
  3. Option 2: Running from a GUI SQL query tool:
    1. Copy and paste this DDL to your SQL GUI query window and run on your new Confluence database:

      -- 1. Drop the existing Stored Procedures
      drop procedure if exists content_perm_set_procedure_for_denormalised_permissions;
      drop procedure if exists content_permission_procedure_for_denormalised_permissions;
      drop procedure if exists content_procedure_for_denormalised_permissions;
      drop procedure if exists space_permission_procedure_for_denormalised_permissions;
      drop procedure if exists space_procedure_for_denormalised_permissions;
    2. Copy and paste this DDL to your SQL GUI query window (do not run this yet!):

      -- 2. Substitute <NEWusername> with the new SQL user
      -- 3. Substitute <NEWhostname> with the new Confluence host
      -- 4. Run each of these blocks (one at a time) as one SQL statement
      create
          definer = <NEWusername>@`<NEWhostname>` procedure `content_perm_set_procedure_for_denormalised_permissions`(OUT isServiceDisabled tinyint(1))
      BEGIN
          SET isServiceDisabled = TRUE;
      END;
      
      create
          definer = <NEWusername>@`<NEWhostname>` procedure content_permission_procedure_for_denormalised_permissions(OUT isServiceDisabled tinyint(1))
      BEGIN
          SET isServiceDisabled = TRUE;
      END;
      
      create
          definer = <NEWusername>@`<NEWhostname>` procedure content_procedure_for_denormalised_permissions(OUT isServiceDisabled tinyint(1))
      BEGIN
          SET isServiceDisabled = TRUE;
      END;
      
      create
          definer = <NEWusername>@`<NEWhostname>` procedure space_permission_procedure_for_denormalised_permissions(OUT isServiceDisabled tinyint(1))
      BEGIN
          SET isServiceDisabled = TRUE;
      END;
      
      create
          definer = <NEWusername>@`<NEWhostname>` procedure space_procedure_for_denormalised_permissions(OUT isServiceDisabled tinyint(1))
      BEGIN
          SET isServiceDisabled = TRUE;
      END;
    3. (warning) Make the substitutions of <NEWusername>  and <NEWhostname>  as described above
    4. Run each "block" one at a time as a single SQL statement until all SQL blocks have been run successfully. Here is an example using DbVisualizer:


3.2. Fix up the Triggers Definers:

For MySQL 5.x and MySQL 8.x...
  1. Dump out the triggers:

    mysqldump -u <USER_NAME> -p --triggers --add-drop-trigger --no-create-info --no-data --no-create-db --skip-opt <ConfluenceDatabase> > triggers.sql
  2. Open up the triggers.sql  in a text editor and manually search and replace all the old definer settings to new definer settings.
    1. E.g. Search and replace all `confold`@`10.0.0.111`  with `confnew`@`10.0.0.122` 

      From (old):
      ...
      /*!50003 CREATE*/ /*!50017 DEFINER=`confold`@`10.0.0.111`*/ /*!50003 TRIGGER denormalised_content_trigger_on_insert
      ...
      To (new)
      ...
      /*!50003 CREATE*/ /*!50017 DEFINER=`confnew`@`10.0.0.122`*/ /*!50003 TRIGGER denormalised_content_trigger_on_insert
      ...
  3. Reapply the updated triggers


    mysql -u <USER_NAME> -p <ConfluenceDatabase> < triggers.sql



Solution 4

  • 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




Last modified on Sep 13, 2022

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.