Unable to export MySQL/MariaDB dump for a domain in Plesk: insufficient privileges to SHOW CREATE

Symptoms

When exporting/copying a MySQL/MariaDB database in Plesk at Domains > example.com > Databases, the operation fails with one of the following errors:

PLESK_ERROR: Unable to export a dump of database_name:
mysqldump: johndoe has insufficient privileges to SHOW CREATE PROCEDURE database_name!
("program 'mysqldump' finished with non-zero exit code: %d", 2)

PLESK_ERROR: Unable to export a dump of database_name:
Unable to backup database 'database_name'
mysqldump: example has insufficient privileges to SHOW CREATE FUNCTION `cleanHtml`!
(Error code 21)

Cause

Insufficient permissions for the database user.

Resolution

1. Connect to the server via SSH

2. Grant SELECT permission to the database user by using one of the following MySQL queries:

   # plesk db "GRANT SELECT ON mysql.proc TO 'johndoe'"

   # plesk db "GRANT SELECT ON mysql.proc TO 'johndoe'@'localhost'"

   # plesk db "GRANT SELECT ON mysql.proc TO 'johndoe'@'%'"

Note: johndoe should be replaced with a required database user name.