Docs

RDS for MySQL

Feature

Invisible primary key

Background

Tables without primary keys

Creating tables without primary keys in a MySQL database introduces significant risks to a high-availability architecture:

Binlog synchronization failure When full table updates or range delete operations occur, each row-based binlog event requires a full table scan to locate data during replay on the secondary instance due to the lack of a clustered index. Tests show that these operations on tables with millions of rows cause the primary-secondary synchronized latency to increase exponentially.

Storage engine feature conflict The clustered index feature of the InnoDB engine requires a unique row identifier. When a table lacks an explicit primary key:

The implicit ROW_ID automatically generated by the engine lacks index visibility.

Secondary index leaf nodes are forced to point to the implicit ROW_ID, making the data location path invisible and increasing maintenance costs.

Each DML operation effectively becomes a full table scan.

Avalanche effect on high-availability links: Latency from tables without primary keys is self-worsening:

The primary database continuously generates new binlog events.

The replica database cannot consume the event queue in time due to full table scans.

This eventually interrupts synchronization.

This creates a risk of data inconsistency during failover.

Solutions

Reactive approach: Rebuild the standby database

After a table without a primary key has been created, a reactive solution is to handle the issue without changing the table structure. This involves setting up primary-replica latency alerts, manually checking if the table without a primary key is the cause, and rebuilding the replica database through a work order. This approach creates an additional manual workload and is unsustainable.

Proactive approach: Use hidden primary keys

MySQL Community Edition 8.0.30 introduces the Generated Invisible Primary Key (GIPK) feature. It identifies tables without primary keys in CREATE TABLE statements and adds a user-invisible primary key index to the table by using MySQL's "invisible column" attribute. This approach solves the problem of tables without primary keys without affecting user operations, ensuring that newly created tables always have a primary key.

Introduction to the GIPK capabilities of RDS for MySQL

RDS for MySQL optimizes the community's invisible primary key based on the community version 8.0 and implements a GIPK-like function in MySQL 5.7 by using a unique index. The following compares the invisible primary key capabilities that RDS for MySQL provides for MySQL 8.0, MySQL 8.4, and MySQL 5.7.

GIPK attribute	MySQL 8.0/MySQL 8.4	MySQL 5.7
KEY type	Primary key index (Primary Key) bigint(20), auto_increment	Unique Index bigint(20), auto_increment
Primary key column name	my_row_id	byte_rds_ai_uk_row_id
Conditions for creating a GIPK	The CREATE TABLE statement has no primary key	CREATE TABLE statement without a primary key or unique key
Whether to automatically drop the primary key column after a user adds a primary key.	Yes	Yes
Whether a user can select GIPK with a `SELECT *` query	No	No
Control parameter (session)	sql_generate_invisible_primary_key	loose_sql_generate_invisible_primary_key
Show GIPK Parameter	show_gipk_in_create_table_and_information_schema	loose_show_gipk_in_create_table_and_information_schema

Supported version

MySQL 8.0 version, and the kernel minor version is MySQL 8.0.32_20240415 and above.

MySQL 5.7 version, and the kernel minor version is MySQL 5.7.32_20241015 and above.

MySQL 8.4 version, and the kernel version is MySQL 8.4.7_20260415 and above.

tip

You can view the kernel minor version of an instance by Viewing instance information. If your instance is a version earlier than MySQL 5.7.32_20241015 or MySQL 8.0.32_20241015, you can Upgrade instance kernel minor version.

Usage

Enable invisible primary key

Modify the instance parameter to ON.

MySQL 8.0、MySQL 8.4：sql_generate_invisible_primary_key

MySQL 5.7：loose_sql_generate_invisible_primary_key

tip

The two parameters above are not open for modification. If you need to modify them, submit a ticket to contact technical support.

For instances with kernel minor version 20241215 or later, the value of these two parameters is ON by default.

Viewing the invisible primary key

MySQL 5.7

After enabling the loose_show_gipk_in_create_table_and_information_schema parameter at the session level, you can see the invisible primary key byte_rds_ai_uk_row_id in show create table.

MySQL 8.0

After enabling the show_gipk_in_create_table_and_information_schema parameter at the session level, you can see the invisible primary key my_row_id in show create table.

MySQL 8.4

