Unlocking MySQL GIPKs: How Generated Invisible Primary Keys Simplify Schema Design

1. Overview of GIPKs

From MySQL 8.0.30 onward, a new feature called Generated Invisible Primary Keys (GIPKs) automatically creates an invisible primary‑key column when an InnoDB table is defined without an explicit primary key. The column is named my_row_id, defined as bigint unsigned NOT NULL AUTO_INCREMENT INVISIBLE.

2. Benefits and Use Cases

The feature resolves several long‑standing issues:

DBAs no longer need to force developers to add an explicit auto‑increment primary key, while still allowing explicit non‑auto‑increment keys (e.g., UUID/VARCHAR) when required.

In MySQL Group Replication (MGR) clusters, every InnoDB table no longer has to declare a primary key, simplifying schema management.

Both scenarios benefit from the automatically generated invisible primary key, which MySQL creates as my_row_id.

3. Limitations and Caveats

If a user tries to create a column named my_row_id, MySQL rejects it because the name is reserved by the GIPKs feature. Example error:

greatsql> create table t2(
id bigint unsigned not null auto_increment,
my_row_id int NOT NULL);
ERROR 4109 (HY000): Failed to generate invisible primary key. Auto-increment column already exists.

In traditional master‑slave replication or MGR, the GIPKs setting is not replicated to slaves; it only affects the current node. This does not interfere with replication as long as binlog_format = row is used.

The mysqldump utility adds a new option --skip-generated-invisible-primary-key to control whether the invisible primary key is included in backups.

4. Practical Example in an MGR Environment

The following session demonstrates enabling GIPKs, creating a table without an explicit primary key, and manipulating the invisible column.

# Current version
greatsql> \s
Server version: 8.0.32-24 GreatSQL, Release 24, Revision ...

# Verify GIPKs is ON
greatsql> show variables like 'sql_generate_invisible_primary_key';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| sql_generate_invisible_primary_key  | ON    |
+------------------------------------+-------+

# Create a table without an explicit primary key
greatsql> create table t1 ( id int not null, c1 varchar(10) not null, unique key(id));

# Show the created table
greatsql> show create table t1\G
CREATE TABLE `t1` (
  `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
  `id` int NOT NULL,
  `c1` varchar(10) NOT NULL,
  PRIMARY KEY (`my_row_id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

# SELECT * does not display the invisible column
greatsql> select * from t1;
+----+----+
| id | c1 |
+----+----+
|  1 | c1 |
|  2 | c2 |
+----+----+

# Make the invisible column visible
greatsql> alter table t1 alter column my_row_id set visible;

# Now the column appears in SELECT results
greatsql> select * from t1;
+-----------+----+----+
| my_row_id | id | c1 |
+-----------+----+----+
|         1 |  1 | c1 |
|         2 |  2 | c2 |
+-----------+----+----+

# Hide it again
greatsql> alter table t1 alter column my_row_id set invisible;

# Replace the invisible primary key with a custom one
greatsql> alter table t1 drop column my_row_id, drop primary key, add aid bigint unsigned not null auto_increment primary key first;

# Verify the new structure
greatsql> show create table t1\G
CREATE TABLE `t1` (
  `aid` bigint unsigned NOT NULL AUTO_INCREMENT,
  `id` int NOT NULL,
  `c1` varchar(10) NOT NULL,
  PRIMARY KEY (`aid`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

This walkthrough shows that the GIPKs feature is flexible: the invisible primary key can be queried, made visible, hidden again, or replaced entirely with a user‑defined key.