Handling Primary Key-Related Error Hiding
Download
Mode fokus
Ukuran font
This document describes the errors you may encounter when creating tables or modifying primary keys after enabling the hidden primary key feature in TencentDB for MySQL, and the methods for correctly viewing, replacing/removing the hidden primary key, or disabling this feature.
Background
TencentDB for MySQL 8.0 kernel minor version 20221215 (Community Edition 8.0.30) and later support the GIPK (Generated Invisible Primary Keys) feature, while TencentDB for MySQL 5.7 does not. If you do not explicitly define a primary key when creating a table, the system automatically adds a hidden primary key named `my_row_id`. After the hidden primary key is added, you may encounter errors if you attempt to define a new primary key using DDL.
Error Message
When you define a new primary key in an ALTER TABLE statement, you may encounter the following error:
Error Scenario 1:
Error 1068: Multiple primary key defined.
Error Explanation: The custom primary key conflicts with the hidden primary key.
Error Scenario 2:
Error 1075: Incorrect table definition; there can be only one auto column and it must be defined as a key.
Error Explanation: A conflict occurs between the new auto-increment column and the hidden primary key.
When you define a new primary key in a
CREATE TABLE statement, you may encounter the following error:Error Scenario 3:
Error 4109: Failed to generate invisible primary key. Auto-increment column already exists.
Error Explanation: When GIPK is enabled, the auto-increment column is not defined as the primary key index.
Viewing GIPK
Because GIPK has the "hidden column" attribute, you cannot directly view it using
show create table. Please use the following method to view the GIPK.1. Set the parameter `sql_generate_invisible_primary_key` to ON.
2. After the configuration is complete, you can view the hidden primary key `my_row_id` in
show create table.mysql> show create table tb1;+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| tb1 | CREATE TABLE `tb1` (`my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,`id` int DEFAULT NULL,`c` varchar(16) DEFAULT NULL,PRIMARY KEY (`my_row_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.01 sec)
3. You do not need to disable GIPK when replacing the primary key; simply keep it enabled.
mysql> select @@sql_generate_invisible_primary_key;+--------------------------------------+| @@sql_generate_invisible_primary_key |+--------------------------------------+| 1 |+--------------------------------------+1 row in set (0.00 sec)
Replacing GIPK with a Custom Primary Key
Upgrading an Existing Column to a Primary Key
Operation Description: Designate a field that already exists in the original table as the primary key.
Error SQL Example:
alter table table_name add primary key(id);
Error Details:
Error 1068: Multiple primary key defined.Example of the Table Structure Before Modification: It contains the hidden primary key column `my_row_id`.
mysql> show create table tb1;+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| tb1 | CREATE TABLE `tb1` (`my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,`id` int DEFAULT NULL,`c` varchar(16) DEFAULT NULL,PRIMARY KEY (`my_row_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.01 sec)
Modification SQL:
alter table tb1 drop column my_row_id, add primary key(id);
Table Structure After Modification: The hidden primary key `my_row_id` is removed, and `id` becomes the new primary key.
mysql> show create table tb1;+-------+--------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+-------+--------------------------------------------------------------------------------------------------------------------------------------+| tb1 | CREATE TABLE `tb1` (`id` int NOT NULL,`c` varchar(16) DEFAULT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |+-------+--------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)
Adding a New Primary Key Column
Operation Description: Add a new column and designate it as the primary key.
Error SQL Example:
alter table table_nameadd column new_id bigint primary key;
Related Error:
Error 1068: Multiple primary key defined.Example of the Table Structure Before Modification: It contains the hidden primary key column `my_row_id`.
mysql> show create table tb2;+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| tb2 | CREATE TABLE `tb2` (`my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,`i` int DEFAULT NULL,`c` varchar(16) DEFAULT NULL,PRIMARY KEY (`my_row_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Modification SQL:
Add a column to replace the GIPK. The new column has no additional requirements. Here, we demonstrate adding a new column named `new_id` with the BIGINT attribute.
alter table table_namedrop column my_row_id,add column new_id bigint primary key;
Table Structure After Modification: The hidden primary key `my_row_id` is removed, and `new_id` becomes the new primary key.
mysql> show create table tb2;+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| tb2 | CREATE TABLE `tb2` (`i` int DEFAULT NULL,`c` varchar(16) DEFAULT NULL,`new_id` bigint unsigned NOT NULL AUTO_INCREMENT,PRIMARY KEY (`new_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Adding an Auto-Increment Column
Operation Description: Add a new auto-increment column.
Error SQL Example:
alter table table_nameadd column new_id bigint unsigned not null auto_increment key;
Error Details:
Error 1075: Incorrect table definition; there can be only one auto column and it must be defined as a key.Example of the Table Structure Before Modification: It contains the hidden primary key column `my_row_id`.
mysql> show create table tb3;+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| tb3 | CREATE TABLE `tb3` (`my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,`i` int DEFAULT NULL,`c` varchar(16) DEFAULT NULL,PRIMARY KEY (`my_row_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Modification SQL: Add an auto-increment column named `new_id` and replace the GIPK.
alter table table_namedrop column my_row_id,add column new_id bigint unsigned not null auto_increment key;
Table Structure After Modification: The hidden primary key `my_row_id` is removed, and `new_id` becomes the new primary key.
mysql> show create table tb3;+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| tb3 | CREATE TABLE `tb3` (`i` int DEFAULT NULL,`c` varchar(16) DEFAULT NULL,`new_id` bigint unsigned NOT NULL AUTO_INCREMENT,PRIMARY KEY (`new_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Creating a Table with an Auto-Increment Column
Operation Description: Create a table that contains an auto-increment column.
Error SQL Example:
create table table_name (id bigint unsigned NOT NULL AUTO_INCREMENT UNIQUE KEY,c varchar(16));
Error Details:
Error 4109: Failed to generate invisible primary key. Auto-increment column already exists.Modification SQL: Use the PRIMARY KEY or KEY keyword.
create table table_name (id bigint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,c varchar(16));
Table Structure After Modification: `id` is the new primary key.
mysql> show create table tb4;+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| tb4 | CREATE TABLE `tb4` (`id` bigint unsigned NOT NULL AUTO_INCREMENT,`c` varchar(16) DEFAULT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Creating a Partitioned Table with GIPK Enabled
Operation Description: Create a partitioned table without a primary key.
Error SQL Example:
CREATE TABLE tb5 (f1 INT, f2 DATE) PARTITION BY KEY(f2) PARTITIONS 2;
Error Details:
Error 1235: This version of MySQL doesn't yet support 'generating invisible primary key for the partitioned tables.Modification Operation: Set the value of the parameter `sql_generate_invisible_primary_key` to OFF.
References
Bantuan dan Dukungan
Apakah halaman ini membantu?
Anda juga dapat Menghubungi Penjualan atau Mengirimkan Tiket untuk meminta bantuan.
masukan