Description:
Bug of auto_increment_increment and auto_increment_offset values in multi-primary consisting of 9 members of InnoDB Cluster
How to repeat:
1. I prepared 9 servers (Virtual Machine) to configure InnoDB Cluster multi-primary consisting of 9 members.
- Detailed specifications of 9 servers are Ubuntu Linux 20.04 (x86, 64-bit)
------------------------------------------------------------------------
no. Host Name. Install.
1 mysql-c-1 MySQL Server, MySQL Shell
2 mysql-c-2 MySQL Server
3 mysql-c-3 MySQL Server
4 mysql-c-4 MySQL Server
5 mysql-c-5 MySQL Server
6 mysql-c-6 MySQL Server
7 mysql-c-7 MySQL Server
8 mysql-c-8 MySQL Server
9 mysql-c-9 MySQL Server
------------------------------------------------------------------------
2. I was trying to create an InnoDB Cluster multi-primary with a total of 9 members.
- All 9 servers installed MySQL Community Server 8.0.30
- Install MySQL Shell version 8.0.30 on 1 out of 9 servers
3. The MySQL installation and setup process is as follows, and the procedure was the same for all 9 servers.
- System variable server_id value is injected with different values for 9 servers (server_id of nth server = n)
- Inject server options to meet InnoDB Cluster requirements
- Create an account (account name: ic) for cluster management and grant privileges
------------------------------------------------------------------------
$> vi /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
server_id=1
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
binlog_format=ROW
binlog_checksum=NONE
log_slave_updates=1
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
transaction_write_set_extraction=XXHASH64
$> systemctl restart mysql.service
mysql> CREATE USER 'ic'@'%' IDENTIFIED BY 'ic1234';
mysql> GRANT ALL ON *.* TO 'ic'@'%' WITH GRANT OPTION;
------------------------------------------------------------------------
4. Start configuring InnoDB cluster
- Start InnoDB Cluster Configuration Using MySQL Shell
------------------------------------------------------------------------
$> mysqlsh
MySQL JS > dba.configureInstance("ic@mysql-c-1@3306")
MySQL JS > dba.configureInstance("ic@mysql-c-2@3306")
MySQL JS > dba.configureInstance("ic@mysql-c-3@3306")
MySQL JS > dba.configureInstance("ic@mysql-c-4@3306")
MySQL JS > dba.configureInstance("ic@mysql-c-5@3306")
MySQL JS > dba.configureInstance("ic@mysql-c-6@3306")
MySQL JS > dba.configureInstance("ic@mysql-c-7@3306")
MySQL JS > dba.configureInstance("ic@mysql-c-8@3306")
MySQL JS > dba.configureInstance("ic@mysql-c-9@3306")
------------------------------------------------------------------------
- After connecting to the first server (mysql-c-1), create a multi-primary cluster and add members
------------------------------------------------------------------------
MySQL JS > \\c ic@mysql-c-1:3306
MySQL mysql-c-1:3306 ssl JS > var cluster = dba.createCluster('testCluster', {multiPrimary:true})
MySQL mysql-c-1:3306 ssl JS > cluster.addInstance("ic@mysql-c-2:3306")
MySQL mysql-c-1:3306 ssl JS > cluster.addInstance("ic@mysql-c-3:3306")
MySQL mysql-c-1:3306 ssl JS > cluster.addInstance("ic@mysql-c-4:3306")
MySQL mysql-c-1:3306 ssl JS > cluster.addInstance("ic@mysql-c-5:3306")
MySQL mysql-c-1:3306 ssl JS > cluster.addInstance("ic@mysql-c-6:3306")
MySQL mysql-c-1:3306 ssl JS > cluster.addInstance("ic@mysql-c-7:3306")
MySQL mysql-c-1:3306 ssl JS > cluster.addInstance("ic@mysql-c-8:3306")
MySQL mysql-c-1:3306 ssl JS > cluster.addInstance("ic@mysql-c-9:3306")
------------------------------------------------------------------------
5. After completing InnoDB Cluster multi-primary configuration, check the setting values of 9 DB Instances
------------------------------------------------------------------------
$> mysql -uroot -p
mysql> select global variables 'server_id';
mysql> select global variables 'auto_increment_increment';
mysql> select global variables 'auto_increment_offset';
------------------------------------------------------------------------
- Result of querying each DB Instance System Variable after setting InnoDB Cluster
------------------------------------------------------------------------
no. Host Name. server_id. auto_increment_increment. auto_increment_offset
1 mysql-c-1 1 8 2
2 mysql-c-2 2 8 3
3 mysql-c-3 3 8 4
4 mysql-c-4 4 8 5
5 mysql-c-5 5 8 6
6 mysql-c-6 6 8 7
7 mysql-c-7 7 8 8
8 mysql-c-8 8 8 1
9 mysql-c-9 9 9 1
------------------------------------------------------------------------
-> Looking at the result, it is different from what was announced in the official MySQL documentation, and I think this is a bug.
Description: Bug of auto_increment_increment and auto_increment_offset values in multi-primary consisting of 9 members of InnoDB Cluster How to repeat: 1. I prepared 9 servers (Virtual Machine) to configure InnoDB Cluster multi-primary consisting of 9 members. - Detailed specifications of 9 servers are Ubuntu Linux 20.04 (x86, 64-bit) ------------------------------------------------------------------------ no. Host Name. Install. 1 mysql-c-1 MySQL Server, MySQL Shell 2 mysql-c-2 MySQL Server 3 mysql-c-3 MySQL Server 4 mysql-c-4 MySQL Server 5 mysql-c-5 MySQL Server 6 mysql-c-6 MySQL Server 7 mysql-c-7 MySQL Server 8 mysql-c-8 MySQL Server 9 mysql-c-9 MySQL Server ------------------------------------------------------------------------ 2. I was trying to create an InnoDB Cluster multi-primary with a total of 9 members. - All 9 servers installed MySQL Community Server 8.0.30 - Install MySQL Shell version 8.0.30 on 1 out of 9 servers 3. The MySQL installation and setup process is as follows, and the procedure was the same for all 9 servers. - System variable server_id value is injected with different values for 9 servers (server_id of nth server = n) - Inject server options to meet InnoDB Cluster requirements - Create an account (account name: ic) for cluster management and grant privileges ------------------------------------------------------------------------ $> vi /etc/mysql/mysql.conf.d/mysqld.cnf [mysqld] server_id=1 disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY" binlog_format=ROW binlog_checksum=NONE log_slave_updates=1 gtid_mode=ON enforce_gtid_consistency=ON master_info_repository=TABLE relay_log_info_repository=TABLE transaction_write_set_extraction=XXHASH64 $> systemctl restart mysql.service mysql> CREATE USER 'ic'@'%' IDENTIFIED BY 'ic1234'; mysql> GRANT ALL ON *.* TO 'ic'@'%' WITH GRANT OPTION; ------------------------------------------------------------------------ 4. Start configuring InnoDB cluster - Start InnoDB Cluster Configuration Using MySQL Shell ------------------------------------------------------------------------ $> mysqlsh MySQL JS > dba.configureInstance("ic@mysql-c-1@3306") MySQL JS > dba.configureInstance("ic@mysql-c-2@3306") MySQL JS > dba.configureInstance("ic@mysql-c-3@3306") MySQL JS > dba.configureInstance("ic@mysql-c-4@3306") MySQL JS > dba.configureInstance("ic@mysql-c-5@3306") MySQL JS > dba.configureInstance("ic@mysql-c-6@3306") MySQL JS > dba.configureInstance("ic@mysql-c-7@3306") MySQL JS > dba.configureInstance("ic@mysql-c-8@3306") MySQL JS > dba.configureInstance("ic@mysql-c-9@3306") ------------------------------------------------------------------------ - After connecting to the first server (mysql-c-1), create a multi-primary cluster and add members ------------------------------------------------------------------------ MySQL JS > \\c ic@mysql-c-1:3306 MySQL mysql-c-1:3306 ssl JS > var cluster = dba.createCluster('testCluster', {multiPrimary:true}) MySQL mysql-c-1:3306 ssl JS > cluster.addInstance("ic@mysql-c-2:3306") MySQL mysql-c-1:3306 ssl JS > cluster.addInstance("ic@mysql-c-3:3306") MySQL mysql-c-1:3306 ssl JS > cluster.addInstance("ic@mysql-c-4:3306") MySQL mysql-c-1:3306 ssl JS > cluster.addInstance("ic@mysql-c-5:3306") MySQL mysql-c-1:3306 ssl JS > cluster.addInstance("ic@mysql-c-6:3306") MySQL mysql-c-1:3306 ssl JS > cluster.addInstance("ic@mysql-c-7:3306") MySQL mysql-c-1:3306 ssl JS > cluster.addInstance("ic@mysql-c-8:3306") MySQL mysql-c-1:3306 ssl JS > cluster.addInstance("ic@mysql-c-9:3306") ------------------------------------------------------------------------ 5. After completing InnoDB Cluster multi-primary configuration, check the setting values of 9 DB Instances ------------------------------------------------------------------------ $> mysql -uroot -p mysql> select global variables 'server_id'; mysql> select global variables 'auto_increment_increment'; mysql> select global variables 'auto_increment_offset'; ------------------------------------------------------------------------ - Result of querying each DB Instance System Variable after setting InnoDB Cluster ------------------------------------------------------------------------ no. Host Name. server_id. auto_increment_increment. auto_increment_offset 1 mysql-c-1 1 8 2 2 mysql-c-2 2 8 3 3 mysql-c-3 3 8 4 4 mysql-c-4 4 8 5 5 mysql-c-5 5 8 6 6 mysql-c-6 6 8 7 7 mysql-c-7 7 8 8 8 mysql-c-8 8 8 1 9 mysql-c-9 9 9 1 ------------------------------------------------------------------------ -> Looking at the result, it is different from what was announced in the official MySQL documentation, and I think this is a bug.