Hi! I just finished a site with VM 3.2.12, after exporting the DB, and trying to import it in production site, I got the error "#1227 - Access denied. You need privilege SUPER for this operation". This is the first time I got an error on VM because of trying to create a MySQL function ¿¿??
I can't find any reference to this SQL function in VM code either. Was this function created by VM by any means? the function name is #__plem_product_in_cats2
Here's the problematic SQL code
DROP FUNCTION IF EXISTS `taubx_plem_product_in_cats2`$$
CREATE DEFINER=`hkajuhrp_USER`@`XX.XX.XX.XX` FUNCTION `taubx_plem_product_in_cats2` (`product_id` INT, `cats` TEXT) RETURNS BIT(1) BEGIN
DECLARE parent int;
DECLARE in_cat bit;
DECLARE ccount int;
DECLARE orig_id int;
SET orig_id = product_id;
SET parent = (SELECT p.product_parent_id FROM taubx_virtuemart_products as p WHERE p.virtuemart_product_id = product_id);
WHILE parent > 0 DO
SET product_id = parent;
SET parent = (SELECT p.product_parent_id FROM taubx_virtuemart_products as p WHERE p.virtuemart_product_id = product_id);
END WHILE;
SET ccount = (SELECT count(*) FROM
taubx_virtuemart_product_categories as pc
LEFT JOIN
taubx_virtuemart_category_categories as cc1 on cc1.category_child_id = pc.virtuemart_category_id
LEFT JOIN
taubx_virtuemart_category_categories as cc2 on cc1.category_parent_id = cc2.category_child_id
LEFT JOIN
taubx_virtuemart_category_categories as cc3 on cc2.category_parent_id = cc3.category_child_id
LEFT JOIN
taubx_virtuemart_category_categories as cc4 on cc3.category_parent_id = cc4.category_child_id
WHERE
pc.virtuemart_product_id IN (product_id,orig_id)
AND
(
LOCATE (concat(',',pc.virtuemart_category_id,','), concat(',',cats,',')) > 0
OR
LOCATE (concat(',',coalesce(cc1.category_parent_id,'-'),','), concat(',',cats,',')) > 0
OR
LOCATE (concat(',',coalesce(cc2.category_parent_id,'-'),','), concat(',',cats,',')) > 0
OR
LOCATE (concat(',',coalesce(cc3.category_parent_id,'-'),','), concat(',',cats,',')) > 0
OR
LOCATE (concat(',',coalesce(cc4.category_parent_id,'-'),','), concat(',',cats,',')) > 0
));
IF ccount > 0 THEN SET in_cat = 1;
ELSE SET in_cat = 0;
END IF;
RETURN in_cat;
END$$
DELIMITER ;
Sorry! It's not related to VM but for Product Excell Like Import extension. Just deleted it and forgot to remove that function...