Sql_generate_invisible_primary_key on XtraDB Cluster

stofa.kenida · March 16, 2026, 3:16pm

Hi There,

We have a cluster with 3 nodes ( 2 nodes and a gardb ).

The used version is :

8.0.44-35.1 Percona XtraDB Cluster (GPL), Release rel35, Revision 7284a5c, WSREP version 26.1.4.3

We observed that, when the parameter sql_generate_invisible_primary_key is changed dinamycally on both nodes by:

set global sql_generate_invisible_primary_key=OFF;

A table creation on one node adds the column my_row_id on the other node ( where the create table is not executed but only replicated )

How to reproduce:

On both nodes : the parameter sql_generate_invisible_primary_key is ON
Set the parametr to OFF on both nodes.
Check the new value is OFF on both nodes at session level
Create a table in one node: create table prova.prova ( id int NOT NULL, testo varchar(30));
On the node where the table is created:
CREATE TABLE prova (
id int NOT NULL,
testo varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
On the other node:

CREATE TABLE prova (
my_row_id bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
id int NOT NULL,
testo varchar(30) DEFAULT NULL,
PRIMARY KEY (my_row_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

As you can see, the table’s structure is different.

The same behaviour with 8.4 version.

How can we fix this issue ?

Thanks.

jrivera · March 17, 2026, 12:29am

I couldn’t reproduce this change either in the GLOBAL or the SESSION scope for the variable sql_generate_invisible_primary_key. I’ve tested this on PXC 8.0.44.

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create table t2 (id int not null, c varchar(100));
Query OK, 0 rows affected (0.07 sec)

mysql> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int NOT NULL,
  `c` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> select @@session.sql_generate_invisible_primary_key;
+----------------------------------------------+
| @@session.sql_generate_invisible_primary_key |
+----------------------------------------------+
|                                            0 |
+----------------------------------------------+
1 row in set (0.00 sec)

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int NOT NULL,
  `c` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> select @@global.sql_generate_invisible_primary_key;
+---------------------------------------------+
| @@global.sql_generate_invisible_primary_key |
+---------------------------------------------+
|                                           1 |
+---------------------------------------------+
1 row in set (0.00 sec)

Upstream’s definition of the variable also states:

This variable is not replicated. In addition, even if set on the replica, it is ignored by replication applier threads; this means that, by default, a replica does not generate a primary key for any replicated table which, on the source, was created without one.

I wonder if there’s anything missing in the test case. for us to reproduce it.

stofa.kenida · March 17, 2026, 9:29am

Hi Rivera,

To reproduce, you should use at least 2 nodes in Xtradb cluster.

STEP 1

Initially on both nodes the parameter is ON:

NODE 1:

@@global.sql_generate_invisible_primary_key
1

NODE2:

@@global.sql_generate_invisible_primary_key
1

STEP 2

SET the parameter to OFF on both nodes:

set global sql_generate_invisible_primary_key=OFF;

STEP 3:

reconnect to mysql on both nodes. Check if the parameter is OFF.

NODE1 and NODE2:

sql_generate_invisible_primary_key is OFF ( global and session level )

STEP 4:

NODE1:

mysql> create database prova;
Query OK, 1 row affected (0.02 sec)

mysql> use prova
Database changed
mysql> create table prova (id int NOT NULL, testo varchar(50));
Query OK, 0 rows affected (0.02 sec)

mysql> show create table prova.prova\G

Table: prova
Create Table: CREATE TABLE prova (
id int NOT NULL,
testo varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

NODE2:

mysql> show create table prova.prova\G

Table: prova
Create Table: CREATE TABLE prova (
my_row_id bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
id int NOT NULL,
testo varchar(50) DEFAULT NULL,
PRIMARY KEY (my_row_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

On both nodes sql_generate_invisible_primary_key is OFF, then on NODE1, where the table is created, the my_row_id is missing ( as must be ) but on NODE2 the my_row:_id is present even if sql_generate_invisible_primary_key is OFF.

Thnaks

jrivera · March 18, 2026, 1:47am

I tried to follow your procedure, but could not reproduce it in 8.0.44:

jerichorivera@PerconaSupport pxc % docker compose exec -it pxc1 bash -c 'mysql -uroot -pt00r -e "set @@global.sql_generate_invisible_primary_key=OFF; select @@global.sql_generate_invisible_primary_key; select @@session.sql_generate_invisible_primary_key;"'
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------------------------------------+
| @@global.sql_generate_invisible_primary_key |
+---------------------------------------------+
|                                           0 |
+---------------------------------------------+
+----------------------------------------------+
| @@session.sql_generate_invisible_primary_key |
+----------------------------------------------+
|                                            0 |
+----------------------------------------------+
jerichorivera@PerconaSupport pxc % docker compose exec -it pxc2 bash -c 'mysql -uroot -pt00r -e "set @@global.sql_generate_invisible_primary_key=OFF; select @@global.sql_generate_invisible_primary_key; select @@session.sql_generate_invisible_primary_key;"'
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------------------------------------+
| @@global.sql_generate_invisible_primary_key |
+---------------------------------------------+
|                                           0 |
+---------------------------------------------+
+----------------------------------------------+
| @@session.sql_generate_invisible_primary_key |
+----------------------------------------------+
|                                            0 |
+----------------------------------------------+
jerichorivera@PerconaSupport pxc % docker compose exec -it pxc3 bash -c 'mysql -uroot -pt00r -e "select @@global.sql_generate_invisible_primary_key; select @@session.sql_generate_invisible_primary_key;"'
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------------------------------------+
| @@global.sql_generate_invisible_primary_key |
+---------------------------------------------+
|                                           1 |
+---------------------------------------------+
+----------------------------------------------+
| @@session.sql_generate_invisible_primary_key |
+----------------------------------------------+
|                                            1 |
+----------------------------------------------+

Created the table in PXC1:

jerichorivera@PerconaSupport pxc % docker compose exec -it pxc1 bash -c 'mysql -uroot -pt00r -e "create database test; create table test.t1 (id int not null, c varchar(100)); show create table test.t1\G"'
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int NOT NULL,
  `c` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

The other two nodes PXC2 and PXC3 didn’t have the my_row_id column:

jerichorivera@PerconaSupport pxc % docker compose exec -it pxc2 bash -c 'mysql -uroot -pt00r -e "show create table test.t1\G"'
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int NOT NULL,
  `c` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
jerichorivera@PerconaSupport pxc % docker compose exec -it pxc3 bash -c 'mysql -uroot -pt00r -e "show create table test.t1\G"'
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int NOT NULL,
  `c` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

As a test, I created a similar table with no PK in PXC3 expecting the my_row_id column to be created as we see below:

jerichorivera@PerconaSupport pxc % docker compose exec -it pxc3 bash -c 'mysql -uroot -pt00r -e "create table test.t2 (id int not null, t varchar(100)); show create table test.t2\G"'
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
  `id` int NOT NULL,
  `t` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`my_row_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

While the other two nodes PXC1 and PXC2 doesn’t have it:

jerichorivera@PerconaSupport pxc % docker compose exec -it pxc1 bash -c 'mysql -uroot -pt00r -e "show create table test.t2\G"'
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int NOT NULL,
  `t` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
jerichorivera@PerconaSupport pxc % docker compose exec -it pxc2 bash -c 'mysql -uroot -pt00r -e "show create table test.t2\G"'
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int NOT NULL,
  `t` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

I will test in PXC 8.4.x and will update this post soon.

stofa.kenida · March 18, 2026, 3:14pm

Hi Rivera,

The test done is quite good.

But, in the 3 nodes you created,

Set the parameter to ON in config file.
Start mysql instance on each node in the cluster. Now, we are sure that the parameter has not the default value ( OFF ) but it is set to ON.
On all nodes, set dinamically the parameter to OFF
Logoff from mysql and login again. Now, the parametro is OFF for your new session
On one node, create the table
You will see that on the node where the table has been created, my_row_id column is missing. But on the other two nodes, the my_row_id colum is present.

It’s important that mysql on all nodes starts with the parameter set to ON. And must be changed dinamically on all nodes to OFF. After this, logoff e login again in mysql and create the table.

Thanks.

Andrea_Franzoni · March 18, 2026, 3:43pm

Hi all,
started all my cluster’s nodes with sql_generate_invisible_primary_key = ON in the config file

– now I set the variable to OFF on all cluster node –

mysql> select version();
±------------+
| version()   |
±------------+
| 8.0.44-35.1 |
±------------+
1 row in set (0.01 sec)

mysql> set global sql_generate_invisible_primary_key = OFF;
Query OK, 0 rows affected (0.00 sec)

– NEW SESSION on “SERVER 1” –

mysql> CREATE TABLE test (
→ id int NOT NULL,
→ testo varchar(30) DEFAULT NULL
→ ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.20 sec)

mysql> show create table test\G
*************************** 1. row ***************************
Table: test
Create Table: CREATE TABLE test (
id int NOT NULL,
testo varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)

“SERVER 2”
mysql> show create table test\G
*************************** 1. row ***************************
Table: test
Create Table: CREATE TABLE test (
my_row_id bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
id int NOT NULL,
testo varchar(30) DEFAULT NULL,
PRIMARY KEY (my_row_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

jrivera · March 19, 2026, 1:12am

I can finally reproduce the bug and will create a bug report on your behalf. I’ll update this post with the link.

jrivera · March 19, 2026, 2:10am

Here’s the bug report - Jira

Topic		Replies	Views
Safest way to add primary key to table Percona XtraDB Cluster 5.x	5	1710	September 4, 2019
Percona, InnoDB, Galera and deleting without a PK Percona XtraDB Cluster 5.x	1	1609	March 14, 2013
Primary key on a galera cluster Percona XtraDB Cluster 5.x	7	5276	September 20, 2018
Limitations on tables w/o primary keys Percona XtraDB Cluster 5.x	1	2683	June 20, 2013
Question in regards to Xtrabackup and primary keys in a galera cluster Percona XtraBackup	4	1073	January 13, 2021

Sql_generate_invisible_primary_key on XtraDB Cluster

Related topics