MySQL Bugs: #108759: InnoDB multi-primary auto_increment_increment, auto_increment

Bug #108759	InnoDB multi-primary auto_increment_increment, auto_increment_offset bug
Submitted:	13 Oct 2022 7:08	Modified:	1 Feb 2023 11:22
Reporter:	MINJUNG SUN	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Shell AdminAPI InnoDB Cluster / ReplicaSet	Severity:	S3 (Non-critical)
Version:	8.0.29, 8.0.30	OS:	Ubuntu (20.04)
Assigned to:		CPU Architecture:	x86
Tags:	auto_increment_increment, auto_increment_offset, multi-primary

Description:
n InnoDB multi-primary mode, when members are composed of 8 or 9, the auto_increment_increment and auto_increment_offset values are different from those announced in the Mysql official documentation. I believe it's a bug and I've attached detailed test results below.

[ MySQL official documentation ]
: If the group is running in multi-primary mode, then when the cluster has 7 instances or less set auto_increment_increment to 7 and auto_increment_offset to 1 + server_id % 7. If a multi-primary cluster has 8 or more instances set auto_increment_increment to the number of instances and auto_increment_offset to 1 + server_id % the number of instances.

How to repeat:
I checked the result below in version 8.0.29 (mysql server 8.0.29, mysql shell 8.0.29).

Case 1. When the number of members is between 3 and 7.
It was confirmed that auto_increment_increment=7, auto_increment_offset=1+(server_id)%7 for multi-primary with the number of members between 3 and 7.
The result is the same as announced in the official MySQL documentation.

Case 2. This is the test result when the number of multi-primary members is 8.

server_id | auto_increment_increment | auto_increment_offset
-------------------------------------------------------------
1 	  | 7 			     | 2
2	  | 7 			     | 3
3 	  | 7 			     | 4
4 	  | 7 			     | 5
5   	  | 7 		             | 6
6 	  | 7 			     | 7
7 	  | 7 			     | 8
8 	  | 8 			     | 1

This result is different from that announced in the official MySQL documentation.

Case 3. This is the test result when the number of multi-primary members is 9.

server_id | auto_increment_increment | auto_increment_offset
-------------------------------------------------------------
1 	  | 8 			     | 2
2	  | 8 			     | 3
3 	  | 8 			     | 4
4 	  | 8 			     | 5
5   	  | 8 		             | 6
6 	  | 8 			     | 7
7 	  | 8 			     | 8
8 	  | 8 			     | 1
8 	  | 9 			     | 1

This result is different from that announced in the official MySQL documentation.

Case 2 and Case 3 are considered bugs.

Additionally, in the section AdminAPI Bugs Fixed in Changes in Mysql Shell 8.0.16, we confirmed that errors in multi-primary auto_increment_increment, auto_increment_offset values have been fixed.
(https://dev.mysql.com/doc/relnotes/mysql-shell/8.0/en/news-8-0-16.html)

edit synopsis

edit Category

Hi Mr. Sun,

Thank you for your bug report.

We need additional info from you. We need exactly the steps for fully reproducing this behaviour. That means entire set of SQL statements.

We also require full description of the architecture of your Innodb Cluster.

Waiting on your feedback.

Hi,

> when members are composed of 8 or 9

what do you mean by this? Composed of 8 or 9 what? Note that you should always setup a Group Replication / InnoDB Cluster with odd (2n+1) number of nodes. Not relevant to this bug but you should always strive to have odd number of nodes.

> I've attached detailed test results below

Would be better if you attached the whole test too :). You should write exactly what config you used, what number of nodes you have and how you created the cluster (step by step).

> This result is different from that announced in the official MySQL documentation.

auto_increment_offset looks ok but auto_increment_increment seems not ok. How did you end up with this? Did you create a cluster with 7 nodes and then added a new node (and then another one for 9) or you created cluster with 8 (9) nodes from scratch?

Thanks

We recorded a full test of the Innodb cluster configuration and reported the bug again.

Id : 108794

https://bugs.mysql.com/bug.php?id=108794

Hi,

Thanks for clarification. I verified the bug. 

kind regards

Hello,

When are you going to fix this bug?
What minor version are you planning to patch to?

This bug is fatal when creating a total of 9 instances when using innodb cluster multi-primary. Please tell me the version to be patched.

Hello,

When are you going to fix this bug?
What minor version are you planning to patch to?

This bug is fatal when creating a total of 9 instances when using innodb cluster multi-primary. Please tell me the version to be patched.

Hi,

Decision on bug fixing scheduling is strictly internal and not known even to the Bug Verification Team.

Posted by developer:
 
As a clarification, this issue is not "fatal". Worst case, transactions with identical auto_inc column values executed at the exact same time will conflict and one of them will be rolled back, but consistency is never compromised.

Thank you, Alfredo ......

Posted by developer:
 
Added the following note to the MySQL Shell 8.0.33 release notes:
	
When adding a member to a cluster, the values of auto_increment_increment and auto_increment_offset were
miscalculated and were off by 1. As a result, when adding or rejoining an eighth member to a cluster, the new
member was configured with the correct values, while the other seven members continued with the configuration of
a seven-member cluster.