Using Generated Invisible Primary Keys (GIPK) in MySQL 8.0.30

1. Introduction

As a MySQL DBA, you may have experienced serious master‑slave lag when loading data, large transactions, or DDL on tables that lack a primary key. MySQL 8.0.30 introduces Generated Invisible Primary Keys (GIPK), which automatically creates an invisible primary key on InnoDB tables that do not define one.

2. Practical Exploration

2.1 Enabling GIPK

The feature is controlled by the sql_generate_invisible_primary_key system variable, which is OFF by default. Turn it on with:

master [localhost:22031]> set sql_generate_invisible_primary_key=on;

Verify the change:

master [localhost:22031]> show variables like 'sql_generate_invisible_primary_key';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| sql_generate_invisible_primary_key | ON    |
+------------------------------------+-------+

2.2 Testing the Feature

Create two tables without primary keys, one with GIPK disabled (t1) and one with it enabled (t3):

master [localhost:22031]> create table t1(id int, c1 int);
master [localhost:22031]> show create table t1 \G
... (definition without primary key)

master [localhost:22031]> set sql_generate_invisible_primary_key=on;
master [localhost:22031]> create table t3(id int, c1 int);
master [localhost:22031]> show create table t3 \G
CREATE TABLE `t3` (
  `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
  `id` int DEFAULT NULL,
  `c1` int DEFAULT NULL,
  PRIMARY KEY (`my_row_id`)
) ENGINE=InnoDB ...

Inserting data into both tables shows identical results when selecting * . The invisible column my_row_id can be queried explicitly:

master [localhost:22031]> select my_row_id, id, c1 from t3;
+-----------+------+------+
| my_row_id | id   | c1   |
+-----------+------+------+
|         1 |    1 |    1 |
|         2 |    2 |    2 |
|         3 |    3 |    3 |
+-----------+------+------+

From the application perspective, GIPK is transparent.

2.3 DDL Operations

The generated primary key can only be toggled between VISIBLE and INVISIBLE. To make it visible:

alter table t3 alter column my_row_id set visible;

After the change, my_row_id appears in normal SELECT * results. To hide it again:

alter table t3 alter column my_row_id set invisible;

Because my_row_id is a reserved keyword when GIPK is on, you cannot create another column with the same name in a table that relies on the generated key.

2.4 Replication Considerations

The setting set sql_generate_invisible_primary_key=on|off is not replicated. If the master disables GIPK while a replica enables it, the replica will not automatically create invisible keys for tables created on the master.

Result: a table created without a primary key on the master remains without a primary key on the replica, even if the replica has GIPK enabled.

2.5 Logical Backup

When using mysqldump on MySQL 8.0.30, the option --skip-generated-invisible-primary-key omits the invisible primary key from the dump. Without this option, the dump includes the hidden key.

2.6 Limitations

Only supported with the InnoDB storage engine.

Works with row‑based replication; statement‑based replication is not supported.

The column name my_row_id becomes a system keyword.

3. Conclusion

The GIPK feature is a strong requirement for environments that frequently create tables without primary keys, especially in self‑managed MySQL deployments. It improves stability and data consistency by automatically providing a hidden primary key, while remaining transparent to most applications.

References

https://dev.mysql.com/doc/refman/8.0/en/create-table-gipks.html

https://dev.mysql.com/doc/refman/8.0/en/invisible-columns.html