How to Reset the AUTO_INCREMENT (Auto-Increment Primary Key) of a Table?

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 如何重置一个表的AUTO_INCREMENT （自增主键）？

| username: exhaustedog

[TiDB Usage Environment] Production Environment / Testing / Poc
Testing Environment
[TiDB Version]
V4.0.9
[Reproduction Path] What operations were performed that caused the issue
It seems that a developer directly inserted the maximum value into the auto-increment ID column, updating the AUTO_INCREMENT column to the maximum value. How can we reset the AUTO_INCREMENT value of the table?
[Encountered Issue: Problem Phenomenon and Impact]
The table structure is as follows:

CREATE TABLE `dcs_option_20230111` (
  `option_id` int(11) NOT NULL AUTO_INCREMENT,
  `survey_id` int(11) DEFAULT '0',
  `template_id` int(11) DEFAULT '0' COMMENT 'Template ID',
  `question_id` int(11) DEFAULT '0',
  `option_value` text DEFAULT NULL,
  `option_identity` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' COMMENT 'Option Identifier',
  PRIMARY KEY (`option_id`),
  KEY `question_id` (`question_id`),
  KEY `survey_id` (`survey_id`),
  KEY `option_identity` (`option_identity`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

A developer inserted a SQL statement specifying option_id, causing option_id to become the maximum value, and data cannot be inserted without specifying a value.
The insert statement is as follows:

INSERT INTO `dcs_option_20230111` 
(`option_id`, `survey_id`, `template_id`, `question_id`, `option_value`) VALUES
('2147483647', '12689831', '0', '169791211', 'Option 1');

The table creation statement then becomes:

CREATE TABLE `dcs_option_20230111` (
  `option_id` int(11) NOT NULL AUTO_INCREMENT,
  `survey_id` int(11) DEFAULT '0',
  `template_id` int(11) DEFAULT '0' COMMENT 'Template ID',
  `question_id` int(11) DEFAULT '0',
  `option_value` text DEFAULT NULL,
  `option_identity` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' COMMENT 'Option Identifier',
  PRIMARY KEY (`option_id`),
  KEY `question_id` (`question_id`),
  KEY `survey_id` (`survey_id`),
  KEY `option_identity` (`option_identity`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=2147513648

At this point, we cannot insert data without specifying option_id, and the following error is reported:

Error Code: 1062 Duplicate entry '2147483647' for key 'PRIMARY'

Using the SQL:

ALTER TABLE dcs_option_20230111 AUTO_INCREMENT=566167158;

and restarting the TiDB component, the table creation statement still shows the original maximum value. Is there any other SQL or method to reset this value?

[Resource Configuration]
[Attachment: Screenshot/Log/Monitoring]

2
| username: 我是咖啡哥 | Original post link

It seems impossible. I went through the documentation and conducted several tests, but none were successful. Using “alter table … auto_increment” gives a warning. Moreover, the auto_increment value shown in “show create table” does not appear to be the current auto-increment value.

3
| username: Lucien-卢西恩 | Original post link

You can only increase the value of auto_increment; decreasing it will not take effect. If you must reset it, it is recommended to reset everything to avoid primary key conflicts. Alternatively, you can filter for primary key conflicts and existing option_id, and when encountering a conflict error, the business can specify a non-conflicting key to resolve the error.

4
| username: 裤衩儿飞上天 | Original post link

CREATE TABLE dcs_option_20230111( option_idbigint(11) NOT NULL AUTO_INCREMENT,

5
| username: xingzhenxiang | Original post link

If the id is meaningless, you can create a new table, import the data except for the id into the new table, and then rename the table.

6
| username: exhaustedog | Original post link

Thank you all, I understand now. Besides modifying the field type or re-importing, there is currently no better solution.

7
| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.