Bug #102004 auto_increment values got different modulo number
Submitted: 16 Dec 2020 4:24 Modified: 16 Dec 2020 13:51
Reporter: burke sephiroth (OCA) Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:8.0.21 OS:Ubuntu (ubuntu 2004 desktop x64)
Assigned to: CPU Architecture:Any
Tags: autoincr;MGR

[16 Dec 2020 4:24] burke sephiroth
Description:
The generation of autoincr is controled by two thread variables: auto_increment_offset and auto_increment_increment. Generally, If the two variables fixed, all new generated autoincr values got the same modulo number(autoincr % auto_increment_increment + 1 = auto_increment_offset)

However, when set auto_increment_offset > auto_increment_increment, the generated autoincr values may have different modulo. And in a multi-primary MGR cluster, the behaviour may lead to duplicate key conflict.

How to repeat:
##########################################################
# connect a mysql server of 8.0.21
# check the generated autoincr values
##########################################################

## generate autoincr values for [offset, increment] = [11, 7]

mysql> set auto_increment_increment=7;
Query OK, 0 rows affected (0.00 sec)

mysql> set auto_increment_offset=11;
Query OK, 0 rows affected (0.00 sec)

mysql> create database test;
Query OK, 1 row affected (0.01 sec)

mysql> use test;
Database changed
mysql> create table t1 (c1 int auto_increment primary key, c2 int) engine=innodb;
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t1(c2) values (1),(2),(3),(4);
Query OK, 4 rows affected (0.03 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> insert into t1(c2) values (1),(2),(3),(4);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> insert into t1(c2) values (1),(2),(3),(4);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+----+------+
| c1 | c2   |
+----+------+
|  2 |    1 |
|  9 |    2 |
| 11 |    3 |
| 18 |    4 |
| 46 |    1 |
| 53 |    2 |
| 60 |    3 |
| 67 |    4 |
| 74 |    1 |
| 81 |    2 |
| 88 |    3 |
| 95 |    4 |
+----+------+
12 rows in set (0.00 sec)

mysql> truncate table t1;
Query OK, 0 rows affected (0.05 sec)

## generate autoincr values for [offset, increment] = [69, 7]

mysql> set auto_increment_offset=69;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1(c2) values (1),(2),(3),(4);
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> insert into t1(c2) values (1),(2),(3),(4);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> insert into t1(c2) values (1),(2),(3),(4);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+----+------+
| c1 | c2   |
+----+------+
|  4 |    1 |
| 11 |    2 |
| 18 |    3 |
| 25 |    4 |
| 32 |    1 |
| 39 |    2 |
| 46 |    3 |
| 53 |    4 |
| 60 |    1 |
| 67 |    2 |
| 69 |    3 |
| 76 |    4 |
+----+------+
12 rows in set (0.00 sec)

## "offset % increment" is not same, but there has duplicated value such as: [11,18,60,67]

mysql> select 11%7;
+------+
| 11%7 |
+------+
|    4 |
+------+
1 row in set (0.00 sec)

mysql> select 69%7;
+------+
| 69%7 |
+------+
|    6 |
+------+
1 row in set (0.00 sec)

##########################################################
# connect a multi-primary MGR cluster of 8.0.21
# check the lock conflict on MGR
##########################################################

1. Build a MGR with three nodes, and set 'auto_increment_offset=69' on the first node, set 'auto_increment_offset=11' on the second node

## first node
mysql> show variables like 'auto_increment_%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 7     |
| auto_increment_offset    | 69    |
+--------------------------+-------+
2 rows in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

## second node
mysql> show variables like 'auto_increment_%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 7     |
| auto_increment_offset    | 11    |
+--------------------------+-------+
2 rows in set (0.01 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

2. begin trasaction and insert records (there have duplicated records)

## first node
mysql> insert into t1(c2) values (1),(2),(3),(4);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> insert into t1(c2) values (1),(2),(3),(4);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> insert into t1(c2) values (1),(2),(3),(4);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+----+------+
| c1 | c2   |
+----+------+
|  4 |    1 |
| 11 |    2 |
| 18 |    3 |
| 25 |    4 |
| 32 |    1 |
| 39 |    2 |
| 46 |    3 |
| 53 |    4 |
| 60 |    1 |
| 67 |    2 |
| 69 |    3 |
| 76 |    4 |
+----+------+
12 rows in set (0.00 sec)

## second node
mysql> insert into t1(c2) values (1),(2),(3),(4);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> insert into t1(c2) values (1),(2),(3),(4);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> insert into t1(c2) values (1),(2),(3),(4);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+----+------+
| c1 | c2   |
+----+------+
|  2 |    1 |
|  9 |    2 |
| 11 |    3 |
| 18 |    4 |
| 46 |    1 |
| 53 |    2 |
| 60 |    3 |
| 67 |    4 |
| 74 |    1 |
| 81 |    2 |
| 88 |    3 |
| 95 |    4 |
+----+------+
12 rows in set (0.00 sec)

3. commit transaction

## first node
mysql> commit;
Query OK, 0 rows affected (0.04 sec)

## second node
mysql> commit;
ERROR 1180 (HY000): Got error 149 - 'Lock deadlock; Retry transaction' during COMMIT

Suggested fix:
If the auto_increment_offset and auto_increment_increment fixed, all generated autoincr values have the save modulo.
[16 Dec 2020 13:51] MySQL Verification Team
Hi Mr. sephiroth,

Thank you for your bug report.

However, this is not a bug.

First of all, InnoDB storage engine has a very specific way of handling auto_increment generation when it comes to multi-row inserts and in the case of MVCC. Since  InnoDB is a MVCC SE, it must obey its rules.

There are already several feature requests to change this behaviour, but it is here to stay. Also, there are other factors involved, that are all also explained in our Reference Manual. The variables that you are writing about are both global and session variables and setting global value does not affect other, already open sessions. Etc, etc .....

Not a bug.