You have created a dump (SQL file) of your WordPress database, for example to move your website to a new server, but the import fails? If you get an error message with a
fn_realmedialibrary_childs function, this is due to Real Media Library. We explain why this happens and how you can fix it.
What error do I get?
If you try to import your database dump back into a MySQL/MariaDB database, you will get the following error message:
#1227 – Access denied; you need (at least one of) the SUPER privilege(s) for this operation
As the cause of the problem, you will probably see an SQL query that looks similar to this one:
-- -- Function -- CREATE DEFINER=`k125083_wp_2mryz`@`%` FUNCTION `bSWa8_fn_realmedialibrary_childs` (`rootId` INT, `_useTempChildTableForCheck` BOOLEAN) RETURNS VARCHAR(1000) CHARSET utf8 SQL SECURITY INVOKER BEGIN DECLARE sTemp VARCHAR(1000); DECLARE sTempChd VARCHAR(1000); SET sTemp = '$'; SET sTempChd = CAST(rootId AS CHAR); SET SESSION group_concat_max_len = 100000; WHILE sTempChd IS NOT NULL DO SET sTemp = CONCAT(sTemp,',',sTempChd); IF _useTempChildTableForCheck IS NULL OR _useTempChildTableForCheck = false THEN SELECT GROUP_CONCAT(id) INTO sTempChd FROM bSWa8_realmedialibrary WHERE FIND_IN_SET(parent,sTempChd) > 0; ELSE SELECT GROUP_CONCAT(id) INTO sTempChd FROM bSWa8_realmedialibrary_tmp WHERE FIND_IN_SET(parent,sTempChd) > 0; END IF; END WHILE; RETURN sTemp; END
Why does this error appear?
Real Media Library uses so-called MySQL
PROCEDURE language constructs. Simply speaking, you can imagine that this is code that is executed directly in the MySQL database and WordPress only gets the result of the calculation. This is many times more efficient than having WordPress (PHP) perform the same calculation.
Real Media Library uses this language construct because it is the only way to manage tens of thousands of folders and hundreds of thousands of files in the WordPress media library without performance problems. If the same calculations were done in PHP, your library with many files and folders would take several seconds to load.
PROCEDURE have by default a user who is allowed to execute the function (technically called
DEFINER) for security reasons (for more details read the MySQL documentation). If you move your website and the MySQL user or hostname of your MySQL database server is different, you will get an error. In your MySQL dump is the old user and hostname defined, but you import the database dump with a different user or hostname. This is reasonably denied for security reasons.
How do you solve the problem (and import the database again)?
You can simply remove the
DEFINER clause and thereby allow your new user to import the
PROCEDURE. Some WordPress backup tools like Duplicator can do this automatically. If your backup tool can’t do that, you can simply delete the DEFINER manually:
- Open the MySQL dump (
.sql) in a text editor (e.g. Visual Studio Code)
- Search for the
DEFINER(in the example above
- Delete the
DEFINIERclause and save the MySQL dump, so the SQL looks like this: