MySQL Bugs: #41166: stored function requires "deterministic" if binlog

Bug #41166	stored function requires "deterministic" if binlog_format is "statement"
Submitted:	2 Dec 2008 8:04	Modified:	5 Aug 2009 10:29
Reporter:	Devananda van der Veen	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.1	OS:	Any
Assigned to:	Alfranio Tavares Correia Junior	CPU Architecture:	Any
Tags:	--log-slave-updates, replication, stored function

Description:
There are really two errors here, and I'm not sure how they are related.

1) Manual page http://dev.mysql.com/doc/refman/5.1/en/stored-programs-logging.html states that one must specify "at least one of DETERMINISTIC, NO SQL, or READS SQL DATA", but in 5.1.28 and 5.1.30, when @@binlog_format is STATEMENT, the server refuses to execute a stored function if DETERMINISTIC is not specified (even if READS SQL DATA is specified). The function call works if @@binlog_format is MIXED or the function is defined as DETERMINISTIC.

2) When the above error happens, the server claims
"ERROR 1536 (HY000): Slave running with --log-slave-updates must use row-based binary logging to be able to replicate row-based binary log events"
even when --log-slave-updates is not enabled and the slave process is not configured.

Here is transcript of error happening on a test machine, RHEL 4 i386, 5.1.30, log slave updates OFF, and slave process unconfigured. Same error (and same error message) happened on production machine, RHEL 5 x86_64, 5.1.30, log slave updates ON, active slave.

# mysql test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.1.30-community-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

localhost:test> drop function if exists my_t;
Query OK, 0 rows affected (0.01 sec)

localhost:test> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000019 |    19207 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

localhost:test> show slave status\G
Empty set (0.00 sec)

localhost:test> show variables like 'log_slave%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| log_slave_updates | OFF   |
+-------------------+-------+
1 row in set (0.00 sec)

localhost:test> set @@binlog_format=statement;
Query OK, 0 rows affected (0.00 sec)

localhost:test> delimiter ;;
localhost:test> create function my_t(i int) returns int reads sql data begin return i * 2; end;;
Query OK, 0 rows affected (0.00 sec)

localhost:test> delimiter ;
localhost:test> select my_t(1);
ERROR 1536 (HY000): Slave running with --log-slave-updates must use row-based binary logging to be able to replicate row-based binary log events

-- the following demonstrate working cases

localhost:test> set @@binlog_format=mixed;
Query OK, 0 rows affected (0.00 sec)

localhost:test> select my_t(1);
+---------+
| my_t(1) |
+---------+
|       2 |
+---------+
1 row in set (0.00 sec)

localhost:test> set @@binlog_format=statement;
Query OK, 0 rows affected (0.00 sec)

localhost:test> drop function if exists my_t;
Query OK, 0 rows affected (0.00 sec)

localhost:test> delimiter ;;
localhost:test> create function my_t(i int) returns int deterministic reads sql data begin return i * 2; end;;
Query OK, 0 rows affected (0.00 sec)

localhost:test> delimiter ;
localhost:test> select my_t(1);
+---------+
| my_t(1) |
+---------+
|       2 |
+---------+
1 row in set (0.00 sec)

How to repeat:
set @@binlog_format=statement;

drop function if exists my_t;
delimiter ;;
create function my_t(i int) returns int reads sql data begin return i * 2; end;;
delimiter ;
select my_t(1);

Suggested fix:
1) allow function to be called and log the statement appropriately
2) return a proper or more descriptive error message

Thank you for the report.

I can not repeat described behavior with default settings. Please provide configuration file you use.

config file from test server

Attachment: my.cnf (application/octet-stream, text), 1.14 KiB.

I can repeat this, but i put binlog_format = STATEMENT in global my.cnf

OK.

To answer (1).. it states "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"

But this is the general case.. this is true but this does not mean the function is safe for Statement-based replication.. but is fine for row-based.

