MySQL database backup task fails with "SHOW VIEW command denied to user"

Created:

2016-11-16 12:58:34 UTC

Modified:

2017-08-08 13:33:45 UTC

0

Was this article helpful?


Have more questions?

Submit a request

MySQL database backup task fails with "SHOW VIEW command denied to user"

Applicable to:

  • Plesk 12.5 for Windows

Symptoms

MySQL database backup task failed with " Couldn't execute 'show create table object_2 ': SHOW VIEW command denied to user 'root'@'IP' for table 'object_2' (1142) ":

Task name       Backup '<DB_NAME>' into group '<GROUP>'
Last execution output Destination host '<HOST>' (#27), IP '<IP>' : Internal error: /bin/sh /bin/sh -c /usr/local/pem/bin/mysql_backup.sh 'root' '' <IP> 3306 '<DB_NAME>' '/usr/local/pem/Backups/database/backup_data' failed with code 2 saying: STDOUT: '' STDERR 'mysqldump: Couldn't execute 'show create table `object_2`': SHOW VIEW command denied to user 'root'@'<IP>' for table 'object_2' (1142)

Cause

On <HOST> " Show_view_priv " is not allowed for " root " user:

mysql> SELECT * FROM mysql.user WHERE User = 'root' and Host = '<IP>'\\G;
*************************** 1. row ***************************
User: root
Password: ************************
...
Show_view_priv: N <<=============== Set to "NO"
1 row in set (0.01 sec)

Resolution

Allow " Show_view_priv ":

mysql> update mysql.user set Show_view_priv = 'Y' where user = 'root' and Host = '<IP>';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.04 sec)

and resubmit the failed task.

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