| Bug #116345 | Feature Request: Create GPIKs on existing tables | ||
|---|---|---|---|
| Submitted: | 12 Oct 2024 17:32 | Modified: | 14 Oct 2024 4:37 |
| Reporter: | Mershad Irani | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: DDL | Severity: | S4 (Feature request) |
| Version: | 9.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[13 Oct 2024 4:46]
Mershad Irani
Workaround:
==========
Clarifying the workaround, the invisible column still shows up in "show create table" even though show_gipk_in_create_table_and_information_schema=0
While you can simulate GPIKs using the below method, it is not a GPIK in a true sense because the invisible column still shows up in "show create table"
mysql> set show_gipk_in_create_table_and_information_schema = 0 ;
Query OK, 0 rows affected (0.00 sec)
mysql> create table t1(col1 int);
Query OK, 0 rows affected (0.03 sec)
mysql> ALTER TABLE t1
-> ADD COLUMN my_row_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT INVISIBLE,
-> ADD PRIMARY KEY (my_row_id);
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE t1 ALTER COLUMN my_row_id SET INVISIBLE;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table t1 \G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`col1` int DEFAULT NULL,
`my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
PRIMARY KEY (`my_row_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
[14 Oct 2024 4:37]
MySQL Verification Team
Hello Mershad, Thank you for the feature request. regards, Umesh
Description: Feature Request: =============== Currently, you can create a new table with Generated Invisible Primary Keys (GPIK) by enabling sql_generate_invisible_primary_key. However, we don't have the ability to add GPIKs to existing tables. It would be helpful to have the ability to add GPIKs to existing tables for consistent primary key implementation across new and existing tables. The syntax can look something similar to the below. ALTER TABLE table_name ADD GENERATED INVISIBLE PRIMARY KEY; I look forward to your feedback and the possibility of seeing this functionality in a future MySQL release. Workaround: ========== While you can simulate GPIKs using the below method, it is not a GPIK in a true sense because the invisible column still shows up in "show create table" mysql> create table t1(col1 int); Query OK, 0 rows affected (0.03 sec) mysql> ALTER TABLE t1 -> ADD COLUMN my_row_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT INVISIBLE, -> ADD PRIMARY KEY (my_row_id); Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE t1 ALTER COLUMN my_row_id SET INVISIBLE; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table t1 \G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `col1` int DEFAULT NULL, `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */, PRIMARY KEY (`my_row_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) Note: ==== Rebuilding the table by setting sql_generate_invisible_primary_key=1 does not help achieve this. I understand that this is by design. mysql> create table t1(col1 int); Query OK, 0 rows affected (0.18 sec) mysql> SET sql_generate_invisible_primary_key=ON; Query OK, 0 rows affected (0.00 sec) mysql> alter table t1 engine=innodb, force; Query OK, 0 rows affected (0.09 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table t1 \G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `col1` int DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) How to repeat: N/A, since this is a feature request.