For (2) i think this error is true.. since function is not DETERMINISTIC.. it is not guaranteed 'safe' for statement-based replication so server refuses to execute it as it cannot log it.. admittedly the error is a touch misleading but overall is-accurate.

Changing to 'row-based' or 'mixed' resolves the problem since RBR is safe for nearly all procedures/fuctions.

In short this is "not a real bug" but in fact

(1) The error is slightly misleading.. perhaps it should say 'Cannot execute statement as it is not safe for statement-based replication
and
(2) Documentation should explicitly state what requirements there are for Statement or Row-based logging of procedures.

Note however I am not the most authoritative source on this information.. but I believe this to be the case.

oh and (3) arguably.. changing the binlog_format was an 'incompatible' change.. but 5.1 was not yet GA so this was acceptable.

I tested this replicating from a 5.0 master to a 5.1 slave (the slave's replication thread refuses to execute the function) and I think I understand what has happened:

1) 5.0 checked only at routine creation time
2) 5.0 does not check at routine execution, therefor non-deterministic routines which do not modify data (eg, have only "READS SQL DATA" defined) can be created and executed without setting log_bin_trust_function_creators=1
3) 5.1 has the same checks at routine creation time as 5.0, therefor function can be created
4) 5.1 also checks at routine execution time to determine whether a statement is safe to log and refuses to execute if binlog_format=STATEMENT and the function is not DETERMINISTIC, whether or not it modifies data.

Since the documentation states that routines can be created with either DETERMINISTIC or READS SQL DATA, this has lead me to the situation where a function that does not modify data was not declared deterministic, even though it is, and was used in a statement that modifies data (eg, INSERT INTO my_table VALUES (my_func(...))). 5.0 had no problem with this, so behavior appeared to match documentation, but now 5.1 refuses to execute it because it lacks the DETERMINISTIC flag and therefor is believed unsafe for STATEMENT logging.

#4 is not stated in the documentation, or if it is I haven't found it. I think adding that to http://dev.mysql.com/doc/refman/5.1/en/stored-programs-logging.html and correcting the error message would clear this up.

I added this to the logging section:

Although it is possible to create a deterministic stored function
without specifying DETERMINISTIC, as of MySQL 5.1.15, you cannot
execute this function using statement-based binary logging. To
execute such a function, you must use row-based or mixed binary
logging. Alternatively, if you explicitly specify DETERMINISTIC in
the function definition, you can use any kind of logging, including
statement-based logging.

By the way, I disagree with this comment:

"
[3 Dec 8:20] Trent Lloyd
...
For (2) i think this error is true.. since function is not DETERMINISTIC.. it is not
guaranteed 'safe' for statement-based replication so server refuses to execute it as it
cannot log it.. admittedly the error is a touch misleading but overall is-accurate.
"

It's not accurate. The error was:

ERROR 1536 (HY000): Slave running with --log-slave-updates must use row-based binary
logging to be able to replicate row-based binary log events

But log_slave_updates was OFF. So the error message is making an assertion about the server configuration that is false.

This is a duplicate of BUG#39489.
For further details on how this will be fixed read comments on BUG#39489.

Sorry for the last comment, BUG#41166 is just related to BUG#39489.

BUG#41166 has two issues that should be fixed:

1) 5.1.x allows to create a function without a DETERMINISTIC clause but
requires such clause to execute it.

2) and a misleading error message as it is making an assertion about the
server configuration that is false: "Slave running with --log-slave-updates
must use row-based binary logging to be able to replicate row-based binary
log events"

So, we need to

1) either update the documentation as already suggested by Paul or make
the behavior in execution time equivalent to the behavior in creation
time.

2) print out an accurate error message.

However, the use of the DETERMINISTIC option should be treated in a broader
perspective as described in BUG#39489. In nutshell, the DETERMINISTIC option
does not prevent a user to create a function with non-deterministic statements
and we should in the future come up with a solution to circumvent this
problem.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/78898

