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:
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.0 OS:Linux (linux)
Assigned to: Assigned Account CPU Architecture:Any

[16 Sep 2005 15:45] Guilhem Bichot
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.
[20 Sep 2005 12:48] Sergei Golubchik
This problem affect any statement, including SELECT, DO, and SET.
[20 Sep 2005 13:08] Guilhem Bichot
Serg: SELECT and SET are not affected (SergeyP fixed them already).
[9 Aug 2006 19:31] Lars Thalmann
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.
[20 Dec 2006 18:52] Andrei Elkin
Bug #23333 stored function + non-transac table + transac table = breaks stmt-based binlog
examplifies a possible situation.
[24 Mar 2007 10:47] Andrei Elkin
Will be fixed with bug#23333 patch.
[30 Mar 2007 19:21] Trudy Pelzer
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
[21 Jun 2007 20:12] Bugs System
Pushed into 5.0.46
[21 Jun 2007 20:14] Bugs System
Pushed into 5.1.20-beta
[22 Jun 2007 15:32] Andrei Elkin
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.