MySQL 8.0.30 introduces the “Generated Invisible Primary Key” (GIPK) feature. This blog provides an introduction to this feature, discusses its limitations and restrictions, and describes how backup and restore operations work with this feature.Let’s get started!

Background

InnoDB storage engine incorporates an internal feature known as the Implicit Primary Key. This feature automatically generates a hidden clustered index named GEN_CLUST_INDEX on a generated column called DB_ROW_ID. Implicit Primary Key is generated when a table is created without a PRIMARY KEY or UNIQUE KEY, where all key columns are defined as NOT NULL. The DB_ROW_ID column itself is a 6-byte field that increases monotonically as new rows are inserted into the table. The Implicit Primary Key serves as an mechanism to ensure to address limitations associated with table locking primary key. However, alongside its advantages, there are some downsides to consider when dealing with the Implicit Primary Key,

  • Scalability Issue: All tables with the Implicit Primary Key utilize a sequence counter common for all tables to generate unique 6-byte values for the DB_ROW_ID column. However, this counter is protected by a mutex, which can introduce a scalability concern. Concurrent insertions into tables with the Implicit Primary Key may lead to a performance issue due to contention for the mutex.
  • Not suitable for replication setups (Both Classic and High Availability):The use of Implicit Primary Key presents challenges in replication environments due to its reliance on a common sequence counter to generate unique values. For the same row of a table, it is not possible to generate the same value for DB_ROW_ID across multiple replication nodes. As a result, Implicit Primary Key cannot be used in replication setups. This limitation means that tables with an implicit primary key are not supported in high-availability setups, and classic replication does not perform efficiently with such tables.

Downsides of InnoDB Implicit Primary Key motivates to implement new GIPK feature in MySQL. The GIPK feature offers significant convenience for developers and database administrators as it automatically generates a primary key on an invisible column. This eliminates the need to explicitly define a primary key in certain scenarios, while retaining the advantages of having a primary key for ensuring data consistency and optimizing queries. Moreover, the GIPK feature helps address limitations associated with tables lacking a primary key. For instance, Group Replication, a high-availability solution in MySQL, does not support tables without a primary key. Additionally, classic replication may not perform optimally or efficiently with tables that lack a primary key. Hence, the GIPK feature enables developers to overcome these limitations and ensure the smooth operation of their MySQL databases.

GIPK Introduction

When a user attempts to create an InnoDB table without a primary key, MySQL automatically generates a primary key for the table when this feature is enabled. By default, this feature is disabled. To enable it, set the “sql_generate_invisible_primary_key” variable to “ON”.

Here’s an example to demonstrate the difference in table creation in sans-GIPK mode (GIPK feature disabled)  and GIPK mode (GIPK feature enabled):

In sans-GIPK mode

-- Disable GIPK mode
SET GLOBAL sql_generate_invisible_primary_key = OFF;
 
-- Create InnoDB table without primary key
CREATE TABLE users(id INT, name VARCHAR(50), age INT) ENGINE = InnoDB;
 
