Description:
Hello,
With GIPK enabled and `show_gipk_in_create_table_and_information_schema` set to `OFF`, the MySQL Group Replication plugin does not see the invisible PKs.
As the doc states:
> Group Replication has its own built-in set of checks for primary keys or primary key equivalents, and does not use the checks carried out by the sql_require_primary_key system variable
How to repeat:
// check settings
information_schema> SELECT @@show_gipk_in_create_table_and_information_schema;
+----------------------------------------------------+
| @@show_gipk_in_create_table_and_information_schema |
+----------------------------------------------------+
| 0 |
+----------------------------------------------------+
1 row in set (0.00 sec)
information_schema> SELECT @@sql_generate_invisible_primary_key;
+--------------------------------------+
| @@sql_generate_invisible_primary_key |
+--------------------------------------+
| 1 |
+--------------------------------------+
1 row in set (0.00 sec)
information_schema> select @@sql_require_primary_key;
+---------------------------+
| @@sql_require_primary_key |
+---------------------------+
| 1 |
+---------------------------+
1 row in set (0.00 sec)
information_schema> create database test;
Query OK, 1 row affected (0.00 sec)
information_schema> use test;
Database changed
test> CREATE TABLE auto_1 (c1 VARCHAR(50), c2 INT);
Query OK, 0 rows affected (0.01 sec)
// table is successfully created
test> show create table auto_1;
+--------+-------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+-------------------------------------------------------------------------------------------------------------------------+
| auto_1 | CREATE TABLE `auto_1` (
`c1` varchar(50) DEFAULT NULL,
`c2` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------+-------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
test> SET show_gipk_in_create_table_and_information_schema = 1;
Query OK, 0 rows affected (0.00 sec)
// verify that GIPK does exist
test> show create table auto_1;
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| auto_1 | CREATE TABLE `auto_1` (
`my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
`c1` varchar(50) DEFAULT NULL,
`c2` int DEFAULT NULL,
PRIMARY KEY (`my_row_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
// try to bootstrap cluster with mysqlsh
~ # mysqlsh --user icadmin
MySQL Shell 8.0.33
Copyright (c) 2016, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.
[...]
Server version: 8.0.33-25 Percona Server (GPL), Release '25', Revision '9468fd1d'
No default schema selected; type \use <schema> to set one.
MySQL localhost JS > var c = dba.createCluster('xxxxx', {localAddress: 'xxxxxxx', memberSslMode: 'REQUIRED', exitStateAction: 'OFFLINE_MODE', consistency: 'BEFORE_ON_PRIMARY_FAILOVER', autoRejoinTries: 10, expelTimeout: 30, memberWeight: 50, communicationStack: 'XCom'})
A new InnoDB Cluster will be created on instance 'xxxxx:3306'.
[...]
ERROR: The following tables do not have a Primary Key or equivalent column:
test.auto_1
Group Replication requires tables to use InnoDB and have a PRIMARY KEY or PRIMARY KEY Equivalent (non-null unique key). Tables that do not follow these requirements will be readable but not updateable when used with Group Replication. If your applications make updates (INSERT, UPDATE or DELETE) to these tables, ensure they use the InnoDB storage engine and have a PRIMARY KEY or PRIMARY KEY Equivalent.
If you can't change the tables structure to include an extra visible key to be used as PRIMARY KEY, you can make use of the INVISIBLE COLUMN feature available since 8.0.23: https://dev.mysql.com/doc/refman/8.0/en/invisible-columns.html
ERROR: Instance must be configured and validated with dba.checkInstanceConfiguration() and dba.configureInstance() before it can be used in an InnoDB cluster.
Dba.createCluster: Instance check failed (RuntimeError)
// same with checkInstanceConfiguration()
MySQL localhost JS > dba.checkInstanceConfiguration()
Validating local MySQL instance listening at port 3306 for use in an InnoDB cluster...
This instance reports its own address as xxxxxxxxx:3306
Checking whether existing tables comply with Group Replication requirements...
ERROR: The following tables do not have a Primary Key or equivalent column:
test.auto_1
Group Replication requires tables to use InnoDB and have a PRIMARY KEY or PRIMARY KEY Equivalent (non-null unique key). Tables that do not follow these requirements will be readable but not updateable when used with Group Replication. If your applications make updates (INSERT, UPDATE or DELETE) to these tables, ensure they use the InnoDB storage engine and have a PRIMARY KEY or PRIMARY KEY Equivalent.
If you can't change the tables structure to include an extra visible key to be used as PRIMARY KEY, you can make use of the INVISIBLE COLUMN feature available since 8.0.23: https://dev.mysql.com/doc/refman/8.0/en/invisible-columns.html
Checking instance configuration...
{
"status": "error"
}
// exit mysqlsh and set show_gipk_in_create_table_and_information_schema to ON
information_schema> SET GLOBAL show_gipk_in_create_table_and_information_schema = 1;
Query OK, 0 rows affected (0.00 sec)
// try to bootstrap cluster again
~ # mysqlsh --user icadmin
MySQL Shell 8.0.33
Copyright (c) 2016, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.
[...]
Server version: 8.0.33-25 Percona Server (GPL), Release '25', Revision '9468fd1d'
No default schema selected; type \use <schema> to set one.
MySQL localhost JS > var c = dba.createCluster('xxxxx', {localAddress: 'xxxxxxx', memberSslMode: 'REQUIRED', exitStateAction: 'OFFLINE_MODE', consistency: 'BEFORE_ON_PRIMARY_FAILOVER', autoRejoinTries: 10, expelTimeout: 30, memberWeight: 50, communicationStack: 'XCom'})
A new InnoDB Cluster will be created on instance 'xxxxxx:3306'.
[...]
Instance configuration is suitable.
* Checking connectivity and SSL configuration...
Creating InnoDB Cluster 'xxxxx' on 'xxxxxx: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.
Suggested fix:
I think we should either have a way to make GIPK visible in information_schema for the GR to see it or change the GR checks to verify if the table has a GIPK.
Description: Hello, With GIPK enabled and `show_gipk_in_create_table_and_information_schema` set to `OFF`, the MySQL Group Replication plugin does not see the invisible PKs. As the doc states: > Group Replication has its own built-in set of checks for primary keys or primary key equivalents, and does not use the checks carried out by the sql_require_primary_key system variable How to repeat: // check settings information_schema> SELECT @@show_gipk_in_create_table_and_information_schema; +----------------------------------------------------+ | @@show_gipk_in_create_table_and_information_schema | +----------------------------------------------------+ | 0 | +----------------------------------------------------+ 1 row in set (0.00 sec) information_schema> SELECT @@sql_generate_invisible_primary_key; +--------------------------------------+ | @@sql_generate_invisible_primary_key | +--------------------------------------+ | 1 | +--------------------------------------+ 1 row in set (0.00 sec) information_schema> select @@sql_require_primary_key; +---------------------------+ | @@sql_require_primary_key | +---------------------------+ | 1 | +---------------------------+ 1 row in set (0.00 sec) information_schema> create database test; Query OK, 1 row affected (0.00 sec) information_schema> use test; Database changed test> CREATE TABLE auto_1 (c1 VARCHAR(50), c2 INT); Query OK, 0 rows affected (0.01 sec) // table is successfully created test> show create table auto_1; +--------+-------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +--------+-------------------------------------------------------------------------------------------------------------------------+ | auto_1 | CREATE TABLE `auto_1` ( `c1` varchar(50) DEFAULT NULL, `c2` int DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +--------+-------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) test> SET show_gipk_in_create_table_and_information_schema = 1; Query OK, 0 rows affected (0.00 sec) // verify that GIPK does exist test> show create table auto_1; +--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | auto_1 | CREATE TABLE `auto_1` ( `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */, `c1` varchar(50) DEFAULT NULL, `c2` int DEFAULT NULL, PRIMARY KEY (`my_row_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) // try to bootstrap cluster with mysqlsh ~ # mysqlsh --user icadmin MySQL Shell 8.0.33 Copyright (c) 2016, 2023, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. [...] Server version: 8.0.33-25 Percona Server (GPL), Release '25', Revision '9468fd1d' No default schema selected; type \use <schema> to set one. MySQL localhost JS > var c = dba.createCluster('xxxxx', {localAddress: 'xxxxxxx', memberSslMode: 'REQUIRED', exitStateAction: 'OFFLINE_MODE', consistency: 'BEFORE_ON_PRIMARY_FAILOVER', autoRejoinTries: 10, expelTimeout: 30, memberWeight: 50, communicationStack: 'XCom'}) A new InnoDB Cluster will be created on instance 'xxxxx:3306'. [...] ERROR: The following tables do not have a Primary Key or equivalent column: test.auto_1 Group Replication requires tables to use InnoDB and have a PRIMARY KEY or PRIMARY KEY Equivalent (non-null unique key). Tables that do not follow these requirements will be readable but not updateable when used with Group Replication. If your applications make updates (INSERT, UPDATE or DELETE) to these tables, ensure they use the InnoDB storage engine and have a PRIMARY KEY or PRIMARY KEY Equivalent. If you can't change the tables structure to include an extra visible key to be used as PRIMARY KEY, you can make use of the INVISIBLE COLUMN feature available since 8.0.23: https://dev.mysql.com/doc/refman/8.0/en/invisible-columns.html ERROR: Instance must be configured and validated with dba.checkInstanceConfiguration() and dba.configureInstance() before it can be used in an InnoDB cluster. Dba.createCluster: Instance check failed (RuntimeError) // same with checkInstanceConfiguration() MySQL localhost JS > dba.checkInstanceConfiguration() Validating local MySQL instance listening at port 3306 for use in an InnoDB cluster... This instance reports its own address as xxxxxxxxx:3306 Checking whether existing tables comply with Group Replication requirements... ERROR: The following tables do not have a Primary Key or equivalent column: test.auto_1 Group Replication requires tables to use InnoDB and have a PRIMARY KEY or PRIMARY KEY Equivalent (non-null unique key). Tables that do not follow these requirements will be readable but not updateable when used with Group Replication. If your applications make updates (INSERT, UPDATE or DELETE) to these tables, ensure they use the InnoDB storage engine and have a PRIMARY KEY or PRIMARY KEY Equivalent. If you can't change the tables structure to include an extra visible key to be used as PRIMARY KEY, you can make use of the INVISIBLE COLUMN feature available since 8.0.23: https://dev.mysql.com/doc/refman/8.0/en/invisible-columns.html Checking instance configuration... { "status": "error" } // exit mysqlsh and set show_gipk_in_create_table_and_information_schema to ON information_schema> SET GLOBAL show_gipk_in_create_table_and_information_schema = 1; Query OK, 0 rows affected (0.00 sec) // try to bootstrap cluster again ~ # mysqlsh --user icadmin MySQL Shell 8.0.33 Copyright (c) 2016, 2023, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. [...] Server version: 8.0.33-25 Percona Server (GPL), Release '25', Revision '9468fd1d' No default schema selected; type \use <schema> to set one. MySQL localhost JS > var c = dba.createCluster('xxxxx', {localAddress: 'xxxxxxx', memberSslMode: 'REQUIRED', exitStateAction: 'OFFLINE_MODE', consistency: 'BEFORE_ON_PRIMARY_FAILOVER', autoRejoinTries: 10, expelTimeout: 30, memberWeight: 50, communicationStack: 'XCom'}) A new InnoDB Cluster will be created on instance 'xxxxxx:3306'. [...] Instance configuration is suitable. * Checking connectivity and SSL configuration... Creating InnoDB Cluster 'xxxxx' on 'xxxxxx: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. Suggested fix: I think we should either have a way to make GIPK visible in information_schema for the GR to see it or change the GR checks to verify if the table has a GIPK.