mysql - Creating functions in phpMyAdmin - Error: access denied you need the super privilege for this operation

Question

I imported a MySQL database. All the tables where successfully imported but not the functions. The only way I can execute SQL queries is through phpMyAdmin or with a PHP script (no SSH).

Here's an example of the functions to import:

DELIMITER ;;
/*!50003 DROP FUNCTION IF EXISTS `f_calc_gst` */;;
/*!50003 SET SESSION SQL_MODE=""*/;;
/*!50003 CREATE*/ /*!50020 DEFINER=`journal`@`%`*/ /*!50003 FUNCTION `f_calc_gst`(p_ht decimal(15,3), p_province varchar(2)) RETURNS varchar(255) CHARSET utf8
begin
  declare res varchar(255); 
  declare v_gst decimal(15,3);
  declare v_gst_formula varchar(255);

  select GST, GST_formula
  into v_gst, v_gst_formula
  from taxes_periods
  where NOW() between dt_debut and dt_fin
  and id_province = p_province;

  set v_gst_formula = replace(v_gst_formula, 'HT$', p_ht);
  set v_gst_formula = replace(v_gst_formula, 'GST%', v_gst);

  set res = concat('select round(', v_gst_formula, ',2) "gst"');
  return res;
end */;;

If I paste this code in phpMyAdmin I get this error: #1227 - Access denied; you need the SUPER privilege for this operation

I tried removing the "/!50003" and the "/" to uncomment the SQL but I get the same error message.

I also tried not using any delimitors and removing "DELIMITER ;;" and got this error:

DROP FUNCTION IF EXISTS f_calc_gst SET SESSION SQL_MODE =  "" CREATE DEFINER =  `journal`@`%` FUNCTION  `f_calc_gst` (

p_ht DECIMAL( 15, 3 ) ,
p_province VARCHAR( 2 )
) RETURNS VARCHAR( 255 ) CHARSET utf8 BEGIN declare res VARCHAR( 255 ) ;

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET SESSION SQL_MODE=""
CREATE DEFINER=`journal`@`%` FUNCTION `f_calc_gst`(p_ht ' at line 2

Also tried :

    CREATE DEFINER=`journal`@`%` FUNCTION `f_calc_gst`(p_ht decimal(15,3), p_province varchar(2)) RETURNS varchar(255) CHARSET utf8
begin
  declare res varchar(255); 
  declare v_gst decimal(15,3);
  declare v_gst_formula varchar(255);

  select GST, GST_formula
  into v_gst, v_gst_formula
  from taxes_periods
  where NOW() between dt_debut and dt_fin
  and id_province = p_province;

  set v_gst_formula = replace(v_gst_formula, 'HT$', p_ht);
  set v_gst_formula = replace(v_gst_formula, 'GST%', v_gst);

  set res = concat('select round(', v_gst_formula, ',2) "gst"');
  return res;
end//

resulting in :

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3 

Is the problem in the SQL, phpMyAdmin or with the server?

Answer 1

The problem was I didn't have the super privilege but if I remove the DEFINER from the query I don't need this privilege anymore.

As of MySQL 5.0.3, CREATE PROCEDURE and CREATE FUNCTION require the CREATE ROUTINE privilege. They might also require the SUPER privilege, depending on the DEFINER value, as described later in this section. If binary logging is enabled, CREATE FUNCTION might require the SUPER privilege, as described in Section 18.6, “Binary Logging of Stored Programs”.

Also had to set the delimiter field under the SQL text box.

Here's the SQL Query without the DEFINER statement:

/*!50003 DROP FUNCTION IF EXISTS `f_calc_gst` */;;
/*!50003 SET SESSION SQL_MODE=""*/;;
/*!50003 CREATE*/ /*!50003 FUNCTION `f_calc_gst`(p_ht decimal(15,3), p_province varchar(2)) RETURNS varchar(255) CHARSET utf8
begin
  declare res varchar(255); 
  declare v_gst decimal(15,3);
  declare v_gst_formula varchar(255);

  select GST, GST_formula
  into v_gst, v_gst_formula
  from taxes_periods
  where NOW() between dt_debut and dt_fin
  and id_province = p_province;

  set v_gst_formula = replace(v_gst_formula, 'HT$', p_ht);
  set v_gst_formula = replace(v_gst_formula, 'GST%', v_gst);

  set res = concat('select round(', v_gst_formula, ',2) "gst"');
  return res;
end */;;