Interpretation of new features | MySQL 8.0 GIPK invisible primary key

Author: Yang Qilong

Net name "North is in the South", senior DBA, mainly responsible for database architecture design and operation and maintenance platform development, good at database performance tuning and fault diagnosis.

Source of this article: original contribution

*The original content is produced by the open source community of Aikesheng, and the original content shall not be used without authorization. For reprinting, please contact the editor and indicate the source.

---

I. Introduction

As a MySQL DBA, I believe that everyone has experienced that in replication mode, if there is no primary key, when there is a large amount of table data row scans such as load data, large transactions, ddl, etc., it will bring serious master-slave delay and stabilize the database. security and data consistency bring hidden dangers.

MySQL 8.0.30 has recently been GA, the new version provides us with a surprising feature - (Generated Invisible Primary Keys) referred to as GIPK. An overview is: when the GIPK mode is turned on, MySQL will automatically generate an invisible primary key on an InnoDB table that does not display a defined primary key .

For friends who have already used Cloud RDS, they may have enjoyed the implicit primary key feature provided by Cloud RDS MySQL very early on. However, for enterprises that build their own databases, GIPK is still a relatively expected feature (of course, having it and using it are two different things!)

Long story short, this article looks at how schools use GIPK based on actual test cases.

2. Practice brings true knowledge

2.1 Turn on

GIPK is controlled by the parameter sql_generate_invisible_primary_key . It is disabled by default, which means it is disabled. If you need to use this feature, you need to explicitly enable it.

 master [localhost:22031]> show variables like 'sql_generate_invisible_primary_key';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| sql_generate_invisible_primary_key | OFF   |
+------------------------------------+-------+
1 row in set (0.00 sec)

master [localhost:22031]> set sql_generate_invisible_primary_key=on;
Query OK, 0 rows affected (0.00 sec)

master [localhost:22031]> show variables like 'sql_generate_invisible_primary_key';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| sql_generate_invisible_primary_key | ON    |
+------------------------------------+-------+
1 row in set (0.00 sec)

2.2 Testing

We create two non-primary key tables with this feature turned off and turned on:

 master [localhost:22031]> create table t1(id int ,c1 int);
Query OK, 0 rows affected (0.00 sec)
master [localhost:22031] {msandbox} (test) > show create table t1 \G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int DEFAULT NULL,
  `c1` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

Turn on GIPK and create a table t3 without a primary key.

 master [localhost:22031]> set sql_generate_invisible_primary_key=on;
Query OK, 0 rows affected (0.00 sec)

master [localhost:22031] {msandbox} (test) > show variables like 'sql_generate_invisible_primary_key';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| sql_generate_invisible_primary_key | ON    |
+------------------------------------+-------+
1 row in set (0.00 sec)

master [localhost:22031]> create table t3(id int ,c1 int);
Query OK, 0 rows affected (0.01 sec)

master [localhost:22031]>
master [localhost:22031]> show create table t3 \G
*************************** 1. row ***************************
       Table: t3
Create Table: 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 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

