Bug #12844 Multi-update or mul-DELETE that calls func with side-effect goes not to binlog
Submitted: 26 Aug 2005 20:20 Modified: 28 Sep 2005 1:38
Reporter: Sergey Petrunya Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.0.13 OS:
Assigned to: Guilhem Bichot CPU Architecture:Any

[26 Aug 2005 20:20] Sergey Petrunya
Description:
Multi-table update statement that updates nothing but calls FUNCTION with side effect will get the slave out of sync - the statement is not written into binlog, and neither are FUNCTION calls.

How to repeat:
Run this test:
--- t/rpl_md.test ---
source include/master-slave.inc;
connection master;

create table t1(a int);
insert into t1 values (1),(2);
create table t2(a int);
insert into t2 values (1),(2);

create table t3 (a int);
delimiter //;

create function f1(a int) returns int deterministic modifies sql data
begin
  insert into t3 values(a);
  return 0;
end//

delimiter ;//

update t1,t2 set t1.a=3, t2.a=3 where f1(t1.a);

select 'master' A;
select * from t3;
sync_slave_with_master;
connection slave;
select 'slave' A;
select * from t3;
--- eof ---
and in .result file get:
...
master
select * from t3;
a
1
2
select 'slave' A;
A
slave
select * from t3;
a
-eof-

Suggested fix:
The problem is caused by the following piece of code:

sql/sql_update.cc: 1437
    ...
    Note that if we updated nothing we don't write to the binlog (TODO:
    fix this).
  */

  if (updated && (local_error <= 0 || !trans_safe))
  {
     <write to binlog here>
.
we should change " if (updated && " to 
 "if (updated && called-SPs-attempted-binlog-writes" ..

I'm not sure why Guilhem (author of the above comment) wanted to write multi-table updates to binlog. Will need to check that with him when he returns from vacation on Monday.
[13 Sep 2005 9:20] Guilhem Bichot
And problem may happen as well with:
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).
LOAD DATA INFILE is affected too before of SET clause.
[13 Sep 2005 9:37] Guilhem Bichot
waiting for Elliot's answer for reassignment
[16 Sep 2005 15:47] Guilhem Bichot
Back to the original problem of multi-UPDATE, which can be fixed easily. The new problem, much harder to fix, and maybe not a showstopper, is how filed as BUG#13270.
[18 Sep 2005 11:05] Guilhem Bichot
Fixing it leads to a multi-UPDATE with function going into binlog (normal), which crashes slave, I filed it as BUG#13305. BUG#12844 will be fixed when 13305 is. Setting back to "Verified".
[18 Sep 2005 19:26] Guilhem Bichot
will fix this bug, push a test file which will be in a disabled state (because there is no way my testcase can avoid BUG#13305), and fixer of BUG#13305 will just have to enable this testcase.
[19 Sep 2005 13:29] Guilhem Bichot
verified with multi-DELETE too (sorry for bad English of the new synopsis, but the max number of chars is small in this field).
[19 Sep 2005 13:50] Bugs System
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/internals/30047
[20 Sep 2005 12:24] Sergei Golubchik
It's a duplicate of BUG#13270.
It'll be fixed with a fix for BUG#13348
[20 Sep 2005 13:07] Guilhem Bichot
Serg:
BUG#13270 happens only if the statement has an error, which is a different case of the one here (where there is no error). BUG#13270 is the continuation of SergeyP's work which he already did on SELECT and SET.
This BUG#12844 is specific to multi-UPDATE and multi-DELETE (because those don't write to binlog if no rows affected), other statements are not touched. BUG#13270 is about lots of statements (INSERT etc). It's not a duplicate of BUG#13270.
Now it's a duplicate of BUG#13348.
[20 Sep 2005 15:48] Guilhem Bichot
see BUG#13348 for status info (as this is a duplicate)
[28 Sep 2005 1:38] Mike Hillyer
Added 5.0.14 functionality change note:

<listitem>
        <para>
          Multi-table <literal>UPDATE</literal> and
          <literal>DELETE</literal> statements are now written to the
          binary log and will replicate. (Bug #13348, Bug #12844)
        </para>
      </listitem>
[28 Sep 2005 1:39] Mike Hillyer
Slight modification to note:

<listitem>
        <para>
          Multi-table <literal>UPDATE</literal> and
          <literal>DELETE</literal> statements that do not affect any
         rows are now written to the binary log and will replicate. (Bug #13348, Bug #12844)
        </para>
      </listitem>