Description:
When configuring an InnoDB Cluster, configuring a multi-primary InnoDB Cluster fails when set to transaction_isolation= SERIALIZABLE.
How to repeat:
1. I prepared 3 servers (Virtual Machine) to configure InnoDB Cluster multi-primary consisting of 3 members.
2. I was trying to create an InnoDB Cluster multi-primary with a total of 3 members.
- All 3 servers installed MySQL Community Server 8.0.31
3. The MySQL installation and setup process is as follows, and the procedure was the same for all 3 servers.
- System variable server_id value is injected with different values for 3 servers (server_id of nth server = n)
- Inject server options to meet InnoDB Cluster requirements
- Create an account (account name: ic_router) for cluster management and grant privileges
------------------------------------------------------------------------
$> vi /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
server_id=1
transaction_isolation=SERIALIZABLE
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_router'@'%' IDENTIFIED BY 'router1234';
mysql> GRANT ALL ON *.* TO 'ic_router'@'%' WITH GRANT OPTION;
------------------------------------------------------------------------
4. Start configuring InnoDB cluster
- Start InnoDB Cluster Configuration Using MySQL Shell
- After connecting to the one server(smj-1-sql), create a multi-primary cluster and add members
------------------------------------------------------------------------
MySQL 172.25.230.16:3306 ssl JS > var cluster = dba.createCluster('testCluster', {multiPrimary:true})
A new InnoDB Cluster will be created on instance 'smj-1-sql:3306'.
The MySQL InnoDB Cluster is going to be setup in advanced Multi-Primary Mode.
Before continuing you have to confirm that you understand the requirements and
limitations of Multi-Primary Mode. For more information see
https://dev.mysql.com/doc/refman/en/group-replication-limitations.html before
proceeding.
I have read the MySQL InnoDB Cluster manual and I understand the requirements
and limitations of advanced Multi-Primary Mode.
Confirm [y/N]: y
Validating instance configuration at 172.25.230.16:3306...
This instance reports its own address as smj-1-sql:3306
Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 'smj-1-sql:3306'. Use the localAddress option to override.
Creating InnoDB Cluster 'testCluster' on 'smj-1-sql:3306'...
The MySQL InnoDB Cluster is going to be setup in advanced Multi-Primary Mode. Consult its requirements and limitations in https://dev.mysql.com/doc/refman/en/group-replication-limitations.html
Adding Seed Instance...
Dba.createCluster: Failed to execute query on Metadata server smj-1-sql:3306: The table does not comply with the requirements by an external plugin. (MySQL Error 3098)
------------------------------------------------------------------------
-> Multi-primary configuration is not possible with such an error message.
5. BUT!!! If you try to configure it as single-primary instead of multi-primary with the same settings, it is set normally and a single-primary inodb cluster is created.
------------------------------------------------------------------------
MySQL 172.25.230.16:3306 ssl JS > var cluster = dba.createCluster('testCluster')
\A new InnoDB Cluster will be created on instance 'smj-1-sql:3306'.
Validating instance configuration at 172.25.230.16:3306...
This instance reports its own address as smj-1-sql:3306
Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 'smj-1-sql:3306'. Use the localAddress option to override.
Creating InnoDB Cluster 'testCluster' on 'smj-1-sql:3306'...
Adding Seed Instance...
Cluster successfully created. Use Cluster.addInstance() to add MySQL instances.
At least 3 instances are needed for the cluster to be able to withstand up to
one server failure.
------------------------------------------------------------------------
-> After setting to transaction_isolation= SERIALIZABLE, it is normally created when it is created as a single-primary InnoDB Cluster.
6. AND!!!! According to procedure 5, a successful InnoDB cluster configured as single-primary fails if it is converted to multi-primary.
------------------------------------------------------------------------
MySQL 172.25.230.16:3306 ssl JS > cluster.switchToMultiPrimaryMode()
Switching cluster 'testCluster' to Multi-Primary mode...
Cluster.switchToMultiPrimaryMode: Failed to execute query on Metadata server smj-1-sql:3306: The table does not comply with the requirements by an external plugin. (MySQL Error 3098)
------------------------------------------------------------------------
That is, when set to transaction_isolation= SERIALIZABLE, the creation of a multi-primary InnoDB cluster failed.
Description: When configuring an InnoDB Cluster, configuring a multi-primary InnoDB Cluster fails when set to transaction_isolation= SERIALIZABLE. How to repeat: 1. I prepared 3 servers (Virtual Machine) to configure InnoDB Cluster multi-primary consisting of 3 members. 2. I was trying to create an InnoDB Cluster multi-primary with a total of 3 members. - All 3 servers installed MySQL Community Server 8.0.31 3. The MySQL installation and setup process is as follows, and the procedure was the same for all 3 servers. - System variable server_id value is injected with different values for 3 servers (server_id of nth server = n) - Inject server options to meet InnoDB Cluster requirements - Create an account (account name: ic_router) for cluster management and grant privileges ------------------------------------------------------------------------ $> vi /etc/mysql/mysql.conf.d/mysqld.cnf [mysqld] server_id=1 transaction_isolation=SERIALIZABLE 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_router'@'%' IDENTIFIED BY 'router1234'; mysql> GRANT ALL ON *.* TO 'ic_router'@'%' WITH GRANT OPTION; ------------------------------------------------------------------------ 4. Start configuring InnoDB cluster - Start InnoDB Cluster Configuration Using MySQL Shell - After connecting to the one server(smj-1-sql), create a multi-primary cluster and add members ------------------------------------------------------------------------ MySQL 172.25.230.16:3306 ssl JS > var cluster = dba.createCluster('testCluster', {multiPrimary:true}) A new InnoDB Cluster will be created on instance 'smj-1-sql:3306'. The MySQL InnoDB Cluster is going to be setup in advanced Multi-Primary Mode. Before continuing you have to confirm that you understand the requirements and limitations of Multi-Primary Mode. For more information see https://dev.mysql.com/doc/refman/en/group-replication-limitations.html before proceeding. I have read the MySQL InnoDB Cluster manual and I understand the requirements and limitations of advanced Multi-Primary Mode. Confirm [y/N]: y Validating instance configuration at 172.25.230.16:3306... This instance reports its own address as smj-1-sql:3306 Instance configuration is suitable. NOTE: Group Replication will communicate with other members using 'smj-1-sql:3306'. Use the localAddress option to override. Creating InnoDB Cluster 'testCluster' on 'smj-1-sql:3306'... The MySQL InnoDB Cluster is going to be setup in advanced Multi-Primary Mode. Consult its requirements and limitations in https://dev.mysql.com/doc/refman/en/group-replication-limitations.html Adding Seed Instance... Dba.createCluster: Failed to execute query on Metadata server smj-1-sql:3306: The table does not comply with the requirements by an external plugin. (MySQL Error 3098) ------------------------------------------------------------------------ -> Multi-primary configuration is not possible with such an error message. 5. BUT!!! If you try to configure it as single-primary instead of multi-primary with the same settings, it is set normally and a single-primary inodb cluster is created. ------------------------------------------------------------------------ MySQL 172.25.230.16:3306 ssl JS > var cluster = dba.createCluster('testCluster') \A new InnoDB Cluster will be created on instance 'smj-1-sql:3306'. Validating instance configuration at 172.25.230.16:3306... This instance reports its own address as smj-1-sql:3306 Instance configuration is suitable. NOTE: Group Replication will communicate with other members using 'smj-1-sql:3306'. Use the localAddress option to override. Creating InnoDB Cluster 'testCluster' on 'smj-1-sql:3306'... Adding Seed Instance... Cluster successfully created. Use Cluster.addInstance() to add MySQL instances. At least 3 instances are needed for the cluster to be able to withstand up to one server failure. ------------------------------------------------------------------------ -> After setting to transaction_isolation= SERIALIZABLE, it is normally created when it is created as a single-primary InnoDB Cluster. 6. AND!!!! According to procedure 5, a successful InnoDB cluster configured as single-primary fails if it is converted to multi-primary. ------------------------------------------------------------------------ MySQL 172.25.230.16:3306 ssl JS > cluster.switchToMultiPrimaryMode() Switching cluster 'testCluster' to Multi-Primary mode... Cluster.switchToMultiPrimaryMode: Failed to execute query on Metadata server smj-1-sql:3306: The table does not comply with the requirements by an external plugin. (MySQL Error 3098) ------------------------------------------------------------------------ That is, when set to transaction_isolation= SERIALIZABLE, the creation of a multi-primary InnoDB cluster failed.