We can find the table structure of t3 through show create table, and there is an invisible primary key named my_row_id . Insert data into two tables to see the difference:

 master [localhost:22031]> insert into t1 values(1,1),(2,2),(3,3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
master [localhost:22031]> select * from t1;
+------+------+
| id   | c1   |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    3 |    3 |
+------+------+
3 rows in set (0.00 sec)
master [localhost:22031]> insert into t3 values(1,1),(2,2),(3,3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
master [localhost:22031]> select * from t3;
+------+------+
| id   | c1   |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    3 |    3 |
+------+------+
3 rows in set (0.00 sec)

When querying directly through select * from table, there is no difference between t3 and ordinary table t1. Because GIPK is implemented based on invisible columns, if we explicitly specify access my_row_id , we can view the hidden primary key my_row_id .

 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 |
+-----------+------+------+
3 rows in set (0.00 sec)

In general, from the perspective of business programs accessing the database, enabling GIPK is transparent to the business.

2.3 About DDL

When the GIPK feature is enabled, the primary key generated by MySQL cannot be changed, and can only be switched between VISIBLE and INVISIBLE. for example:

Make the GIPK primary key visible: alter table TALBE_NAME alter column my_row_id set visible;

 master [localhost:22031]> alter table t3 alter column my_row_id set visible;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
master [localhost:22031]> show create table t3 \G
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT, ### 显式可见 
  `id` int DEFAULT NULL,
  `c1` int DEFAULT NULL,
  PRIMARY KEY (`my_row_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

### 而且可以被直接查询到
master [localhost:22031]> select * from t3;
+-----------+------+------+
| my_row_id | id   | c1   |
+-----------+------+------+
|         1 |    1 |    1 |
|         2 |    2 |    2 |
|         3 |    3 |    3 |
+-----------+------+------+
3 rows in set (0.00 sec)

Turn off visibility: alter table TABLE_NAME alter column my_row_id set invisible;

 master [localhost:22031]> alter table t3 alter column my_row_id set invisible;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
master [localhost:22031]> show create table t3 \G
*************************** 1. row ***************************
       Table: t3
Create Table: 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 AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
## 再次通过select * 查询则看不到 my_row_id
master [localhost:22031]> select * from t3;
+------+------+
| id   | c1   |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    3 |    3 |
+------+------+
3 rows in set (0.00 sec)

In addition, after opening GIPK, my_row_id is a system keyword. When we create a table without a primary key, we cannot include a field named my_row_id .

 master [localhost:22031]> create table t6(my_row_id int not null   ,c1 int);
ERROR 4108 (HY000): Failed to generate invisible primary key. Column 'my_row_id' already exists.

Of course if MySQL allows to create a table with a primary key named my_row_id :

 master [localhost:22031]> create table t5(my_row_id int not null auto_increment primary key  ,c1 int);
Query OK, 0 rows affected (0.01 sec)

When GIPK mode is enabled, the invisible primary key cannot be deleted directly. You must explicitly add a new primary key and then delete the GIPK

 master [localhost:22031]> alter table t3 drop PRIMARY KEY;
ERROR 1235 (42000): This version of MySQL doesn't yet support 'existing primary key drop without adding a new primary key. In @@sql_generate_invisible_primary_key=ON mode table should have a primary key. Please add a new primary key to be able to drop existing primary key.'

master [localhost:22031]> alter table t3 drop PRIMARY KEY,add primary key(id);
ERROR 4111 (HY000): Please drop primary key column to be able to drop generated invisible primary key.

master [localhost:22031]> alter table t3 drop column my_row_id,add primary key(id);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

2.4 About master-slave replication

It should be noted that set sql_generate_invisible_primary_key=on|off will not be copied to the slave library. If this feature is enabled on the master library, the slave library will not enable GIPK. That is to say, the slave library will not create a primary key for any table that does not have a primary key created on the source library. There may be questions from readers if the main library turns off this feature, but the slave library shows it is turned on? do a test

Turn off this feature on the master and create a table t6 without a primary key

 master [localhost:22031]> set sql_generate_invisible_primary_key=off;
Query OK, 0 rows affected (0.00 sec)
master [localhost:22031]>
master [localhost:22031]>show variables like 'sql_generate_invisible_primary_key';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| sql_generate_invisible_primary_key | OFF   |
+------------------------------------+-------+
1 row in set (0.00 sec)

master [localhost:22031]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
| t2             |
| t3             |
| t4             |
| t5             |
+----------------+
5 rows in set (0.00 sec)

master [localhost:22031]> create table t6(id int ,c1 int);
Query OK, 0 rows affected (0.01 sec)

master [localhost:22031]> show create table t6\G
*************************** 1. row ***************************
       Table: t6
Create Table: CREATE TABLE `t6` (
  `id` int DEFAULT NULL,
  `c1` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

Enable this feature on the slave library

 slave1 [localhost:22032]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
| t2             |
| t3             |
| t4             |
| t5             |
+----------------+
5 rows in set (0.00 sec)
slave1 [localhost:22032]> set sql_generate_invisible_primary_key=on;
Query OK, 0 rows affected (0.00 sec)
slave1 [localhost:22032]> show variables like 'sql_generate_invisible_primary_key';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| sql_generate_invisible_primary_key | ON    |
+------------------------------------+-------+
1 row in set (0.00 sec)
slave1 [localhost:22032]> show create table t6\G
*************************** 1. row ***************************
       Table: t6
Create Table: CREATE TABLE `t6` (
  `id` int DEFAULT NULL,
  `c1` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

Result: The master library closes GIPK, the slave library opens GIPK, the source library creates a table without a primary key, and the slave library does not actively create a primary key for the table.

2.5 Logical Backup

Most instances will perform logical backups. If the GIPK mode is enabled, the --skip-generated-invisible-primary-key option provided by the mysqldump of MySQL 8.0.30 will ignore the GIPK information. Simply put, mysqldump does not include this parameter, and the logically exported data will contain the implicit primary key. If this parameter is included, the implicit primary key will not be included.

2.6 Restrictions

1. Only the InnoDB storage engine is supported.
2. Supports row mode replication, does not support statement mode replication.
3. my_row_id becomes the system keyword.

3. Summary

Overall, this feature is definitely a strong demand. After all, the forest is big, and anything can happen. Operation (chu) dimension (li) experience (gu) experience (zhang) is relatively rich DBA, MySQL database stability suffers from no primary key, for self-built scenarios, especially for companies without an audit process, this feature can Improve database system stability and security.

Reference documentation

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