Create function on google mysql gives "SUPER privilege and binary logging is enabled" error

Question

Trying to create functions on MySQL that runs on Google CloudSQL. These functions worked fine on another server (VPS- Godaddy).

Official documentation says that User Defined Functions are not supported, but I am trying to create a regular stored function and not a UDF.

Lookup Error - MySQL Database Error: 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)

Logged in using Toad

Username used TAdmin

Sample Function:

DELIMITER $$
DROP FUNCTION IF EXISTS `func_getEQId`$$
CREATE DEFINER=`TAdmin`@`%` FUNCTION `func_getEQId` (`pTopicId` INT(11))
RETURNS BIGINT(20)

READS SQL DATA DETERMINISTIC

BEGIN
DECLARE vQId bigint (20);

SELECT QId INTO vQId FROM quests WHERE... 

RETURN vQId;

END$$

Don't want to disable binary logging

Jugular

I think the issue is not due to binary blogging but due to the use of the "DEFINER" option which requires SUPER privilege. Are you able to define the user directly using the TAdmin user? – Raffish

Did you try to enable the flag log_bin_trust_function_creators? Usually with that enable you can create Functions and Stored Procedures. – Enphytotic

@Enphytotic I have tried your suggestions and it worked. Setting the log_bin_trust_function_creators flag on did the trick and I was able to register UDF. Thanks. – Boisleduc

Answer 1

If using Cloud SQL, enable the log_bin_trust_function_creators by running:

gcloud sql instances patch [INSTANCE_NAME] --database-flags log_bin_trust_function_creators=on

More information in the Cloud SQL Documentation.

Enphytotic