Cannot restore database from dump that has views

Created:

2017-06-26 18:43:16 UTC

Modified:

2017-08-16 16:17:53 UTC

0

Was this article helpful?


Have more questions?

Submit a request

Cannot restore database from dump that has views

Applicable to:

  • Plesk for Linux

Symptoms

Cannot restore database from dump:

# mysql -u example_dbuser -p example_dbname < example_dbname.sql


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:

# 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

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 < example_dbname.sql

# mysqldump -u example_dbuser -p <password> example_dbname > example_dbname_2.sql
Have more questions? Submit a request
Please sign in to leave a comment.