Trying to write stored function for checking is account verified or not. I want to use it in view.
CREATE
ALGORITHM = UNDEFINED
DEFINER = `root`@`%`
SQL SECURITY DEFINER
VIEW `Admin_Departaments` AS
SELECT
`Departaments`.`Id` AS `Id`,
`Departaments`.`Name` AS `Name`,
`Departaments`.`Email` AS `Email`,
ST_ISEMPTY(`Departaments`.`EmailVerificationToken`) AS `IsVerified`
FROM
`Departaments`
Following show errors and code statements.
ERROR 1227: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
SQL Statement:
CREATE DEFINER=`%`@`%` FUNCTION `IsEmpty`(str tinytext) RETURNS tinyint(1)
BEGIN
IF str = '' THEN
RETURN true;
else Return false;
END IF;
END
ERROR 1418: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)
SQL Statement:
CREATE DEFINER=`root`@`%` FUNCTION `IsEmpty`(str tinytext) RETURNS tinyint(1)
BEGIN
IF str = '' THEN
RETURN true;
else Return false;
END IF;
END
ERROR 1419: You do not have the SUPER privilege and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)
SQL Statement:
CREATE DEFINER=`root`@`%` FUNCTION `IsEmpty`(str tinytext) RETURNS tinyint(1)
deterministic
BEGIN
IF str = '' THEN
RETURN true;
else Return false;
END IF;
END
Some more info:
-Database is stored in Google Cloud Platform
-Root user is the main user that given from Google
-I already tried to change root privileges from gcloud shell running mysql from sudo, but got error
-The root privileges are all besides FILE. Role is all besides DBA.2
