| Bug #95988 | create function does not accept 'NOT DETERMINISTIC' | ||
|---|---|---|---|
| Submitted: | 26 Jun 2019 2:14 | Modified: | 7 Feb 2020 6:24 |
| Reporter: | Wayne John | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Stored Routines | Severity: | S1 (Critical) |
| Version: | 8 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[26 Jun 2019 12:11]
Peter Laursen
The error message in MySQL 8 (in 5.7 there is no error) is 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) But I think it is inteentional when binary logging is enabled - refer https://dev.mysql.com/doc/refman/8.0/en/stored-programs-logging.html. "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" .. however I don't see why this particular example would be unsafe to replicate, as it does not write any data. Anyway it seems that the default for this 'log_bin_trust_function_creators' variable has changed between 5.7 and 8.0 But I do think there is another bug: SHOW VARIABLES does not list 'log_bin_trust_function_creators' variable (though you can change it successfully with a SET GLOBAL) -- Peter -- not a MySQL/Oracle person
[26 Jun 2019 12:37]
Peter Laursen
I have posted this report: https://bugs.mysql.com/bug.php?id=96002
[27 Jun 2019 13:05]
MySQL Verification Team
Verified as described, thanks for your report
Bogdan
> select @@version $$ CREATE FUNCTION currentTimeMillis()
+-----------+
| @@version |
+-----------+
| 5.7.26 |
+-----------+
1 row in set (0.00 sec)
-> RETURNS BIGINT NOT DETERMINISTIC
-> BEGIN
-> RETURN UNIX_TIMESTAMP()*1000;
-> END;$$
Query OK, 0 rows affected (0.00 sec)
---
> select @@version $$ CREATE FUNCTION currentTimeMillis()
+-----------+
| @@version |
+-----------+
| 8.0.16 |
+-----------+
1 row in set (0.00 sec)
-> RETURNS BIGINT NOT DETERMINISTIC
-> BEGIN
-> RETURN UNIX_TIMESTAMP()*1000;
-> END;$$
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)
[7 Feb 2020 6:24]
Erlend Dahl
Posted by developer / Jens Even Blomsoy
Bug #29966036 CREATE FUNCTION DOES NOT ACCEPT 'NOT DETERMINISTIC'
I believe this bug is not a bug.
************************MYSQL 5.7***********************'
5.7 docs, "To create or alter a stored function,
you must have the SUPER privilege, in addition to
the CREATE ROUTINE or ALTER ROUTINE privilege that
is normally required. (Depending on the DEFINER
value in the function definition, SUPER might be
required regardless of whether binary logging is
enabled. See Section 13.1.16, ???CREATE PROCEDURE
and CREATE FUNCTION Syntax???.)"
so the docs say when binlogging's on, you should
not even be able to CREATE FUNCTION without SUPER
It seemed like 5.7 wasn't behaving as per the docs,
which makes it very possible that 5.7 was wrong.
Under you can see that 5.7 allows creation regardless of
correct privilege.
+-----------------------------+
| GRANTEE |
+-----------------------------+
| 'mysql.session'@'localhost' |
| 'root'@'localhost' |
+-----------------------------+
mysql> SHOW VARIABLES LIKE '%log_bin_trust_function_creators%';$$
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF |
+---------------------------------+-------+
mysql>
-> $$CREATE FUNCTION currentTimeMillis()
-> RETURNS BIGINT NOT DETERMINISTIC
-> BEGIN
-> RETURN UNIX_TIMESTAMP()*1000;
-> END;$$
Query OK, 0 rows affected (0.00 sec)
mysql>
-> $$CREATE FUNCTION currentTimeMillis()
-> RETURNS BIGINT DETERMINISTIC
-> BEGIN
-> RETURN UNIX_TIMESTAMP()*1000;
-> END;$$
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW GRANTS;
+-----------------------------------------------------------------------------
---+
| Grants for newuser@localhost
|
+-----------------------------------------------------------------------------
---+
| GRANT CREATE, CREATE ROUTINE ON *.* TO 'newuser'@'localhost' WITH GRANT
OPTION |
+-----------------------------------------------------------------------------
---+
mysql>
-> $$CREATE FUNCTION currentTimeMillis()
-> RETURNS BIGINT NOT DETERMINISTIC
-> BEGIN
-> RETURN UNIX_TIMESTAMP()*1000;
-> END;$$
Query OK, 0 rows affected (0.00 sec)
mysql>
-> $$CREATE FUNCTION currentTimeMillis()
-> RETURNS BIGINT DETERMINISTIC
-> BEGIN
-> RETURN UNIX_TIMESTAMP()*1000;
-> END;$$
Query OK, 0 rows affected (0.01 sec)
************************MYSQL 8.0****************************
How it works
mysql> SELECT GRANTEE FROM INFORMATION_SCHEMA.USER_PRIVILEGES
-> WHERE PRIVILEGE_TYPE = 'SUPER';
+-----------------------------+
| GRANTEE |
+-----------------------------+
| 'mysql.session'@'localhost' |
| 'root'@'localhost' |
+-----------------------------+
mysql> show variables like 'log_bin_trust_function_creators';$$
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF |
+---------------------------------+-------+
mysql>
-> $$CREATE FUNCTION currentTimeMillis()
-> RETURNS BIGINT NOT DETERMINISTIC
-> BEGIN
-> RETURN UNIX_TIMESTAMP()*1000;
-> END;$$
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)
mysql>
-> $$CREATE FUNCTION currentTimeMillis()
-> RETURNS BIGINT DETERMINISTIC
-> BEGIN
-> RETURN UNIX_TIMESTAMP()*1000;
-> END;$$
Query OK, 0 rows affected (0.01 sec)
+-----------------------------------------------------------------------------
------------------+
| Grants for newuser@localhost
|
+-----------------------------------------------------------------------------
------------------+
| GRANT CREATE, CREATE ROUTINE, ALTER ROUTINE ON *.* TO `newuser`@`localhost`
WITH GRANT OPTION |
+-----------------------------------------------------------------------------
------------------+
1 row in set (0.00 sec)
mysql>
-> $$CREATE FUNCTION currentTimeMillis()
-> RETURNS BIGINT NOT DETERMINISTIC
-> BEGIN
-> RETURN UNIX_TIMESTAMP()*1000;
-> END;$$
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)
mysql>
-> $$CREATE FUNCTION currentTimeMillis()
-> RETURNS BIGINT DETERMINISTIC
-> BEGIN
-> RETURN UNIX_TIMESTAMP()*1000;
-> END;$$
ERROR 1419 (HY000): 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)
How it was described
To create or alter a stored function, you must have the SET_USER_ID or
SUPER privilege, in addition to the CREATE ROUTINE or ALTER ROUTINE
privilege that is normally required. (Depending on the DEFINER value
in the function definition, SET_USER_ID or SUPER might be required
regardless of whether binary logging is enabled. See Section 13.1.17,
???CREATE PROCEDURE and CREATE FUNCTION Syntax???.)
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.
Conclusion
In conclusion, mysql-5.7 was divergent from the docs.
mysql-8.0 works as intended as you have to have the super privilege
and you have to specify if the stored function is deterministic or
that it does not modify data, in this regards the error is true.
Description: The following function is marked as error: CREATE FUNCTION currentTimeMillis() RETURNS BIGINT NOT DETERMINISTIC BEGIN RETURN UNIX_TIMESTAMP()*1000; END;// If 'NOT' is removed, it will pass, but wrong. How to repeat: The following function is marked as error: CREATE FUNCTION currentTimeMillis() RETURNS BIGINT NOT DETERMINISTIC BEGIN RETURN UNIX_TIMESTAMP()*1000; END;// If 'NOT' is removed, it will pass, but wrong.