Bug #83090 AUTO_INCREMENT not updated when using INSERT ON DUPLICATE KEY UPDATE

• Submitted: 22 Sep 2016 2:13
• Modified: 22 Sep 2016 7:22
• 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

[22 Sep 2016 2:13] monty solomon

Description:
The table AUTO_INCREMENT value is not kept in synch between the master and the slave when using INSERT ON DUPLICATE KEY UPDATE.

This bug is related to bug #83030.

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';

INSERT INTO monty SET stub='foo' ON DUPLICATE KEY UPDATE stub=stub;

SELECT * FROM monty;
+----+------+
| id | stub |
+----+------+
|  2 | bar  |
|  1 | foo  |
+----+------+
2 rows in set (0.00 sec)

Master

mysql> SHOW  CREATE TABLE monty\G
*************************** 1. row ***************************
       Table: monty
Create Table: CREATE TABLE `monty` (
  `id` int(10) 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)

Slave

mysql> SHOW  CREATE TABLE monty\G
*************************** 1. row ***************************
       Table: monty
Create Table: CREATE TABLE `monty` (
  `id` int(10) 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 4 on the master and 3 on the slave.

[22 Sep 2016 2:20] monty solomon

Now execute the following statements on the Master

ALTER TABLE monty ADD COLUMN dummy BOOL NOT NULL DEFAULT '0';

mysql> INSERT INTO monty SET stub='foo' ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id+1), dummy=NOT dummy;
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'

mysql> INSERT INTO monty SET stub='foo' ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id+1), dummy=NOT dummy;
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'

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

Observe that the AUTO_INCREMENT value was incremented to six (6) even though the statements failed with ERROR 1062.

On the Slave observe that the AUTO_INCREMENT value is still three (3)

mysql> SHOW CREATE TABLE monty\G
*************************** 1. row ***************************
       Table: monty
Create Table: CREATE TABLE `monty` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `stub` char(15) NOT NULL,
  `dummy` tinyint(1) NOT NULL DEFAULT '0',
  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)

[22 Sep 2016 7:22] MySQL Verification Team

Hello monty,

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

Thanks,
Umesh