MySQL stored function won't compile with MODIFIES SQL DATA

Question

If I compile the stored function below in MySQL (using the MySQL Workbench 8.0), I get the message:

ERROR 1418: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled...

However, if I change MODIFIES SQL DATA to READS SQL DATA, the function compiles just fine. The MySQL manual says:

MODIFIES SQL DATA indicates that the routine contains statements that may write data (for example, INSERT or DELETE).

So why is MODIFIES SQL DATA not accepted? I understand these characteristics are "advisory only", but I'd still like to use the correct characteristic.

CREATE FUNCTION DoSomething(employeeName VARCHAR(30)) RETURNS int(11)
    MODIFIES SQL DATA
BEGIN
   DECLARE income INT;

   SELECT Salary
   INTO income
   FROM Employee
   WHERE Name = employeeName;

   UPDATE Employee
   SET Salary = 300
   WHERE Name = employeeName;

   IF income < 5000 THEN
      RETURN 0;
   ELSE
      RETURN (income - 5000) * 0.1;
   END IF;
END

nbk nbk · Accepted Answer · 2020-03-27T21:36:55

In MySQL 8.0.19 and Workbench it is enough to use DETERMINISTIC

EDIT:

To clarify, the function options like DETERMINISTIC, NO SQL or READS SQL DATA doesn't decide nothing, it only indicates, that you have make a decision about its security. Functions that have NO SQL or READS SQL DATA clearly tell MySQL that there are no data manipulation inside and they are secure.

By using DETERMINISTIC you tell mysql that it is safe to run this function, which does manipulate data inside..

See the mysql explanation, which reads:

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.

CREATE DEFINER=`root`@`localhost` FUNCTION `DoSomething`(employeeName VARCHAR(30)) RETURNS int
    DETERMINISTIC
BEGIN
   DECLARE income INT;

   SELECT Salary
   INTO income
   FROM Employee
   WHERE Name = employeeName;

   UPDATE Employee
   SET Salary = 300
   WHERE Name = employeeName;

   IF income < 5000 THEN
      RETURN 0;
   ELSE
      RETURN (income - 5000) * 0.1;
   END IF;
END

Updates employee

MySQL stored function won't compile with MODIFIES SQL DATA

1 Answers