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