MySQL Bugs: #13270: INSERT,UPDATE,etc that calls func with side-effect does not go into binlog

Bug #13270	INSERT,UPDATE,etc that calls func with side-effect does not go into binlog
Submitted:	16 Sep 2005 15:45	Modified:	16 May 2007 18:39
Reporter:	Guilhem Bichot	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.0	OS:	Linux (linux)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
As I move BUG#12844 to its original topic (multi-update), I create a new bug report for this new problem which has been discovered by SergeyP while discussing BUG#12844. This new problem is quite hard to fix, and as it may not be a showstopper, while original BUG#12844 was, it's another reason for separating.
The new problem may happen for INSERT, UPDATE, DELETE, the multi- variants, LOAD DATA INFILE. Consider for example this case:
table has a PK, INSERT INTO t VALUES(1);
INSERT INTO t VALUES(function_w_side_effect_which_returns_1);
then function will be called, will do its side effects, then will return 1 and
INSERT will fail so no binlogging (because no rows updated in the table AND
there was an error, so INSERT does not write itself to binlog).
LOAD DATA INFILE is affected too because of the SET clause.

How to repeat:
see description.

Suggested fix:
Always write INSERT/UPDATE/etc to binlog (even if no rows and if insertion/update to table had an error) if binlog_union is true.

This problem affect any statement, including SELECT, DO, and SET.

Serg: SELECT and SET are not affected (SergeyP fixed them already).

Rough solution:

Check the following variables (MySQL 5.1):

    /*
      If true, mysql_bin_log::write(Log_event) call will not write events to
      binlog, and maintain 2 below variables instead (use
      mysql_bin_log.start_union_events to turn this on)
    */
    bool do_union;

    /*
      If TRUE, at least one mysql_bin_log::write(Log_event) call has been
      made after last mysql_bin_log.start_union_events() call.
    */
    bool unioned_events;

    /*
      If TRUE, at least one mysql_bin_log::write(Log_event e), where
      e.cache_stmt == TRUE call has been made after last
      mysql_bin_log.start_union_events() call.
    */
    bool unioned_events_trans;

This is how it works currently:

- In a function call, start_union_events() start the temporary block
  of events until stop_union_events() is called.  The unioned_events
  variable keeps track of whether any binlog writes needed to be done.
- If an INSERT does not generate any new rows, it is not logged.

This is how it should work:

- If a function is called and thus start_union_events is running, then
  check the varaible thd->unioned_events.  If this is >0, then make sure
  that the INSERT is logged even though it did not generate any rows.

Bug #23333 stored function + non-transac table + transac table = breaks stmt-based binlog
examplifies a possible situation.

Will be fixed with bug#23333 patch.

I'm changing status to 'patch pending' based on Andrei's comment
that this bug will be fixed by the patch for bug#23333; see
http://lists.mysql.com/commits/22831

Pushed into 5.0.46

Pushed into 5.1.20-beta

Not pushed actually, there is a a bug in pushbuild's buglist generator that interprets an auxiliary bugs list along cset comments after the target bug - which is the only one is supposed to get into the list.