| 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: | |
| Category: | MySQL Server: Replication | Severity: | S3 (Non-critical) |
| Version: | 5.0.13 | OS: | |
| Assigned to: | Guilhem Bichot | CPU Architecture: | Any |
[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>
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.