Import of a database dump with Real Media Library fails due to missing permissions

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 FUNCTION and 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.

But, every FUNCTION and 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.