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

[1 Jun 2006 8:12] Guilhem Bichot
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)
[3 Jun 2006 8:29] Guilhem Bichot
looks like the fix for this bug would be quite small, so I should be able to push it into 5.0 too.
[3 Jun 2006 9:50] Lars Thalmann
See also BUG#19630
[7 Jun 2006 14:29] 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/7357
[12 Jun 2006 22:06] 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/7552
[13 Jun 2006 10:33] 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/7562
[16 Jun 2006 13:53] 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/7762
[18 Jun 2006 20:58] Guilhem Bichot
Funny: INSERT ON DUPLICATE KEY UPDATE has a similar bug, I will submit a total patch to fix both bugs.
[20 Jun 2006 20:16] 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/7965
[22 Jun 2006 20:29] 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/8104
[3 Jul 2006 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/8638
[5 Jul 2006 12:42] 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/8754
[11 Jul 2006 8:49] Guilhem Bichot
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).
[1 Aug 2006 3:09] Paul Dubois
Noted in 5.0.24, 5.1.12 changelogs.