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

Problem

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)

Diagnosis

Environment

This issue affects Confluence with MySQL version 5.7 and 8.0.

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

Reference: https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html

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

The recommendation to alter procedures on MySQL 8.X is to drop them and recreate them with the changes needed.

Option 1: Running from mysql command line:

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 ;

Make the substitutions of <NEWusername> and <NEWhostname> as described above into fix_conf_definer.sql

Apply the SQL using mysql :

mysql -u <MYSQL_USER_NAME> <NewConfluenceDatabaseName> -p < fix_conf_definer.sql

Option 2: Running from a GUI SQL query tool:

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;

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;

Make the substitutions of <NEWusername> and <NEWhostname> as described above
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...

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

Open up the triggers.sql in a text editor and manually search and replace all the old definer settings to new definer settings.

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

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

Confluence Support

Get started

Knowledge base

Products

Jira Software

Jira Service Management

Jira Work Management

Confluence

Bitbucket

Resources

Documentation

Community

System Status

Suggestions and bugs

Marketplace

Billing and licensing

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

Still need help?

Problem

Diagnosis

Cause

Resolution

Solution 1

Solution 2

Solution 3

3.1. Fix up the Stored Procedures Definers:

3.2. Fix up the Triggers Definers:

Solution 4

Page

Viewport

Confluence

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

Related content

Still need help?

Problem

Diagnosis

Cause

Resolution

Solution 1

Solution 2

Solution 3

3.1. Fix up the Stored Procedures Definers:

3.2. Fix up the Triggers Definers:

Solution 4

Related content