Bug #44074 Non deterministic function gives silly error message
Submitted: 3 Apr 2009 11:52 Modified: 18 Jun 2012 7:35
Reporter: Oli Sennhauser Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Errors Severity:S3 (Non-critical)
Version:5.1, 6.0 bzr OS:Any
Assigned to: Jon Olav Hauglid CPU Architecture:Any

[3 Apr 2009 11:52] Oli Sennhauser
Description:
Execution of non-deterministic function give silly error message:

mysql> SELECT hello('jens');
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

How to repeat:
mysql> CREATE FUNCTION hello (input CHAR(20)) RETURNS CHAR(50) NO SQL RETURN CONCAT('Hello, ', input, '!');

show master status;
+----------------+----------+--------------+------------------+
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------+----------+--------------+------------------+
| bin-log.000020 |      398 |              |                  |
+----------------+----------+--------------+------------------+

show variables like '%binlog_forma%';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+

show variables like 'log_slave%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| log_slave_updates | OFF   |
+-------------------+-------+

mysql> select hello('jens');
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

This behavior affects >= 5.1.29 b/c then we changed the default from mixed to statement!

Suggested fix:
1. Make a more appropriate error message
[4 Apr 2009 18:51] Sveta Smirnova
Thank you for the report.

Verfieid as described.

Test case:

--source include/master-slave.inc

CREATE FUNCTION hello (input CHAR(20)) RETURNS CHAR(50) NO SQL RETURN
CONCAT('Hello, ', input, '!');

SELECT hello('jens');

sleep 2;

connection slave;

SELECT hello('jens');

Option file -slave.opt:

--log-bin --log-slave-updates --log_bin_trust_function_creators=0 --binlog_format=STATEMENT
[8 Apr 2009 15:49] Omer Barnir
This is the proper error.
A non deterministic function must be logged only with RBR even if it doesn't
happen to affect binlog content.
[14 Apr 2009 8:52] Sveta Smirnova
Omer, question is why it works fine on master with same binlog_format=STATEMENT? (Which is default currently)
[14 Apr 2009 8:52] Sveta Smirnova
So either this should fail in both cases or doesn't fail with log-slave-updates.
[7 Apr 2010 13:44] Andrii Nikitin
isn't it a duplicate of bug 41166 (fixed in 5.1.38)
[18 Jun 2012 7:35] Jon Olav Hauglid
Not repeatable on current trunk. No errors from master or slave.