Symptoms
-
A backup task finishes with the following warning message in Plesk:
Warning: mysql "dbname"
Not all the data was backed up into /var/lib/psa/dumps/domains/example.com/databases/dbname successfully. Total space: 18.00 GB; Available space: 11.00 GB; Mounted on: /. mysqldump: Got error: 1449: "The user specified as a definer ('old-dbuser'@'%') does not exist" when using LOCK TABLES -
When exporting a MySQL/MariaDB database at Domains > Databases > dbname > Export Dump, the operation fails with:
Unable to export a dump of dbname:
mysqldump: Got error: 1045: "Access denied for user 'dbuser'@'%' (using password: YES)" when using LOCK TABLES -
When opening views in PHPMyAdmin, the page fails to load with:
#1449 - The user specified as a definer ('old-dbuser'@'localhost') does not exist
#1045 - Access denied for user 'dbuser'@'%' (using password: YES)
-
Migration of a subscription via Plesk Migrator fails with the following error message:
Failed to copy content of database 'dbname'
Migration tools tried to perform operation in 3 attempts: Command execution failed on the source server 'source' (203.0.113.2) with non-zero exit code.
command: MYSQL_PWD="$(cat)" mysqldump --no-defaults -h localhost -P 3306 -uadmin --quick --quote-names --add-drop-table --default-character-set=utf8 --set-charset --routines --events database_name > /root/plesk_migrator/plesk_migrator-b1i6noaid74taessfxtzkzxsliabvk0z/db-dumps/dbname.sql
exit code: 2
stdout:
stderr: mysqldump: Got error: 1449: The user specified as a definer ('old-dbuser'@'%') does not exist when using LOCK TABLES
Cause
The MySQL/MariaDB user set as a definer in the MySQL/MariaDB view is missing on the system or has different "Access control" setting.
Resolution
-
Check whether the MySQL user from the error message exists under subscription at Domains > example.com > Databases > User Management.
If the user exists - change its Access control setting:
- Go to Domains > example.com > Databases > User Management.
-
Click on the user mentioned in the error message
-
Change the access control setting to "Allow remote connections from any host".
-
Apply the chnages.
If the user does not exist - create a new user:
- Go to Domains > example.com > Databases > User Management > Add Database User.
- Fill in the fields with the missing database user from the the error message, in our example: old-dbuser > OK