Google CloudSQL 2nd Generation create Function not working

Question

For a Google CloudSQL 2nd generation instance, with Failover replication was enabled. After that when tried to import the database it is not allowing to create the procedure. Receiving below error.

Error Code: 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)

Is it true that CloudSQL with failover will not support function ?

Sample execute query

DELIMITER ;;
CREATE FUNCTION `stutzen`(amount INT) RETURNS int(11)
    DETERMINISTIC
BEGIN
    DECLARE charges FLOAT DEFAULT 1.0;
    SELECT valuesettings INTO charges FROM dreamer_tbl WHERE namesettings='stutzen.co';
    RETURN FLOOR((amount / 100) * charges) ;
END ;;
DELIMITER ;

Answer 1

You just need to set 'log_bin_trust_function_creators' to ON

To do it, open https://console.cloud.google.com

Select SQL

Select your instance

Select EDIT

DB signals

Add: log_bin_trust_function_creators on

general_log on

SAVE

Answer 2

That is not a Stored Procedure, that is a User Defined Function. You would need to rewrite this UDF as a Stored Procedure, which would work.

Answer 3

Google Cloud SQL support both, stored procedures and functions.

In your case the problem seems to be that you're trying to import a sql file that has some kind of routine that needs the SUPER privilege, and this is not permitted.

Answer 4

I tried using the SET GLOBAL log_bin_trust_function_creators, which should allow creating functions without the SUPER privilege but setting that variable is also not allowed in Cloud SQL. It needs SUPER privilege for setting it.

Answer 5

You should run:

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

as mentioned here