Can someone help me with functions in MySQL?

Question

I run the following script:

USE MODERN_FAMILY;
DROP FUNCTION IF EXISTS compare_news; 
DELIMITER $$
CREATE FUNCTION compare_news(n INT, m INT)
RETURNS VARCHAR(20)

BEGIN
DECLARE s VARCHAR(20);

IF n>m THEN SET s='>'; 
ELSEIF n=m THEN SET s='='; 
ELSE SET s='<';
END IF;
SET s = CONCAT(n, ' ', s, ' ',m);
RETURN s; END;$$

First script returns this error :

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

Then, I run this:

USE MODERN_FAMILY;
SELECT compare_news(2,5);

It returns this error:

Error Code: 2014. Commands out of sync; you can't run this command now.

Does someone know if I have an error with the script? Or is related to my SQL configuration?

Works fine for me so 'is related with my SQL configuration' – P.Salmon Sep 10 '19 at 16:19 — P.Salmon, Sep 10 '19 at 16:19

Dark Knight · Accepted Answer · 2019-09-10T16:49:05.297

log_bin_trust_function_creators variable controls whether binary logging should trust the stored function creators for not to create unsafe stored functions.

Reference: Stored Program Logging

When you create a stored function, you must declare either that it is deterministic or that it does not modify data. Otherwise, it may be unsafe for data recovery or replication.

By default, for a CREATE FUNCTION statement to be accepted, at least one of DETERMINISTIC, NO SQL, or READS SQL DATA must be specified explicitly. Otherwise an error occurs:

ERROR 1418 (HY000): 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)

To relax the preceding conditions on function creation (that you must have the SUPER privilege and that a function must be declared deterministic or to not modify data), set the global log_bin_trust_function_creators system variable to 1.

Solution 1: Make your function which doesn't manipulate data or deterministic in nature

CREATE FUNCTION `compare_news`(
    `n` INT,
    `m` INT
) RETURNS VARCHAR(20) CHARSET latin1 LANGUAGE SQL DETERMINISTIC NO SQL SQL SECURITY DEFINER COMMENT '' 
BEGIN 
    DECLARE s VARCHAR(20); 
    IF n>m THEN 
        SET s='>'; 
    ELSEIF n=m THEN 
        SET s='='; 
    ELSE 
        SET s='<'; 
    END IF; 

    SET s = CONCAT(n, ' ', s, ' ',m); 
    RETURN s; 
END

Solution 2: Enable MySQL to trust such functions by setting mysql log_bin_trust_function_creators variable to ON.

Reference: log_bin_trust_function_creators

SET GLOBAL log_bin_trust_function_creators = 1;

The variable will change upon restart if you do not update the config to reflect the change.

Hey! Thank you so much for all the explanation. I read that it is necessary to change as well the configuration file .ini but I have a Mac how can I do that? Do you know or is not necessary? I really appreciate your time. — Hkova07, Sep 10 '19 at 16:40
@HeinerRomeroLeiva, if you use `solution 1` then you don't have to set that variable. — Dark Knight, Sep 10 '19 at 16:57
This might be helpful for future reference. https://stackoverflow.com/questions/10757169/location-of-my-cnf-file-on-macos — Dark Knight, Sep 10 '19 at 17:02

Can someone help me with functions in MySQL?

1 Answers1