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

小编典典

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

mysql

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?

阅读 364

2020-05-17

共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. phpMyAdmin
Delimiter field

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 */;;

2020-05-17