Troubleshoot duplicate primary key values in AUTO_INCREMENT columns - PolarDB

Auto-increment columns in PolarDB-X behave differently from standard MySQL in several ways. Understanding these constraints before you design your schema helps you avoid duplicate primary key errors and unexpected gaps in auto-increment values.

Auto-increment values are not guaranteed to be continuous. Do not build application logic that depends on gap-free sequences. This is expected behavior in PolarDB-X distributed databases.

Limits

Limit	Details
AUTO mode	An auto-increment column can only be associated with a NEW sequence.
DRDS mode	Non-partitioned tables are not associated with a sequence by default. To associate one, specify the sequence explicitly.
TIME sequence data type	An auto-increment column associated with a TIME sequence must use the BIGINT data type.
Custom step sizes	Not supported. The `auto_increment_increment` variable is fixed at `1`. The `auto_increment_offset` variable is not supported.

Usage notes

Implicit primary keys

If you create a table without a primary key, PolarDB-X automatically creates an implicit primary key using the AUTO_INCREMENT keyword. It also creates a GROUP sequence and associates it with that implicit primary key.

Hint-based INSERT pushdown

When an INSERT statement includes a hint that specifies a database shard — for example, INSERT INTO ... VALUES ... or INSERT INTO ... SELECT ... — and the destination table has an auto-increment column, PolarDB-X does not optimize the statement. Instead, it pushes the statement down to the data node layer. The physical table shard on the data node then generates values for the auto-increment column, bypassing the associated sequence.

This can produce values that conflict with sequence-generated values, leading to duplicate primary key errors. See Fix duplicate primary key values for resolution steps.

Modifying sequences

Use ALTER SEQUENCE to add or remove an auto-increment column or to change its values.

Non-continuous values in NEW sequences

In a NEW sequence, Global Meta Service (GMS) allocates AUTO_INCREMENT values. To ensure that the performance is as expected, you must cache values in GMS. When GMS performs operations such as upgrade, switchover, configuration change, or migration, it discards the cached values before the operation begins. Subsequent values skip past the discarded range, producing gaps.

Fix duplicate primary key values

Duplicate primary key values occur when hint-based INSERT statements are pushed down to the data node layer. Physical table shards generate values independently, without coordinating with the associated sequence. The sequence later generates values that overlap with the already-inserted ones.

Follow these steps to resolve the issue.

Run SHOW SEQUENCES to list existing sequences. Sequences with the AUTO_SEQ_ prefix are implicit sequences — automatically created from the AUTO_INCREMENT column when the table was created.

SHOW SEQUENCES;

Sample output:

+---------------------+-------+--------------+------------+-----------+-------+-------+
| NAME                | VALUE | INCREMENT_BY | START_WITH | MAX_VALUE | CYCLE | TYPE  |
+---------------------+-------+--------------+------------+-----------+-------+-------+
| AUTO_SEQ_xkv_t_item | 0     | N/A          | N/A        | N/A       | N/A   | GROUP |
| AUTO_SEQ_xkv_shard  | 0     | N/A          | N/A        | N/A       | N/A   | GROUP |
+---------------------+-------+--------------+------------+-----------+-------+-------+
2 rows in set (0.04 sec)

Query the current maximum value in the primary key column of the affected table. In this example, the id column of xkv_t_item contains duplicates.
```
SELECT MAX(id) FROM xkv_t_item;
```
Sample output:
```
+---------+
| MAX(id) |
+---------+
| 8231    |
+---------+
1 row in set (0.01 sec)
```
Set the sequence start value to a number greater than the current maximum. In this example, the maximum is 8231, so set the start value to 9000. All values generated after this change will be 9000 or higher, preventing conflicts with existing rows.
```
ALTER SEQUENCE AUTO_SEQ_xkv_t_item START WITH 9000;
```