Author Topic: [Solved] Can't create SQL function #__plem_product_in_cats2  (Read 340 times)

razor7

  • Jr. Member
  • **
  • Posts: 322
    • MGSCreativa - Professional VM!
[Solved] Can't create SQL function #__plem_product_in_cats2
« on: January 10, 2018, 15:24:46 pm »
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
Code: [Select]
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 ;
MGS Creativa - VirtueMart Payment Plugin Experts
http://www.mgscreativa.com

Take a look at our downloads section for VirtueMart payment plugins and mouch more!
http://www.mgscreativa.com/en/online-store

razor7

  • Jr. Member
  • **
  • Posts: 322
    • MGSCreativa - Professional VM!
Re: Can't create SQL function #__plem_product_in_cats2
« Reply #1 on: January 10, 2018, 15:37:32 pm »
Sorry! It's not related to VM but for Product Excell Like Import extension. Just deleted it and forgot to remove that function...
MGS Creativa - VirtueMart Payment Plugin Experts
http://www.mgscreativa.com

Take a look at our downloads section for VirtueMart payment plugins and mouch more!
http://www.mgscreativa.com/en/online-store