Symptoms
-
A backup creation fails with the following error in Plesk interface:
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 -
Unable to export MySQL/MariaDB database dump going to Domains > Databases > dbname > Export Dump with the following error message:
Unable to export a dump of dbname:
mysqldump: Got error: 1045: "Access denied for user 'dbuser'@'%' (using password: YES)" when using LOCK TABLES -
Going to Domains > Databases > dbname > PHPMyAdmin > Views > viewname, fails with one of the following error messages:
#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:
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.
Resolution
-
Create a backup of the affected domain to find out the missing MySQL/MariaDB user going to Domains > example.com > Backup Manager > Back Up
-
The backup will show a warning message, click on View the log option:
The backup Aug 1, 2019 02:45 PM was created and can be restored, although some minor issues occurred. Download the log file View the log Close this message
-
Get the missing MySQL/MariaDB user from the error message:
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 -
Go to Domains > example.com > Databases > User Management > Add Database User > Fill the form with the missing database user from the 4th step, in our example: old-dbuser > OK