3028 Alfranio Correia	2009-07-16
      BUG#41166 stored function requires "deterministic" if binlog_format is "statement"
      
      If the log_bin_trust_function_creators option is not defined, creating a stored
      function requires either one of the modifiers DETERMINISTIC, NO SQL, or READS
      SQL DATA. Executing a stored function should also follows the same rules if in
      STATEMENT mode. However, this was not happening and a wrong error was being
      printed out: ER_BINLOG_ROW_RBR_TO_SBR.
      
      The patch makes the creation and execution compatible and prints out the correct
      error ER_BINLOG_UNSAFE_ROUTINE when a stored function without one of the modifiers
      above is executed in STATEMENT mode.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/79440

3028 Alfranio Correia	2009-07-28
      BUG#41166 stored function requires "deterministic" if binlog_format is "statement"
      
      If the log_bin_trust_function_creators option is not defined, creating a stored
      function requires either one of the modifiers DETERMINISTIC, NO SQL, or READS
      SQL DATA. Executing a stored function should also follows the same rules if in
      STATEMENT mode. However, this was not happening and a wrong error was being
      printed out: ER_BINLOG_ROW_RBR_TO_SBR.
      
      The patch makes the creation and execution compatible and prints out the correct
      error ER_BINLOG_UNSAFE_ROUTINE when a stored function without one of the modifiers
      above is executed in STATEMENT mode.

Pushed into 5.4.4-alpha (revid:alik@sun.com-20090804194615-h40sa098mx4z49qg) (version source revid:alfranio.correia@sun.com-20090728224502-v9swy2yy3y9lscnp) (merge vers: 5.4.4-alpha) (pib:11)

Pushed into 5.1.38 (revid:davi.arnaut@sun.com-20090804204317-ggodqkik7de6nfpz) (version source revid:davi.arnaut@sun.com-20090804204317-ggodqkik7de6nfpz) (merge vers: 5.1.38) (pib:11)

Documented bugfix in the 5.1.38 and 5.4.4 changelogs, as follows:

        If the log_bin_trust_function_creators option is not enabled,
        CREATE FUNCTION requires one of the modifiers DETERMINISTIC, NO
        SQL, or READS SQL DATA. When using statement mode, the execution
        of a stored function should follow the same rules; however, only
        functions defined with DETERMINSTIC could actually be executed.
        In addition the wrong error was generated
        (ER_BINLOG_ROW_RBR_TO_SBR rather than ER_BINLOG_UNSAFE_ROUTINE).

        Now execution of stored functions is compatible with creation in
        this regard; when a stored function without one of the modifiers
        above is executed in STATEMENT mode, the correct error is
        raised, and functions defined using NO SQL, READS SQL DATA, or
        both (that is, without including DETERMINSTIC) can be executed.

Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.

Ignore previous comment about 5.4.2.

Pushed into 5.1.39-ndb-6.3.28 (revid:jonas@mysql.com-20091001055605-ap2kiaarr7p40mmv) (version source revid:jonas@mysql.com-20091001055605-ap2kiaarr7p40mmv) (merge vers: 5.1.39-ndb-6.3.28) (pib:11)

Pushed into 5.1.39-ndb-7.0.9 (revid:jonas@mysql.com-20091001072547-kv17uu06hfjhgjay) (version source revid:jonas@mysql.com-20091001071652-irejtnumzbpsbgk2) (merge vers: 5.1.39-ndb-7.0.9) (pib:11)

Pushed into 5.1.39-ndb-7.1.0 (revid:jonas@mysql.com-20091001123013-g9ob2tsyctpw6zs0) (version source revid:jonas@mysql.com-20091001123013-g9ob2tsyctpw6zs0) (merge vers: 5.1.39-ndb-7.1.0) (pib:11)

Pushed into 5.1.39-ndb-6.2.19 (revid:jonas@mysql.com-20091005103850-dwij2dojwpvf5hi6) (version source revid:jonas@mysql.com-20090930185117-bhud4ek1y0hsj1nv) (merge vers: 5.1.39-ndb-6.2.19) (pib:11)

The 5.4 fix has been pushed to 5.4.2.