MySQL Bugs: #65116: REPLACE INTO does not update auto increment when replicated row based

Bug #65116	REPLACE INTO does not update auto increment when replicated row based
Submitted:	26 Apr 2012 13:44	Modified:	20 Jul 2018 10:33
Reporter:	Art van Scheppingen	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Row Based Replication ( RBR )	Severity:	S3 (Non-critical)
Version:	5.1.x, 5.1.64, 5.5.25, 5.6.6	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	auto increment, replace into, replication, row based replication

Description:
A query using the REPLACE INTO construction to replace a single row with auto increment does not increase the auto increment value on the slave when using row based replication.

It does not work in 5.1, but it does update it correctly in 5.5. I've verified this with several 5.1.x versions (including 5.1.62) and with 5.5 with both statement- and row-based replication

How to repeat:
Create this table:
CREATE TABLE `counter_test` (
  `counter` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `counter_number` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY (`counter`),
  UNIQUE KEY `counter_number` (`counter_number`)
) ENGINE=InnoDB AUTO_INCREMENT=50;

Prime the master with the counter:
REPLACE INTO `counter_test` (`counter_number`) VALUES (1);

This will result in the initial insert:
(root@localhost) [counter]> select * from counter_test;
+----------+----------------+
| counter  | counter_number |
+----------+----------------+
|       50 |              1 |
+----------+----------------+
1 row in set (0.00 sec)

performing a "show table status like 'counter_test';" on both master and slave will show 51 as the next auto increment. And this is correct on both master and slave.

Now increase the counter with a replace:
REPLACE INTO `counter_test` (`counter_number`) VALUES (1);

This will result in a correct value on both machines:
(root@localhost) [counter]> select * from counter_test;
+----------+----------------+
| counter  | counter_number |
+----------+----------------+
|       51 |              1 |
+----------+----------------+
1 row in set (0.00 sec)

But performing the "show table status like 'counter_test';" will show that the master has 52 as its next auto increment while the slave is still at 51.

Adding a new counter through a non violating key does set it correctly:
REPLACE INTO `counter_test` (`counter_number`) VALUES (2);

select * from counter_test;
+---------+----------------+
| counter | counter_number |
+---------+----------------+
|      50 |              1 |
|      52 |              2 |
+---------+----------------+
2 rows in set (0.00 sec)

And also the "show table status like 'counter_test';" reveals that the auto increment value has been set to 53 on both master and slave.

Thank you for the report.

Verified as described. Problem only repeatable if storage engine of table on slave is InnoDB.

MTR test case used

Attachment: rpl_bug65116.test (application/octet-stream, text), 780 bytes.

slave option file

Attachment: rpl_bug65116-slave.opt (application/octet-stream, text), 29 bytes.

For me problem is repeatable with versions 5.5 and 5.6 as well

This bug has been marked as a duplicate of Bug#73563