Symptoms
- Error while trying to import a database in Plesk:
Unable to import the example_dbname dump:
ERROR 1227 (42000) at line 27: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
- Cannot restore database from the dump under database user
example_dbuser:ERROR 1227 (42000) at line 2143: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
- Database dump has view in it with definer specified:
-
-
On Windows
C:> findstr DEFINER example_dbname.sql
/*!50013 DEFINER=`another_dbuser`@`%` SQL SECURITY DEFINER */ -
On Linux
# grep DEFINER example_dbname.sql
/*!50013 DEFINER=`another_dbuser`@`%` SQL SECURITY DEFINER */
-
Cause
User specified in the definer does not match with the user that used during restoration. User example_dbuser does not have CREATE_VIEW previlege.
Resolution
Note: the further instructions are intended for server administrators with direct RDP/SSH access to the server.
If direct SSH/RDP access to the server is not possible, contact server administrator for further assistance.
Click on a section to expand
For Linux
- Connect to the server using SSH.
- Restore such database as Plesk admin user and further create dumps as actual DB user
example_dbuser# MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -uadmin example_dbname < /path/to/example_dbname.sql
# mysqldump -u example_dbuser -p[dbuser_password] example_dbname > example_dbname_2.sql
Click on a section to expand
For Windows
-
Connect to the server via RDP.
- Open cmd as Administrator user and restore such database as Plesk admin user and further create dumps as actual DB user
example_dbuserC:> "%plesk_dir%Mysqlbinmysql.exe" -uadmin -p[Plesk_admin_password] -P3306 db_example < "C:FULLPATHTOFILE_NAME.sql"
C:> "%plesk_dir%Mysqlbinmysqldump.exe" -uexample_dbuser -p[dbuser_password] -P3306 example_dbname > "C:FULLPATHTOFILE_NAME.sql"