When importing a database export from another server, the error “ERROR 1227 (42000) at line 15126: Access denied; you need (at least one of) the SUPER privilege(s) for this operation ”- you looked in the dump at this point, was found there
/*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`MEINDBUSER`@`%` SQL SECURITY INVOKER */
The problem was the part “DEFINER =` MEINDBUSER` @ `%` ” (MEINDBUSER is of course only to be seen as an example). The database user used for the import and in this case also localhost had to be stored here. It looked like this
/*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=MEINDBUSERNEU@localhost SQL SECURITY INVOKER */
It was also important to remove the individual quotes, that only MEINDBUSERNEU @ localhost was left there. Then the import worked without any problems.
Update vom 11.05.2020
Now this problem arose again, but not so easy this time. The triggering part for the error when importing the database was this time:
/*!50003 CREATE*/ /*!50017 DEFINER=`[MEINDBUSER]`@`localhost`*/ /*!50003 TRIGGER trg_catalog_category_entity_after_insert AFTER INSERT ON catalog_category_entity FOR EACH ROW
The Definer was now very common - and not just once as in the previous part- called.
So there were two options:
- Prevent, that when backing up / Dump the database of these database users is written to the backup file OR
- Subsequently remove the user from the file and then import it.
There are options for both. I created the backup file myself using the SSH command, I can adjust the command a little and achieve it, that no concrete user but “Current User” is used. The command would be this one:
mysqldump -u "[User]" --opt --single-transaction --password="[Password]" -h "[Host z.B. localhost]" "[Database name]" | sed -E 's/DEFINER=`[^`]+`@`[^`]+`/DEFINER=CURRENT_USER/g' | gzip -9 > DATEINAME.sql.gz;
This creates a database backup with the name DATEINAME.sql.gz (so it's packed right away). During the creation of the file, the SED command turns the database-defined user data against the general one “CURRENT_USER”-Information exchanged. So the file can be imported later. (To unzip the file on the target server again “gunzip DATEINAME.sql.zip” enter. I probably don't have to mention that, that the name can be freely assigned. Likewise, the space holders within the square brackets INCL. brackets are replaced by their own data.)
But I have to revise the finished SQL file, to import them, I can do this via SSH with the following command and get the same result as above
sed -E 's/DEFINER=`[^`]+`@`[^`]+`/DEFINER=CURRENT_USER/g' DATEINAME.sql > Filename new.sql
For security reasons, the file is not simply overwritten, a new file is created, which has the name FILENAMEnew.sql.
Update vom 04.05.2021
Today we had this problem again when setting up a test shop. The error occurred when importing the live shop database into the new database
ERROR 1227 (42000) at line 717: Access denied; you need (at least one of) the SU PER privilege(s) for this operationWe then dug up this article again and now we have a possibility, to expand this a little with a new way of solving this problem. The following command removes all references to the user and an import is therefore possible without any problems:
sed -i.bak -e "s/\/\*[^*]*DEFINER=[^*]*\*\///" meindumpfile.sql