MySQL Bugs: #20188: REPLACE or ON DUPLICATE KEY UPDATE in auto

Bug #20188	REPLACE or ON DUPLICATE KEY UPDATE in auto_increment breaks binlog
Submitted:	1 Jun 2006 8:12	Modified:	1 Aug 2006 3:09
Reporter:	Guilhem Bichot	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.0	OS:	Linux (linux)
Assigned to:	Guilhem Bichot	CPU Architecture:	Any

Description:
in the testcase below, I observe that in the end master and slave contain different values, while they should be identical.
The testcase forces a difference in the auto_increment counter of the MyISAM table, but replication is expected to still work fine (thanks to the INSERT_ID variable which is set in the slave).
The problem is that REPLACE, when it internally does an UPDATE, does thd->next_insert_id=0, which makes the slave forget the INSERT_ID read from the binlog. So slave then generates a new value based on its auto_inc counter, differing from master's.
This thd->next_insert_id=0 is a consequence of a too early call to restore_auto_increment() in mysql_insert() (at a stage where in fact we can't know we can restore auto_increment).

How to repeat:
# create rpl_bug.test:

source include/master-slave.inc;
--disable_warnings
drop database if exists mysqltest;
--enable_warnings
create database mysqltest;

create table mysqltest.t1 (n int primary key auto_increment not null,
b int, unique(b));
insert into mysqltest.t1 values(null,100);
select * from mysqltest.t1;
sync_slave_with_master;
# make slave's table autoinc counter bigger (replication is
# supposed bear that)
insert into mysqltest.t1 values(null,200),(null,300);
delete from mysqltest.t1 where b <> 100;
# check that slave's table content is identical to master
select * from mysqltest.t1;
# only the auto_inc counter differs.
connection master;
# prove that it's wrong that REPLACE sets thd->next_insert_id=0 when
# it does an UPDATE
replace into mysqltest.t1 values(null,100),(null,350);
select * from mysqltest.t1;
sync_slave_with_master;
select * from mysqltest.t1;
connection master;
drop database mysqltest;
sync_slave_with_master;

# RESULTS I SEE:
+ stop slave;
+ drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+ reset master;
+ reset slave;
+ drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+ start slave;
+ drop database if exists mysqltest;
+ create database mysqltest;
+ create table mysqltest.t1 (n int primary key auto_increment not null,
+ b int, unique(b));
+ insert into mysqltest.t1 values(null,100);
+ select * from mysqltest.t1;
+ n     b
+ 1     100
+ insert into mysqltest.t1 values(null,200),(null,300);
+ delete from mysqltest.t1 where b <> 100;
+ select * from mysqltest.t1;
+ n     b
+ 1     100
+ replace into mysqltest.t1 values(null,100),(null,350);
+ select * from mysqltest.t1;
+ n     b
+ 2     100
+ 3     350
+ select * from mysqltest.t1;
+ n     b
+ 2     100
+ 4     350
+ drop database mysqltest;
# see, master and slave differ (3!=4)

looks like the fix for this bug would be quite small, so I should be able to push it into 5.0 too.

See also BUG#19630

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/7357

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/7552

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/7562

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/7762

Funny: INSERT ON DUPLICATE KEY UPDATE has a similar bug, I will submit a total patch to fix both bugs.

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/7965

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/8104

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/8638

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/8754

pushed into replication team trees 5.0 and 5.1, will be in 5.0.24 and 5.1.12.
ChangeSet
  guilhem@mysql.com|ChangeSet|20060705124135|09883    2006/07/05 14:41:35+02:00 guilhem@mysql.com +5 -0
  Fix for BUG#20188 "REPLACE or ON DUPLICATE KEY UPDATE in
  auto_increment breaks binlog":
  if slave's table had a higher auto_increment counter than master's (even
  though all rows of the two tables were identical), then in some cases,
  REPLACE and INSERT ON DUPLICATE KEY UPDATE failed to replicate
  statement-based (it inserted different values on slave from on master).

Noted in 5.0.24, 5.1.12 changelogs.