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
• Status: Verified
• Category: MySQL Server: Row Based Replication ( RBR )
• Severity: S1 (Critical)
• Version: 5.7.13, 5.7.15
• OS: Any
• 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] MySQL Verification Team

Hello monty,

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

Thanks,
Umesh