Import database give ERROR 1227

[Mik_27]

I am trying to import a database from a vBulletin 4 server to another server, on MySQL 5.7. The goal here to have the database ready for XenForo to import it using its add on.
When I launch the import
mysql -u user -p vb_db < vb_db_backup.sql
I get
ERROR 1227 (42000) at line 11055: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

Anyone encounter the same problem?

It seems that I could grant the SUPER permission with
GRANT SUPER ON *.* TO user@'localhost' IDENTIFIED BY 'password';
but I don't know the downside of this.

 

[Mik_27]

Apparently it is due to the presence of views into the database

Troubleshooting database import errors

If you find yourself in a situation that requires changes to your site, and your developer isn't available to help, DreamHost's skilled support team may be able to assist you for a small fee. You c...

help.dreamhost.com

now I have to find a way to remove the DEFINER in a 3+ GB file!

 

[Mik_27]

One way is to run the dump with
--set-gtid-purged=OFF
or
--skip-definer
not sure about the difference though. Plus it is available only on the recent mysqldump.

Another option is to remove the DEFINER from the dump file with sed
sed -i 's/DEFINER=[^*]*\*/\*/g' mydump.sql
or
sed -ie "s/DEFINER=\`[URL='https://forums.aws.amazon.com/']a-z0-9A-Z[/URL]*\`@\`[URL='https://forums.aws.amazon.com/']a-z0-9A-Z[/URL]*\`/DEFINER=CURRENT_USER /g" mydump.sql
again not sure which one is the best option.

I did a
cat db_backup.sql.1 | grep "DEFINER" and found a single line

/*!50013 DEFINER=root@`localhost` SQL SECURITY DEFINER */

at that point I tried to do the import with root instead of the dbuser and voilà, like magic

I hope this can be useful to other inexperienced people who encounter the same problem.