在phpMyAdmin中创建函数-错误:访问被拒绝，您需要此操作的超级权限 [英] Creating functions in phpMyAdmin - Error: access denied you need the super privilege for this operation

问题描述

我导入了一个MySQL数据库.所有表均已成功导入，但未成功导入功能.我执行SQL查询的唯一方法是通过phpMyAdmin或使用PHP脚本(无SSH).

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

如果将此代码粘贴到phpMyAdmin中，则会出现以下错误:#1227-访问被拒绝；您需要SUPER权限才能执行此操作

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

我尝试删除"/！50003"和"/"以取消对SQL的注释，但是我得到了相同的错误消息.

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

我还尝试了不使用任何分隔符并删除"DELIMITER ;;"并收到此错误:

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

也尝试过:

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

导致:

#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 

是SQL，phpMyAdmin还是服务器出现问题?

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

推荐答案

问题是我没有超级特权，但是如果我从查询中删除DEFINER，就不再需要此特权.

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.

从MySQL 5.0.3开始，CREATE PROCEDURE和CREATE FUNCTION需要 创建例程特权.他们可能还需要SUPER特权， 取决于DEFINER值，如本节后面所述.如果 启用二进制日志记录，CREATE FUNCTION可能需要SUPER 特权，如第18.6节存储的二进制日志"中所述 程序".

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".

还必须在SQL文本框下设置定界符字段.

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

这是不带DEFINER语句的SQL查询:

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

这篇关于在phpMyAdmin中创建函数-错误:访问被拒绝，您需要此操作的超级权限的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！