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

Question

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

Here is an example of imported functions:

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 embed this code in phpMyAdmin, I get this error: # 1227 - Access denied; you need the SUPER privilege for this operation

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

I also tried not to use any delimiters and remove "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// 

as a result of:

 #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 SQL, phpMyAdmin or with the server?

Answer 1

The problem was that I did not have super privileges, but if I remove DEFINER from the request, I no longer need this privilege.

Starting with MySQL 5.0.3, CREATE PROCEDURE and CREATE FUNCTION require the CREATE ROUTINE privilege. They may also require the SUPER privilege, depending on the DEFINER value, as described later in this section. If binary logging is enabled, CREATE FUNCTION may require SUPER privileges, as described in section 18.6, “Binary Log Saved Programs”.

It was also necessary to set the separator field in 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 */;;