-- Verify the table structure
SHOW CREATE TABLE users
*************************** 1. row ***************************
Table: users
Create Table: CREATE TABLE `users` (
`id` int DEFAULT NULL,
`name` varchar(50) DEFAULT NULL,
`age` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

In this mode, the existing InnoDB mechanism to create a hidden Implicit Primary Key is used to generate the primary key for tables

In GIPK mode

-- Enable GIPK mode
SET GLOBAL sql_generate_invisible_primary_key = ON;
 
-- Create InnoDB table without primary key
CREATE TABLE users(id INT, name VARCHAR(50), age INT) ENGINE = InnoDB;
 
-- Verify the table structure
SHOW CREATE TABLE users
*************************** 1. row ***************************
       Table: users
Create Table: CREATE TABLE `users` (
  `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
  `id` int DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `age` int DEFAULT NULL,
  PRIMARY KEY (`my_row_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.02 sec)

In GIPK mode, MySQL automatically adds an invisible column named “my_row_id” of type “bigint unsigned NOT NULL auto_increment” as the first column in the table’s column list. Additionally, a primary key is added on the “my_row_id” column.

When a primary key is not explicitly defined for an InnoDB table in a MySQL, the database considers a UNIQUE KEY with all key columns defined as NOT NULL as an implicit primary key.  When GIPK is enabled, a primary key is generated even for tables that are created with an implicit primary key.

Here is an example to demonstrate this:

-- Enable GIPK mode
SET GLOBAL sql_generate_invisible_primary_key = ON;
 
-- Create InnoDB table without primary key
CREATE TABLE users(id INT NOT NULL UNIQUE, name VARCHAR(50), age INT) ENGINE = InnoDB;
 
-- Verify the table structure
SHOW CREATE TABLE users
*************************** 1. row ***************************
       Table: users
Create Table: CREATE TABLE `users` (
  `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
  `id` int NOT NULL,
  `name` varchar(50) DEFAULT NULL,
  `age` int DEFAULT NULL,
  PRIMARY KEY (`my_row_id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.02 sec)

 

Adding GIPK to an already existing tables

In GIPK mode, a primary key is automatically added to the table only when the table is created. Altering a table does not automagically add a primary key for a table without a primary key. Invisible Primary Key can be added to existing table as below,

  • In a stand-alone DB system user can add GIPK to a table as below,
ALTER TABLE users ADD COLUMN my_row_id BIGINT UNSIGNED NOT NULL INVISIBLE AUTO_INCREMENT PRIMARY KEY FIRST;

 

  • In a replication setup, user can follow this multi-phase process to  add GIPK to a table at source,
ALTER TABLE users ADD COLUMN my_row_id BIGINT UNSIGNED DEFAULT NULL INVISIBLE FIRST;
SET @x = 0;                                        
UPDATE users SET my_row_id = (@x := @x+1);
LOCK TABLES users WRITE;
SET @x := (SELECT MAX(my_row_id) FROM users);
UPDATE users SET my_row_id = (@x := @x+1) WHERE my_row_id IS NULL;
ALTER TABLE users ADD PRIMARY KEY(my_row_id), MODIFY COLUMN my_row_id BIGINT UNSIGNED AUTO_INCREMENT INVISIBLE;
UNLOCK TABLES;


After adding the Generated Invisible Primary Key (GIPK) to an existing table, InnoDB’s Implicit Primary Key on the hidden column DB_ROW_ID and the column DB_ROW_ID itself are abandoned.
 

Find GIPK information in SHOW and INFORMATION_SCHEMA tables

Users can view the Generated Invisible Primary Key (GIPK) information in the output of the SHOW CREATE TABLE, SHOW COLUMNS, and SHOW INDEX statements. Additionally, the GIPK information is visible in the Information Schema COLUMNS and STATISTICS tables.

-- Enable GIPK mode
SET GLOBAL sql_generate_invisible_primary_key = ON;
 
-- Create InnoDB table without primary key
CREATE TABLE users(name VARCHAR(50)) ENGINE = InnoDB;
 
-- Verify the table structure
SHOW CREATE TABLE users
*************************** 1. row ***************************
       Table: users
Create Table: CREATE TABLE `users` (
  `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
  `name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`my_row_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.02 sec)
 
-- Verify INFORMATION_SCHEMA.COLUMNS output
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema='test' and table_name='users'\G
*************************** 1. row ***************************
           TABLE_CATALOG: def
            TABLE_SCHEMA: test
              TABLE_NAME: users
             COLUMN_NAME: my_row_id
        ORDINAL_POSITION: 1
          COLUMN_DEFAULT: NULL
             IS_NULLABLE: NO
               DATA_TYPE: bigint
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: 20
           NUMERIC_SCALE: 0
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
             COLUMN_TYPE: bigint unsigned
              COLUMN_KEY: PRI
                   EXTRA: auto_increment INVISIBLE
              PRIVILEGES: select,insert,update,references
          COLUMN_COMMENT:
   GENERATION_EXPRESSION:
                  SRS_ID: NULL
*************************** 2. row ***************************
           TABLE_CATALOG: def
            TABLE_SCHEMA: test
              TABLE_NAME: users
             COLUMN_NAME: name
        ORDINAL_POSITION: 2
          COLUMN_DEFAULT: NULL
             IS_NULLABLE: YES
               DATA_TYPE: varchar
CHARACTER_MAXIMUM_LENGTH: 50
  CHARACTER_OCTET_LENGTH: 200
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: utf8mb4
          COLLATION_NAME: utf8mb4_0900_ai_ci
             COLUMN_TYPE: varchar(50)
              COLUMN_KEY:
                   EXTRA:
              PRIVILEGES: select,insert,update,references
          COLUMN_COMMENT:
   GENERATION_EXPRESSION:
                  SRS_ID: NULL
2 rows in set (0.01 sec)
 
-- Verify SHOW COLUMNS output
SHOW COLUMNS FROM users\G
*************************** 1. row ***************************
Field: my_row_id
Type: bigint unsigned
Null: NO
Key: PRI
Default: NULL
Extra: auto_increment INVISIBLE
*************************** 2. row ***************************
Field: name
Type: varchar(50)
Null: YES
Key:
Default: NULL
Extra:
2 rows in set (0.01 sec)
 
-- Verify INFORMATION_SCHEMA.STATISTICS output
SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE table_schema='test' and table_name='users'\G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: test
TABLE_NAME: users
NON_UNIQUE: 0
INDEX_SCHEMA: test
INDEX_NAME: PRIMARY
SEQ_IN_INDEX: 1
COLUMN_NAME: my_row_id
COLLATION: A
CARDINALITY: 0
SUB_PART: NULL
PACKED: NULL
NULLABLE:
INDEX_TYPE: BTREE
COMMENT:
INDEX_COMMENT:
IS_VISIBLE: YES
EXPRESSION: NULL
1 row in set (0.02 sec)
 
-- Verify SHOW INDEX output
SHOW INDEX FROM users\G
*************************** 1. row ***************************
Table: users
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: my_row_id
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
1 row in set (0.01 sec)


To hide the Generated Invisible Primary Key (GIPK) information in the SHOW and information schema COLUMNS and STATISTICS result sets, users can set the system variable “show_gipk_in_create_table_and_information_schema” to “OFF”. By default, this variable is set to “ON”. Here is an example to demonstrate this:

-- Enable GIPK mode
SET GLOBAL sql_generate_invisible_primary_key = ON;
 
-- Create InnoDB table without primary key
CREATE TABLE users(name VARCHAR(50)) ENGINE = InnoDB;
 
-- Verify the table structure with sql_gipk_in_create_table_and_information_schema=ON
SHOW CREATE TABLE users
*************************** 1. row ***************************
       Table: users
Create Table: CREATE TABLE `users` (
  `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
  `name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`my_row_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.02 sec)
 
-- Hide GIPK information
SET SESSION sql_gipk_in_create_table_and_information_schema=OFF;
 
-- Verify the table structure
SHOW CREATE TABLE users
*************************** 1. row ***************************
       Table: users
Create Table: CREATE TABLE `users` (
  `name` varchar(50) DEFAULT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.02 sec)
 
-- Verify INFORMATION_SCHEMA.COLUMNS output
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema='test' and table_name='users'\G
*************************** 1. row ***************************
           TABLE_CATALOG: def
            TABLE_SCHEMA: test
              TABLE_NAME: users
             COLUMN_NAME: name
        ORDINAL_POSITION: 2
          COLUMN_DEFAULT: NULL
             IS_NULLABLE: YES
               DATA_TYPE: varchar
CHARACTER_MAXIMUM_LENGTH: 50
  CHARACTER_OCTET_LENGTH: 200
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: utf8mb4
          COLLATION_NAME: utf8mb4_0900_ai_ci
             COLUMN_TYPE: varchar(50)
              COLUMN_KEY:
                   EXTRA:
              PRIVILEGES: select,insert,update,references
          COLUMN_COMMENT:
   GENERATION_EXPRESSION:
                  SRS_ID: NULL
1 rows in set (0.01 sec)
 
-- Verify SHOW COLUMNS output
SHOW COLUMNS FROM users\G
*************************** 1. row ***************************
Field: name
Type: varchar(50)
Null: YES
Key:
Default: NULL
Extra:
1 rows in set (0.01 sec)
 
-- Verify INFORMATION_SCHEMA.STATISTICS output
SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE table_schema='test' and table_name='users'\G
Empty set (0.00 sec)
 
-- Verify SHOW INDEX output
SHOW INDEX FROM users\G
Empty set (0.01 sec)

 

Backup and restore:

Users can create a logical backup of their database using the mysqldump and mysqlpump tools. By default, these tools include the Generated Invisible Primary Key (GIPK) along with the column definitions in the table definition, as well as the values of generated columns in the logical dump. This means that when restoring a database from the logical dump, the tables will be created with the GIPK and the corresponding values for the generated columns.

To provide more flexibility, both mysqldump and mysqlpump offer a new option called “–skip-generated-invisible-primary-key“. By using this option, users can create a logical dump without including the GIPK information. This can be useful if the user wants to exclude the generated primary keys from the dump. However, it’s important to note that if the logical dump is restored in GIPK mode, then a primary key will be generated for any tables that do not have a primary key defined.

On the other hand, physical backups always include the GIPK information for the tables. When restoring from a physical backup, the GIPK mode is not applicable, as the backup contains the actual table data along with the generated primary keys.

Overall, the mysqldump and mysqlpump tools provide users with options to include or exclude the GIPK information in their logical backups, while physical backups always preserve the GIPK for the tables.
 

Limitations and Restrictions of GIPK:

  • GIPK feature is applicable to InnoDB tables only. For tables created in other storage engines, the primary key is not generated.
  • If the table being created already has a column with the name “my_row_id”, then the primary key is not generated. 
  • This feature is not supported for partitioned tables at the moment. If a partitioned table is created without a primary key, then an error is reported.  
    • The workaround is to explicitly create a primary key on such tables. If there is no suitable natural primary key, a primary key consisting of partitioning columns and an invisible column with a UUID as the default value can be used. However, when adding such a primary key to existing tables with binary log enabled, there may be an issue with ALTER TABLE statements adding an invisible column with a UUID-based default. To work around this issue, a multi-phase process needs to be followed.
       
      ALTER TABLE t1 ADD COLUMN my_row_id VARBINARY(16)
      ALTER TABLE t1 CHANGE COLUMN my_row_id SET DEFAULT (UUID_TO_BIN(UUID())
      LOCK TABLES t1 WRITE;
      -- This statement will be binlogged using ROW format!
      UPDATE TABLE t1 SET row_id = UUID_TO_BIN(UUID()) WHERE my_row_id IS NULL;
      ALTER TABLE t1 ADD PRIMARY KEY (part_col1, ... part_colN, my_row_id);
      UNLOCK TABLES;
  • If the table being created already has an AUTO_INCREMENT column, then the primary key is not added.  
    • A possible workaround is to manually create a UUID-based invisible primary key using the multi-phase process described for the partitioned tables.
  • If no primary key is specified in the CREATE TABLE … SELECT statement, a primary key is generated. However, if the SELECT statement includes a column with the name “my_row_id”, a primary key is not generated, and an error is reported.
    • The workaround for this case is to explicitly add a primary key on the “my_row_id” column within the same CREATE TABLE … SELECT statement, as shown in the example below:
       

       
      CREATE TABLE users_copy (my_row_id BIGINT UNSIGNED
                               INVISIBLE AUTO_INCREMENT       PRIMARY KEY) 
                                     AS SELECT my_row_id, users.* FROM users;
  • When using statement based binary logging, replicating CREATE TABLE  … SELECT with generated invisible primary key is not safe, so an error is reported in this case.
  • If source table of CREATE TABLE … LIKE does not have primary key, then the primary key is not generated for the target table. CREATE TABLE … LIKE is often used in the situation where a target table definition must be like a source table definition. So to keep the same behavior, the primary key is not added to the target table.
  •  ALTER TABLE operation to CHANGE/MODIFY/ALTER/RENAME “my_row_id” column is not allowed. An error is generated in this case.
    • Changing VISIBILITY of the “my_row_id” is an exception. This operation is allowed on the “my_row_id”. Please note, changing visibility of “my_row_id” column effects the properties of Generated Invisible Primary Key too.
  • ALTER TABLE … DROP PRIMARY KEY to drop Generated Invisible Primary Key, without dropping “my_row_id” column in the same statement, is not allowed.
  • In GIPK mode, ALTER TABLE operations which results in a table without primary key are not allowed. An error is reported in this case.
     

References

Please check out this new feature and let us know your feedback.
 

Thanks for using MySQL!