Bug #83030 AUTO_INCREMENT not updated when using REPLACE INTO
Submitted: 17 Sep 2016 7:34 Modified: 19 Sep 2016 7:15
Reporter: monty solomon Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Row Based Replication ( RBR ) Severity:S1 (Critical)
Version:5.7.13, 5.7.15 OS:Any
Assigned to: CPU Architecture:Any

[17 Sep 2016 7:34] monty solomon
Description:
The table AUTO_INCREMENT value is not kept in synch between the master and the slave when using REPLACE INTO.

How to repeat:
CREATE TABLE `monty` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `stub` char(15) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `stub` (`stub`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

INSERT INTO monty SET stub = 'foo';
INSERT INTO monty SET stub = 'bar';

Master

mysql> SELECT @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| ROW             |
+-----------------+
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE monty\G
*************************** 1. row ***************************
       Table: monty
Create Table: CREATE TABLE `monty` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `stub` char(15) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `stub` (`stub`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)

Slave

mysql> SHOW CREATE TABLE monty\G
*************************** 1. row ***************************
       Table: monty
Create Table: CREATE TABLE `monty` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `stub` char(15) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `stub` (`stub`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)

Master

mysql> REPLACE INTO monty (stub) VALUES ('foo'); SHOW CREATE TABLE monty\G
Query OK, 2 rows affected (0.01 sec)

*************************** 1. row ***************************
       Table: monty
Create Table: CREATE TABLE `monty` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `stub` char(15) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `stub` (`stub`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)
 
mysql> REPLACE INTO monty (stub) VALUES ('bar'); SHOW CREATE TABLE monty\G
Query OK, 2 rows affected (0.01 sec)

*************************** 1. row ***************************
       Table: monty
Create Table: CREATE TABLE `monty` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `stub` char(15) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `stub` (`stub`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)

mysql> SELECT * from monty;
+----+------+
| id | stub |
+----+------+
|  4 | bar  |
|  3 | foo  |
+----+------+
2 rows in set (0.00 sec)

Slave

mysql> SELECT * from monty; SHOW CREATE TABLE monty\G
+----+------+
| id | stub |
+----+------+
|  4 | bar  |
|  3 | foo  |
+----+------+
2 rows in set (0.00 sec)

*************************** 1. row ***************************
       Table: monty
Create Table: CREATE TABLE `monty` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `stub` char(15) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `stub` (`stub`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)

Observe that the table AUTO_INCREMENT is 5 on the master and 3 on the slave.
[19 Sep 2016 7:15] Umesh Shastry
Hello monty,

Thank you for the report.
Observed this with 5.7.15 build.

Thanks,
Umesh