Bug #113511 gipk show_gipk_in_create_table_and_information_schema misleading

• Submitted: 26 Dec 2023 2:31
• Modified: 27 Dec 2023 15:48
• Reporter: zongyi chen (OCA)
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• OS: Any
• Assigned to: MySQL Verification Team
• CPU Architecture: Any

[26 Dec 2023 2:31] zongyi chen

Description:
drop table if exists t1;
SET SESSION sql_generate_invisible_primary_key=ON;
SET SESSION show_gipk_in_create_table_and_information_schema = OFF;
CREATE TABLE t1(f1 INT);
alter table t1 add key(my_row_id);
show create table t1;

![image-20231226102820014](http://image.huawei.com/tiny-lts/v1/images/3f797896542d668773579d01d6a42cfd_814x224.png@90...)

in this section ,my_row_id key will be show ,but my_row_id column do not show.is it acceptable?

How to repeat:
drop table if exists t1;
SET SESSION sql_generate_invisible_primary_key=ON;
SET SESSION show_gipk_in_create_table_and_information_schema = OFF;
CREATE TABLE t1(f1 INT);
alter table t1 add key(my_row_id);
show create table t1;

Suggested fix:
null

[27 Dec 2023 15:48] MySQL Verification Team

Hi,

The image you linked does not work so I cannot say what you are seeing here as a. bug but looking at the test case:

mysql> SET SESSION sql_generate_invisible_primary_key=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> SET SESSION show_gipk_in_create_table_and_information_schema = OFF;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t1(f1 INT);
Query OK, 0 rows affected (0.03 sec)

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `f1` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> alter table t1 add key(my_row_id);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t1;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                    |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `f1` int DEFAULT NULL,
  KEY `my_row_id` (`my_row_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> 

This is expected behavior so it is not a bug