Bug #27716 Partial modification by multi-table update not binlogged
Submitted: 9 Apr 2007 15:38 Modified: 23 Jun 2007 7:51
Reporter: Andrei Elkin Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.40/5.1BK OS:Any
Assigned to: Andrei Elkin CPU Architecture:Any

[9 Apr 2007 15:38] Andrei Elkin
Description:
A table might be modified after partially completed multi-update still
without recording anything in binlog. It is expected to have an event with a corresponding error code.

How to repeat:
# prepare

CREATE TABLE `i1` (
  `a` int(11) NOT NULL auto_increment,
  `b` int(11) default NULL,
  PRIMARY KEY  (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ;

CREATE TABLE `m2` (
  `a` int(11) NOT NULL auto_increment,
  `b` int(11) default NULL,
  PRIMARY KEY  (`a`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 ;

insert into i1 values (1,1),(2,2);
insert into m2 values (1,1),(4,4);
reset master;

# execute

mysql> UPDATE m2,i1  SET m2.a=i1.a+2;
ERROR 1062 (23000): Duplicate entry '3' for key 1

# check

mysql> select * from m2;
+---+------+
| a | b    |
+---+------+
| 3 |    1 | 
| 4 |    4 | 
+---+------+

mysql> show binlog events;
+-------------------+-----+-------------+-----------+-------------+---------------------------------------------+
| Log_name          | Pos | Event_type  | Server_id | End_log_pos | Info                                        |
+-------------------+-----+-------------+-----------+-------------+---------------------------------------------+
| master-bin.000001 |   4 | Format_desc |         1 |          98 | Server ver: 5.0.40-debug-log, Binlog ver: 4 | 
+-------------------+-----+-------------+-----------+-------------+---------------------------------------------+

Suggested fix:
Needs more analysis, from the first look
multi_update_table does not call join->result->send_error like select_insert would do in case like that.
[9 Apr 2007 16:23] Miguel Solorzano
Thank you for the bug report. Verified as described.
[14 May 2007 9:24] 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/commits/26578

ChangeSet@1.2439, 2007-05-14 12:24:20+03:00, aelkin@dsl-hkibras-fe31f900-164.dhcp.inet.fi +3 -0
  Bug #27716  	multi-update did partially and has not binlogged
  
  Implementation of mysql_multi_update did not call multi_update::send_error method in some cases 
  (see the test reported on bug page and test cases in changeset).
  
  Fixed with deploying the method, ::send_error() is refined to get binlogging code which works whenever 
  there is modified non-transactional table.
  thd->no_trans_update.stmt flag is set in to TRUE to ease testing though being the beginning of relative 
  bug#27417 fix.
[18 May 2007 12:08] 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/commits/26972

ChangeSet@1.2439, 2007-05-18 13:55:31+03:00, aelkin@dsl-hkibras-fe31f900-164.dhcp.inet.fi +3 -0
  Bug #27716  	multi-update did partially and has not binlogged
  
  Implementation of mysql_multi_update did not call multi_update::send_error method in some cases 
  (see the test reported on bug page and test cases in changeset).
  
  Fixed with deploying the method, ::send_error() is refined to get binlogging code which works whenever 
  there is modified non-transactional table.
  thd->no_trans_update.stmt flag is set in to TRUE to ease testing though being the beginning of relative 
  bug#27417 fix (addresses a part of those issues).
  Eliminating two minor issues (small bugs) in multi_update methods.
  This patch for multi-update also addresses a part of the issues reported in bug#13270,bug#23333.
[24 May 2007 11:19] 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/commits/27269

ChangeSet@1.2439, 2007-05-24 14:19:20+03:00, aelkin@dsl-hkibras1-ff5dc300-70.dhcp.inet.fi +3 -0
  Bug #27716  	multi-update did partially and has not binlogged
  
  Implementation of mysql_multi_update did not call multi_update::send_error method in some cases 
  (see the test reported on bug page and test cases in changeset).
  
  Fixed with deploying the method, ::send_error() is refined to get binlogging code which works whenever 
  there is modified non-transactional table.
  thd->no_trans_update.stmt flag is set in to TRUE to ease testing though being the beginning of relative 
  bug#27417 fix (addresses a part of those issues).
  Eliminating two minor issues (small bugs) in multi_update methods.
  This patch for multi-update also addresses a part of the issues reported in bug#13270,bug#23333.
[25 May 2007 10:08] 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/commits/27329

ChangeSet@1.2439, 2007-05-25 11:47:04+03:00, aelkin@dsl-hkibras1-ff5dc300-70.dhcp.inet.fi +5 -0
  Bug #27716  	multi-update did partially and has not binlogged
  
  Implementation of mysql_multi_update did not call multi_update::send_error method in some cases 
  (see the test reported on bug page and test cases in changeset).
  
  Fixed with deploying the method, ::send_error() is refined to get binlogging code which works whenever 
  there is modified non-transactional table.
  thd->no_trans_update.stmt flag is set in to TRUE to ease testing though being the beginning of relative 
  bug#27417 fix (addresses a part of those issues).
  Eliminating two minor issues (small bugs) in multi_update methods.
  This patch for multi-update also addresses a part of the issues reported in bug#13270,bug#23333.
[1 Jun 2007 8:14] 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/commits/27875

ChangeSet@1.2439, 2007-06-01 11:14:04+03:00, aelkin@dsl-hkibras1-ff5dc300-70.dhcp.inet.fi +5 -0
  Bug #27716  	multi-update did partially and has not binlogged
  
  Implementation of mysql_multi_update did not call multi_update::send_error method in some cases 
  (see the test reported on bug page and test cases in changeset).
  
  Fixed with deploying the method, ::send_error() is refined to get binlogging code which works whenever 
  there is modified non-transactional table.
  thd->no_trans_update.stmt flag is set in to TRUE to ease testing though being the beginning of relative 
  bug#27417 fix (addresses a part of those issues).
  Eliminating two minor issues (small bugs) in multi_update methods.
  This patch for multi-update also addresses a part of the issues reported in bug#13270,bug#23333.
[4 Jun 2007 11:46] 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/commits/28050

ChangeSet@1.2537, 2007-06-04 14:46:00+03:00, aelkin@dsl-hkibras1-ff5dc300-70.dhcp.inet.fi +7 -0
  Bug #27716  	multi-update did partially and has not binlogged
  
  manual merge with 5.0: automatic merge went incorrectly; fixing tests in rbr mode.
[4 Jun 2007 12:03] 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/commits/28052

ChangeSet@1.2537, 2007-06-04 15:02:40+03:00, aelkin@dsl-hkibras1-ff5dc300-70.dhcp.inet.fi +7 -0
  Bug #27716  	multi-update did partially and has not binlogged
  
  manual merge with 5.0: automatic merge went incorrectly; fixing tests in rbr mode.
[21 Jun 2007 20:11] Bugs System
Pushed into 5.0.46
[21 Jun 2007 20:14] Bugs System
Pushed into 5.1.20-beta
[23 Jun 2007 7:51] Jon Stephens
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

Bugfix documented as follows in 5.0.46 and 5.1.20 changelogs:

          The modification of a table by a partially completed
          multi-column update was not recorded in the binlog, rather
          than being marked by an event and a corresponding error code.

Updated synopsis.
[27 Nov 2007 10:48] Bugs System
Pushed into 5.0.54
[27 Nov 2007 10:50] Bugs System
Pushed into 5.1.23-rc
[27 Nov 2007 10:53] Bugs System
Pushed into 6.0.4-alpha