After enabling the show_gipk_in_create_table_and_information_schema parameter at the session level, you can see the invisible primary key my_row_id in show create table.

DDL compatibility list

After GIPK is enabled, RDS for MySQL supports DDL as described in the following table.

tip

The kernel minor version is 20241015 or a later version.

CREATE TABLE

Description	DDL example	MySQL 5.7	MySQL 8.0	MySQL 8.4
The table creation DDL contains a user-defined auto_increment column (not defined as an index)	create table gipk_test(i int auto_increment, c varchar(255));	Not allowed first. ERROR 1075。 Native behavior, auto inc must be an index
The table creation DDL contains a user-defined auto_increment column (defined as an index)	create table gipk_test(i int auto_increment unique key, c varchar(255));	Allowed first.
The table creation DDL uses the GIPK reserved column name, not as a hidden primary key (not bigint NOT NULL AUTO_INCREMENT)	create table gipk_test(`my_row_id` int auto_increment unique key, c varchar(255));	Not allowed first. Cannot use byte_rds_ai_uk_row_id as a column name.	Allowed, but a warning is output Skips GIPK creation, and my_row_id becomes a regular column.
Use GIPK-related COMMENT	create table gipk_test(i int COMMENT 'Implicit Primary Key by RDS', c varchar(255));	Allowed first.
Create DDL for a partitioned table with no primary key	CREATE TABLE gipk_test ( sale_id INT NOT NULL, sale_date DATE NOT NULL, customer_id INT NOT NULL, amount DECIMAL(10,2) NOT NULL ) PARTITION BY RANGE (YEAR(sale_date)) ( PARTITION p2020 VALUES LESS THAN (2021), PARTITION p2021 VALUES LESS THAN (2022), PARTITION p2022 VALUES LESS THAN (2023), PARTITION p2023 VALUES LESS THAN (2024) );	Allowed first. Skip GIPK creation.
When binlog_format=statement Execute Create table select	set binlog_format=statement; create table gipk_test from select * from t_gipk;	Not allowed first. Cannot be modified to SBR.

ALTER TABLE

Table structure used in the example

GIPK table: create table gipk_test(i int, c varchar(255));

Standard primary key table: create table gipk_test(i int auto_increment primary key, c varchar(255));

Description	DDL example	MySQL 5.7	MySQL 8.0	MySQL 8.4
Add a primary key to a GIPK primary key table	alter table gipk_test add column id int primary key;	Allowed first.
Add an auto-increment column to a table without a primary key	alter table gipk_test add column id int auto_increment;	Not allowed. ERROR 1075 AUTO INC must be an index, which is native behavior.
Add an auto-increment column as a key to a table without a primary key	alter table gipk_test add column id int auto_increment key;	Allowed first.
The user adds an auto-increment column as a UNIQUE KEY to a table without a primary key	alter table gipk_test add column id int auto_increment unique key;	Allowed first.
Drop the hidden primary key column from the table	alter table gipk_test drop column `my_row_id`;	Allowed first.
Drop the primary key column in the table	create table gipk_test(i int primary key, c varchar(255)); alter table gipk_test drop column i;	Allowed first. Becomes a table without a primary key.
Drop the primary key in the table	create table gipk_test(i int primary key, c varchar(255)); alter table gipk_test drop primary key;	Allowed. Becomes a table without a primary key.
Drop the hidden primary key in the table	alter table gipk_test drop key `my_row_id`;	Allowed first.
Promote a column in the table to the primary key	create table gipk_test(f1 int , f2 int , f3 varchar(16) , c varchar(255)); alter table gipk_test add primary key (f1);	Allowed first.
GIPK table uses GIPK as the partition key	CREATE TABLE gipk_test (f1 INT); ALTER TABLE gipk_test PARTITION BY KEY(byte_rds_ai_uk_row_id) PARTITIONS 10;	Not allowed first. Hidden columns should not be used as partition keys, and a DDL error will be reported first.
GIPK table uses other columns as the partition key	CREATE TABLE gipk_test (f1 INT); ALTER TABLE gipk_test PARTITION BY KEY(f1) PARTITIONS 10;	Allowed first. Drop the GIPK column at the same time.

Alert handling

To handle alerts related to hidden primary keys, refer to How to handle errors related to hidden primary keys first.

Last updated: 2026.05.28 15:17:49