MySQL Bugs: #27716: Partial modification by multi-table update not binlogged

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

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.

Thank you for the bug report. Verified as described.

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.

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.

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.

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.

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.

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.

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.

Pushed into 5.0.46

Pushed into 5.1.20-beta

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.

Pushed into 5.0.54

Pushed into 5.1.23-rc

Pushed into 6.0.4-alpha