ERROR 1227 (42000) at line X: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

ERROR 1227 (42000) at line X: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

When migrating a WordPress website database import failed with the error:

mysql -u USERNAME -p DB-NAME < /PATH/dump.sql

ERROR 1227 (42000) at line 3258: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

so I opened my dump file on the line at which the error occurs:

$ sed -n '3258' dump.sql
/*!50001 CREATE ALGORITHM=UNDEFINED */
image 21 - ERROR 1227 (42000) at line X: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

So because I’m importing the SQL dump as an ordinary user, I lack permissions needed to create this view.

The solution:

  1. Give required permissions to the MySQL user that is importing the file (not recommended)
  2. Import the dump as a root mysql user

Grant Permissions

Open MySQL and run:

GRANT ALL PRIVILEGES ON DB_NAME_HERE.* TO 'USERNAME_HERE'@'localhost';

Import as root

Another solution is just to import the dump as a MySQL root user:

mysql -u root -p DB-NAME < /PATH and dump.sql

There are no errors and the view is created successfully:

image 22 1024x356 - ERROR 1227 (42000) at line X: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
whoami
Stefan Pejcic
Join the discussion

I enjoy constructive responses and professional comments to my posts, and invite anyone to comment or link to my site.