MySQL Bugs: #88633: Auto_increment value on a table is less than max(id) can happen

Bug #88633	Auto_increment value on a table is less than max(id) can happen
Submitted:	24 Nov 2017 3:08	Modified:	19 Dec 2017 13:00
Reporter:	Jericho Rivera	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.7, 5.7.20	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
It is possible to have max(id) greater than the table's auto_increment value on a replication cluster. I've only been able to reproduce this on 5.7 instances (tested 5.7.17 and 5.7.20). This affects regardless if it's master-master or master-slave replication. Only the primary master is affected, slaves will have correct max(id) and auto_increment value on table.

Relevant config variables:
Master:
auto_increment_increment=10
auto_increment_offset=1
innodb_autoinc_lock_mode=2

Slave:
auto_increment_increment=10
auto_increment_offset=2
innodb_autoinc_lock_mode=2

FWIW, I tested on 5.6.38 but couldn't reproduce the same issue.

How to repeat:
~/sandboxes/rcsandbox_5_7_20$ ./use_all "show global variables like 'auto_inc%'"
# server: 1:
Variable_name	Value
auto_increment_increment	10
auto_increment_offset	1
# server: 2:
Variable_name	Value
auto_increment_increment	10
auto_increment_offset	2
~/sandboxes/rcsandbox_5_7_20$ ./use_all "show global variables like 'innodb_autoinc%'"
# server: 1:
Variable_name	Value
innodb_autoinc_lock_mode	2
# server: 2:
Variable_name	Value
innodb_autoinc_lock_mode	2

~/sandboxes/rcsandbox_5_7_20$ ./use_all "select @@version,@@version_comment"
# server: 1:
@@version	@@version_comment
5.7.20-log	MySQL Community Server (GPL)
# server: 2:
@@version	@@version_comment
5.7.20-log	MySQL Community Server (GPL)

# Create a simple table with auto_increment column
~/sandboxes/rcsandbox_5_7_20$ ./n1 -uroot -e "create table test.foo (id int auto_increment not null primary key, dt timestamp not null default current_timestamp) engine=innodb"
~/sandboxes/rcsandbox_5_7_20$ ./use_all "show create table test.foo\G"
# server: 1:
*************************** 1. row ***************************
       Table: foo
Create Table: CREATE TABLE `foo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
# server: 2:
*************************** 1. row ***************************
       Table: foo
Create Table: CREATE TABLE `foo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

# Run a continues insert into the table:
~/sandboxes/rcsandbox_5_7_20$ while true; do ./n1 -e "insert into test.foo values ()"; sleep 1; done

# In a separate terminal do:
jerichorivera@percona:~/sandboxes/rcsandbox_5_7_20$ ./n1 -uroot -e "set global auto_increment_increment=1"
jerichorivera@percona:~/sandboxes/rcsandbox_5_7_20$ ./n2 -uroot -e "set global auto_increment_increment=1"

# End the continues insert to the table once you see duplicate entry errors like these two below:
ERROR 1062 (23000) at line 1: Duplicate entry '260' for key 'PRIMARY'
ERROR 1062 (23000) at line 1: Duplicate entry '261' for key 'PRIMARY'
^C
~/sandboxes/rcsandbox_5_7_20$ ./n1 -e "show create table test.foo\G"
*************************** 1. row ***************************
       Table: foo
Create Table: CREATE TABLE `foo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=263 DEFAULT CHARSET=latin1
jerichorivera@percona:~/sandboxes/rcsandbox_5_7_20$ ./n1 -e "select max(id) from test.foo"
+---------+
| max(id) |
+---------+
|     372 |
+---------+

Suggested fix:
Check difference between 5.6 and 5.7 and why only 5.7 is affected then follow 5.6 behavior?

In fact, there is really no need to have a slave. Just bring up one instance set auto_increment_increment > 0, insert to the table and change auto_increment_increment to 1 and be able to reproduce it.

:~/sandboxes/msb_5_7_20$ ./use -e "show create table test.foo\G"
*************************** 1. row ***************************
       Table: foo
Create Table: CREATE TABLE `foo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=latin1
:~/sandboxes/msb_5_7_20$ ./use -e "select max(id) from test.foo"
+---------+
| max(id) |
+---------+
|      35 |
+---------+

Hello Jericho Rivera,

Thank you for the report and test case.

Thanks,
Umesh

test results

Attachment: 88633.results (application/octet-stream, text), 10.04 KiB.