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: 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'
    You can create the user that is missing and grant permission

    CREATE USER IF NOT EXISTS <username> IDENTIFIED BY '<password>';
    GRANT ALL PRIVILEGES ON <ConfluenceDatabase>.* TO '<username>'@'<password>';

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




Last modified on Nov 9, 2021

Was this helpful?

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