Bug #96213 group replication auto_increment_offset is not accurate
Submitted: 16 Jul 2019 6:01 Modified: 5 Sep 2019 7:42
Reporter: hiller hiller Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Group Replication Severity:S3 (Non-critical)
Version: OS:Any
Assigned to: CPU Architecture:Any
Tags: auto_increment_offset

[16 Jul 2019 6:01] hiller hiller
Description:
The auto_increment_offset parameter value is 413306,but the system auto assignment is 3

How to repeat:
mysql> show variables like '%auto_incre%';
+--------------------------------------------+--------+
| Variable_name                              | Value  |
+--------------------------------------------+--------+
| auto_increment_increment                   | 7      |
| auto_increment_offset                      | 413306 |
| group_replication_auto_increment_increment | 7      |
+--------------------------------------------+--------+
3 rows in set (0.01 sec)

mysql> insert into t1 values();
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+----+
| id |
+----+
|  3 |
+----+
1 row in set (0.00 sec)

mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.26-log |
+------------+
1 row in set (0.00 sec)
[16 Jul 2019 6:07] hiller hiller
mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
[5 Aug 2019 7:42] MySQL Verification Team
Hello hiller,

Thank you for the report.
Imho this is expected behavior i.e quoting from our manual "When Group Replication is started on a server, the value of the server system variable auto_increment_increment is changed to this value, and the value of the server system variable auto_increment_offset is changed to the server ID. These settings avoid the selection of duplicate auto-increment values for writes on group members, which causes rollback of transactions" - https://dev.mysql.com/doc/refman/5.7/en/group-replication-options.html#sysvar_group_replic...

https://dev.mysql.com/doc/refman/5.7/en/replication-options-master.html#sysvar_auto_increm...

Also, could you please confirm server-id value on the problem'ed server, whether it is a single master or multi master GR setup? Thank you.

regards,
Umesh
[6 Sep 2019 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".