MySQL Bugs: #16456: RBR: rpl_sp.test expects query to fail, but passes in RBR

Bug #16456	RBR: rpl_sp.test expects query to fail, but passes in RBR
Submitted:	12 Jan 2006 15:56	Modified:	3 Jan 2007 9:26
Reporter:	Jonathan Miller	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Row Based Replication ( RBR )	Severity:	S2 (Serious)
Version:	5.1.5-alpha	OS:	Linux (Linux)
Assigned to:	Tatiana Azundris Nuernberg	CPU Architecture:	Any

Description:
mysqltest: At line 185: query 'create function fn1(x int)
returns int
begin
insert into t1 values (x);
return x+2;
end' succeeded - should have failed with errno 1418...

How to repeat:
Run the rpl_sp.test with --mysqld=--binlog-format=row

If you think this should pass with RBR, then we could change the --error to include 0 and close this bug.

Let me know.

Currently it is illegal to define a stored function (SF) without
DETERMINISTIC keyword when the binlog is enabled.  This because it can
not be correctly logged in SBR.

Since it is now also possible to dynamically change binlog format, we
face a new situation.

I think we have three options.

Alternative 1
-------------
We consider format change to be a highly riskful task and only
allow change with the SUPER privilage (this also for the SESSION
version).

We maintain the error for STATEMENT binlog format but make it legal to
construct non-deterministic stored function in ROW and MIXED format.

Changing the logging format can cause the replication to fail.

Alternative 2
-------------
We consider format change to be a natural thing.  Some queries are
better replicated as RBR and some as SBR, so any user can change
format at any time.

This puts higher demands on that everything should work regardless of
what kind of logging is enabled.

1. Binlog enabled (any format) + Defining non-determ SF -> Warning
2. Executing non-determ SF and ROW format -> Ok
3. Executing non-determ SF and STATEMENT format -> Error
4. Executing non-determ SF and MIXED format -> Ok

This forces us to implement a check at SF runtime.

Alternative 3
-------------
Same as 2 but no warning on definition.

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/15321

ChangeSet@1.2376, 2006-11-15 01:27:39+01:00, tnurnberg@salvation.intern.azundris.com +7 -0
  Bug#16456 "RBR: rpl_sp.test expects query to fail, but passes in RBR"
  
  calling (rather than defining) non-deterministic SP in SBR (as opposed
  to RBR or mixed) will throw an error now.
  
  require mixed mode for tests now. SBR will now fail when calling
  non-deter SPs and SFs (as it should), and RBR already failed by virtue of
  giving different results for "show binlog" than the results-file has.
  also test for 16456 now. lastly make amends because one of the tests
  fails with a new error # now as code was added to sql_trigger.cc while
  test was disabled.

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/15523

ChangeSet@1.2372, 2006-11-17 21:30:28+01:00, tnurnberg@salvation.intern.azundris.com +26 -0
  Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR
  
  Fix tests for new behaviour: an error is thrown if a NON DETERMINISTIC
  stored function (SF) is called during statement-based replication (SBR).

pushed to 5.1-maint (5.1.13)

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented behaviour change in 5.1.15 changelog.