Backup fails because of missing MySQL user

Created:

2016-11-16 13:19:27 UTC

Modified:

2017-08-16 16:25:27 UTC

0

Was this article helpful?


Have more questions?

Submit a request

Backup fails because of missing MySQL user

Applicable to:

  • Plesk Onyx for Linux
  • Plesk 11.x for Linux
  • Plesk 12.0 for Windows
  • Plesk 12.0 for Linux

Symptoms

Backup task in Plesk fails with an error:

Warning:         mysql "database_name"
mysqldump: Got error: 1449: The user specified as a definer ('database_user'@'localhost') does not exist when using LOCK TABLES

Cause

User set as a definer is missing on the system.

Resolution

Create a user with the same name and give him privileges to manage corresponding database.

CREATE USER 'database_user'@'localhost' IDENTIFIED BY 'plain_password';
GRANT ALL PRIVILEGES ON database_name.* To 'database_user'@'hostname' IDENTIFIED BY 'plain_password';

In case the username is root, instead of create the user, change the definer of a table/view with phpMyAdmin in Plesk control panel as explained in the following steps:

1. Go to the affected domain > databases > affected database > phpMyAdmin
2. Tables or Views > Select all the views and Select "Show Create" from the dropdown menu
3. Copy the sentence with the wrong definer and change the values like:
ALTER instead of CREATE
Correct database user name instead of the user with error

The first part of the sentence hould look like:

ALTER ALGORITHM=UNDEFINED DEFINER=`database_user`@`host`...


4. Go to the SQL from the upper menu and execute the modified ALTER sentence
5. Do it for all the affected views

Have more questions? Submit a request
Please sign in to leave a comment.