MySQL - how to fix function creation error 1418

Question

Im using mysqlWorkbench

I have followed example work supplied by my tutor and suggestions from other users on this sight but am now recieving a 1418 error when trying to create the function, as shown in the following:

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) 0.00069 sec

The function is as follows:

DELIMITER £
CREATE FUNCTION Calc(ReferenceNumber INTEGER)
RETURNS INTEGER

BEGIN 
DECLARE NUMCATS INTEGER;
    SELECT SUM(BOOKCAT.Ref = ReferenceNumber) INTO NUMCATS
    FROM CATTERY.BOOKCAT;
    RETURN NUMCATS;
END £
DELIMITER ;

select calc(7);

Let me know what i am doing wrong and how to correct it, thanks.

How/what ide are you using to create? I would probably avoid using £ as a delimiter — P.Salmon, Apr 14 '20 at 10:29

score 1 · Answer 1 · answered Apr 14 '20 at 10:29

The error message is clear enough. You need to add one of the mentionned keywords in the function definition to make it valid.

Consider:

DELIMITER £
CREATE FUNCTION Calc(ReferenceNumber INTEGER)
RETURNS INTEGER
DETERMINISTIC
BEGIN 
DECLARE NUMCATS INTEGER;
    SELECT SUM(BOOKCAT.Ref = ReferenceNumber) INTO NUMCATS
    FROM CATTERY.BOOKCAT;
    RETURN NUMCATS;
END £
DELIMITER ;

Demo on DB Fiddle

MySQL - how to fix function creation error 1418

1 Answers1