ERROR 1467 (HY000): Failed to read auto-increment value from storage engine

Translated
1

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

Original topic: ERROR 1467 (HY000): Failed to read auto-increment value from storage engine

| username: wakaka

[TiDB Usage Environment] Production Environment / Testing / PoC
[TiDB Version] 5.0.6
[Encountered Problem] After mistakenly inserting the maximum value for the auto-increment ID, subsequent inserts result in the error: ERROR 1467 (HY000): Failed to read auto-increment value from storage engine.
[Reproduction Path]
Primary key bigint
Insert into t values 7900000000000000000 inserts the maximum value, and subsequent inserts all fail with the above error.
Even after deleting the dirty data and setting auto_increment to the remaining maximum value +1, the issue persists. The table has approximately 200 million rows.
[Problem Phenomenon and Impact]

2
| username: h5n1 | Original post link

What is this? What does the table structure look like? auto_inc_id and _tidb_rowid are allocated together and come from the same allocator.

3
| username: wakaka | Original post link

PRIMARY KEY (id) /*T![clustered_index] CLUSTERED */,
UNIQUE KEY cid (cid),
KEY create_time (create_time),
KEY aweme_id (aweme_id),
KEY reply_id (reply_id),
KEY short_id (short_id),
KEY unique_id (unique_id),
KEY video_id (video_id),
KEY video_create_time (video_create_time),
KEY brand_ids (brand_ids),
KEY brand_names (brand_names)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=207378376 COMMENT=‘ABC’;

4
| username: wakaka | Original post link

The ID is bigint, and the table structure is very ordinary as shown above.

5
| username: 啦啦啦啦啦 | Original post link

Rebuild the table and try migrating and importing the data for recovery. Directly changing the auto-increment value won’t work; you can check the results with SHOW WARNINGS after making the changes.

6
| username: alfred | Original post link

When the auto-increment reaches its maximum value and it is also the primary key, rename the original table, then create a new table and copy the data to the new table.

7
| username: Kongdom | Original post link

Manually inserting into an auto-increment column is definitely not feasible. It is recommended to rebuild the table and then perform data import and export.

8
| username: rainwolf01 | Original post link

How did you solve it?

9
| username: redgame | Original post link

Use the SHOW CREATE TABLE your_table_name command to check the table definition, ensuring that the auto-increment column is defined correctly and is not set to the UNSIGNED type.

10
| username: zhanggame1 | Original post link

200 million is not much, just use dumpling to export and then rebuild the table for import.

11
| username: ljluestc | Original post link

The error message “ERROR 1467 (HY000): Failed to read auto-increment value from storage engine” indicates that the auto-increment ID has likely reached its maximum value. To address this issue, you can follow these steps:

Check if the auto-increment ID has reached its maximum value:

if (auto_increment_id >= max_auto_increment_value) {
    // Perform actions to address the issue
}

To modify the auto-increment value, you can use the ALTER TABLE statement:
ALTER TABLE your_table AUTO_INCREMENT = new_start_value;

If adjusting the auto-increment value does not resolve the issue, consider redesigning the table structure or adopting other strategies to prevent reaching the maximum auto-increment value.