Applicable to:
- Plesk for Linux
- Plesk for Windows
Symptoms
-
A backup creation fails with the following error in Plesk interface:
PLESK_ERROR: 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 database dump going to Domains > Databases > dbname > Export Dump with the following error message:
PLESK_ERROR: 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:
CONFIG_TEXT: #1449 - The user specified as a definer ('old-dbuser'@'localhost') does not exist
CONFIG_TEXT: #1045 - Access denied for user 'dbuser'@'%' (using password: YES)
-
Migration of a subscription via Plesk Migrator fails with the following error:
PLESK_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 user set as a definer in the MySQL view is missing on the system.
Resolution
-
Create a backup of the affected domain to find out the missing MySQL user going to Domains > example.com > Backup Manager > Back Up
-
The backup will show a warning message, click on View the log option:
PLESK_WARN: 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 user from the error message:
PLESK_WARN: 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
If the VIEW table is not required , remove it from database:
- Log in to Plesk GUI
- Go to Domains > example.com > Databases > dbname > phpMyAdmin and click on SQL tab > Type in the following command > Click on Go to delete the MySQL view:
CONFIG_TEXT: DROP VIEW dbname.viewname;
* Where dbname is the database name and viewname is the MySQL view name
Warning: The following steps have to be applied at your own risk and will, it's recommended to ensure that the database or the server has a valid backup in case of failure.
-
Go to Domains > example.com > Databases > dbname > phpMyAdmin
-
Click on SQL tab > Type in the following command > Click on Go:
Warning: It is very important to copy correctly the output from the command because it will be necessary to recreate the MySQL view.
CONFIG_TEXT: SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = 'dbname' AND TABLE_NAME = 'viewname';
* Where dbname is the database name and viewname the MySQL view name
The previous command provides a query that will be used to recreate the MySQL View. This is an example:
CONFIG_TEXT: select `dbname`.`hE8WGh_users`.`ID` AS `ID`,`dbname`.`hE8WGh_users`.`user_login` AS `user_login`,`dbname`.`hE8WGh_users`.`user_pass` AS `user_pass` from `dbname`.`hE8WGh_users`
-
Click on SQL tab > Type in the following command > Click on Go to delete the MySQL view:
CONFIG_TEXT: DROP VIEW dbname.viewname;
* Where dbname is the database name and viewname is the MySQL view name
-
Click on SQL tab > Type in the following command > Click on Go to create the new MySQL view:
CONFIG_TEXT: CREATE VIEW dbname.viewname AS select `dbname`.`hE8WGh_users`.`ID` AS `ID`,`dbname`.`hE8WGh_users`.`user_login` AS `user_login`,`dbname`.`hE8WGh_users`.`user_pass` AS `user_pass` from `dbname`.`hE8WGh_users`;
* Where dbname is the database name, viewname is the new MySQL view name and the SELECT query was obtained in the 3rd step
Comments
0 comments
Please sign in to leave a comment.