Hi Everybody,
-- I am trying to import database trigger on one of my tables I have imported earlier. It is giving me following error.
-------
ERROR 1227 (42000) at line 30: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
-------
I have googled a lot to resolve this issue. everybody asks me to set following things.
Disabel binary logging or
log_bin_trust_function_creators to 1 (i.e. true)
I did both things together and one-bye-one too. But nothing helped.
Below is my import file content. (generated from mysql workbench)
+++++++++++++++++++++
-- MySQL dump 10.13 Distrib 5.7.21, for Win64 (x86_64)
--
-- Host: localhost Database: cpr
-- ------------------------------------------------------
-- Server version 5.7.21-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `cpr.updateTRNumber`
BEFORE INSERT ON cpr.changerequest FOR EACH ROW
BEGIN
DECLARE nextNumber VARCHAR(10);
SET nextNumber = (select if(cnt = 0, 'TR0000001', (select concat('TR',lpad(cast(max(substr(CRTRNumber,3))+1 as char),7,'0'))
from cpr.changerequest where CRLocationId=new.CRLocationId)) as nextTicketNumber
from (SELECT count(*) as cnt
FROM cpr.changerequest
where CRLocationId=new.CRLocationId) as TRCountInLocation);
SET new.CRTRNumber = nextNumber;
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `cpr`.`updateTCRPumber` BEFORE UPDATE ON `changerequest` FOR EACH ROW
BEGIN
DECLARE nextNumber VARCHAR(11);
DECLARE currNumber VARCHAR(11);
DECLARE statusidtocheck integer;
select CRSMID
into statusidtocheck
from changerequeststatusmaster
where CRSMStatus = "Requester Hod Approved Proposed Solution";
if (new.CRStatus = statusidtocheck) then
select IFNULL(new.CRTCRPNumber,"") into currNumber from cpr.changerequest where CRId = new.CRId;
if (currNumber = "") THEN
SET nextNumber = (select if(cnt = 0, 'CRP0000001', (select concat('CRP',lpad(cast(max(substr(CRTCRPNumber,4))+1 as char),7,'0'))
from cpr.changerequest where CRLocationId=new.CRLocationId)) as nextTicketNumber
from (SELECT count(*) as cnt
FROM cpr.changerequest
where CRLocationId=new.CRLocationId
and CRTCRPNumber is not null) as TRCountInLocation);
#SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT = new.CRLocationId ;
SET new.CRTCRPNumber = nextNumber;
END IF;
end if;
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2018-01-24 12:02:31
+++++++++++++++++++++++++
I have been searching for last week and not able to find any solution to this issue. I found from Google Cloud SQL group that SUPER privilege is not supported.
Great if somebody can help me.
Thanks in advance.
You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-discuss+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/fdfcc0a9-3fcf-463c-a7df-6232b212afb2%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
No comments:
Post a Comment