In TiDB 6.5.5, auto-increment primary keys are mostly odd numbers

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

Original topic: tidb 6.5.5 主键自增基本都是奇数

| username: Vincent_Wang

[TiDB Usage Environment] Production Environment
[TiDB Version]
V6.5.5
[Reproduction Path]
Upgraded from 5.4.3 to 6.5.5
[Encountered Problem: Problem Phenomenon and Impact]

Auto-increment primary keys are mostly odd numbers




Tested and found it related to NONCLUSTERED. This issue did not exist in 5.4.3. How to handle this?

[Resource Configuration] Go to TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
[Attachments: Screenshots/Logs/Monitoring]

| username: zhanggame1 | Original post link

Try setting AUTO_ID_CACHE to 1. In higher versions of TiDB, it can increment globally without significantly affecting performance.

| username: Vincent_Wang | Original post link

This cannot be changed online, it has to be an empty table, right?

The current phenomenon is that the hidden column _tidb_rowid of NONCLUSTERED and the auto-increment of ID are using the same auto-increment sequence.

| username: 随缘天空 | Original post link

It should be an issue with the auto-increment step size. Try executing the following SQL statement: ALTER TABLE table_name AUTO_INCREMENT_INCREMENT = 1; However, this is likely session-level. If you want it to be global, it’s best to change the configuration file and restart.

| username: Vincent_Wang | Original post link

It has nothing to do with the step size.
Looking at the data pattern, the id and the hidden column _tidb_rowid use the same auto-increment sequence, so odd numbers are in id and even numbers are in _tidb_rowid.

| username: 随缘天空 | Original post link

Yes, the step size is 1, but it is assigned to the hidden ID and the ID you set yourself by default. However, it is better not to set it to auto-increment, as it can easily lead to hotspot issues.

| username: Vincent_Wang | Original post link

The code hasn’t changed, there was no such issue in 5.4.3, and it doesn’t match the official description. Could this be a bug in 6.5.5?

| username: zhanggame1 | Original post link

The document refers to clustered tables, while yours is a non-clustered table, which is different. Clustered table primary keys do not have _tidb_rowid.

| username: Vincent_Wang | Original post link

Now discussing the issue of non-clustered tables.

| username: zhanggame1 | Original post link

According to the documentation, a single allocator is used, so inserting a row of data will increment by 2 consecutive numbers, one for the auto-increment column and one for _tidb_rowid.

| username: Vincent_Wang | Original post link

Yes, so the ID is always an odd number.

| username: 随缘天空 | Original post link

It shouldn’t be considered a bug. It’s just that in different versions, the same allocator distinguishes when allocating auto-increment data. In one version, both values generated by a single piece of data are given to the auto-increment column, while in the other version, one value is given to the auto-increment column and the other to the rowid. There’s no need to be too concerned about this. When designing primary keys, try to avoid using auto-increment.

| username: 芮芮是产品 | Original post link

It’s not a bug.

| username: 芮芮是产品 | Original post link

You should have two TiDBs, and the reason you connected to one of them.

| username: Kongdom | Original post link

Then it should be normal. Both are sharing one allocator.

| username: dba远航 | Original post link

Try increasing the AUTO_INCREMENT_OFFSET.

| username: zhanggame1 | Original post link

Use clustered tables once and for all.

| username: andone | Original post link

Issue with setting auto-increment step size