Applicable to:
- Plesk for Linux
Symptoms
- The dump of one database cannot be imported in another:
PLESK_ERROR: ERROR 1227 (42000) at line 3171: Access denied; you need (at least one of)
the SUPER privilege(s) for this operation
Traceback (most recent call last):
File "/opt/psa/admin/sbin/dbbackup", line 6, in <module>
File "/opt/psa/lib/modules/python/dbbackup/dbbackup.py", line 99, in main
restore(options, password)
File "/opt/psa/lib/modules/python/dbbackup/dbbackup.py", line 88, in restore
raise Exception("program 'mysql' finished with non-zero exit code: %d" % p.returncode)
Exception: program 'mysql' finished with non-zero exit code: 1 - In the dump there is CREATE FUNCTION procedure with DEFINER, where DEFINER is the user of the source database
Cause
The user of the destination database doesn't have permissions to process CREATE FUNCTION procedure with DEFINER
Resolution
Provide the destination user the SUPER privilege:
- Go to Domains > example.com > File Manager and identify the database username in wp-config.php
-
Go to Plesk > Tools & Settings > Database Servers and click the phpMyAdmin icon opposite the MySQL database server.
-
Navigate to the User accounts page and click the Edit privileges link opposite the required database user:
-
Mark the SUPER permission in the Administration list and press the GO button:
Comments
I really don't think you should be advising to grant SUPER privileges to any user without explaining the implications and security risks. There are other ways to mitigate this error without granting SUPER privileges, for example by changing the username behind the DEFINER to the destination database user.
Please sign in to leave a comment.