MySQL Bugs: #93807: MODIFIES SQL DATA is not supported

Bug #93807	MODIFIES SQL DATA is not supported
Submitted:	3 Jan 2019 18:42	Modified:	10 Jan 2019 10:34
Reporter:	Frederic Steinfels	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	8.0.13	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When creating a function, there is supposed the (although currently rather useless?) "MODIFIES SQL DATA" phrase as part of the characteristics argument.

https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html

However when using it, I will get

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

If I replace "MODIFIES SQL DATA" with "READS SQL DATA", all goes well.

How to repeat:
Use the sql file attached, put in your database name and apply it

mysql -u user -p </dvd/sqlroutines.txt

Suggested fix:
Be consistant with your documentation

How to recreate

Attachment: sqlroutines.txt (text/plain), 1.22 KiB.

Hello Frederic,

Thank you for the report and test case.
Imho this is expected behavior when binary logging is enabled i.e quoting from manual "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)".

Please see https://dev.mysql.com/doc/refman/8.0/en/stored-programs-logging.html
https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html
https://dev.mysql.com/doc/refman/8.0/en/replication-rbr-safe-unsafe.html

I quickly modified your function and indeed it allows "MODIFIES SQL DATA".

mysql> DELIMITER ;
mysql> DELIMITER ;;
mysql> CREATE DEFINER=`root`@`localhost` FUNCTION `UPDATEPRODUCT`(`pid` INT(11))
    -> RETURNS char(1)
    -> DETERMINISTIC MODIFIES SQL DATA
    -> BEGIN
    -> DECLARE cnt INT(11);
    ->   RETURN(1);
    -> END ;;
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

mysql> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 8.0.13                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| tls_version             | TLSv1,TLSv1.1,TLSv1.2        |
| version                 | 8.0.13                       |
| version_comment         | MySQL Community Server - GPL |
| version_compile_machine | x86_64                       |
| version_compile_os      | linux-glibc2.12              |
| version_compile_zlib    | 1.2.11                       |
+-------------------------+------------------------------+
9 rows in set (0.01 sec)

Could you please try and confirm this? Thank you!

regards,
Umesh