MySQL Bugs: #89686: Need to add "offset is provided by server

Bug #89686	Need to add "offset is provided by server_id and increment is 7" to GR manual.
Submitted:	16 Feb 2018 7:47	Modified:	30 Apr 2019 11:04
Reporter:	Meiji Kimura	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.7, 8.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	group replication

Description:
"offset is provided by server_id and increment is 7" is explained in many documentation for auto_increment column of Group Replication.

https://www.slideshare.net/nunoacarvalho/mysql-high-availability-with-group-replication/32
https://dev.mysql.com/worklog/task/?id=8445
https://mysqlhighavailability.com/mysql-group-replication-auto-increment-configuration-han...

But I cannot find such a description on official manual.

How to repeat:
Do GR(Group Replication) and make a table with auto_increment.

Suggested fix:
Add description to proper manual chapter related "offset is provided by server_id and increment is 7""

For example,

auto_increment_offset is overwritten by server_id when using GR.

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

auto_increment_increment is overwritten by 7 when using GR.

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

and need explain in limit, FAQ
https://dev.mysql.com/doc/refman/5.7/en/group-replication-limitations.html
https://dev.mysql.com/doc/refman/5.7/en/group-replication-frequently-asked-questions.html
https://dev.mysql.com/doc/refman/8.0/en/group-replication-limitations.html
https://dev.mysql.com/doc/refman/8.0/en/group-replication-frequently-asked-questions.html

Hello Meiji-San,

Thank you for the report!

Thanks,
Umesh

Posted by developer:
 
Hi - Thanks for pointing this out. I have updated the descriptions for auto_increment_increment, auto_increment_offset, and group_replication_auto_increment_increment, and added to the FAQs as follows:

Why did the auto increment setting on the server change?

When Group Replication is started on a server, the value of auto_increment_increment is changed to the value of group_replication_auto_increment_increment, which defaults to 7, and the value of auto_increment_offset is changed to the server ID. The changes are reverted when Group Replication is stopped. These settings avoid the selection of duplicate auto-increment values for writes on group members, which causes rollback of transactions. The default auto increment value of 7 for Group Replication represents a balance between the number of usable values and the permitted maximum size of a replication group (9 members). 

The changes are only made and reverted if auto_increment_increment and auto_increment_offset each have their default value of 1. If their values have already been modified from the default, Group Replication does not alter them. From MySQL 8.0, the system variables are also not modified when Group Replication is in single-primary mode, where only one server writes. 

I don't think this is a limitation as such so I didn't add it there. Thanks for helping us to improve the documentation!