Question: AUTO_INCREMENT=100001, but the auto-increment ID still starts from 1

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

Original topic: 请教,AUTO_INCREMENT=100001, 但自增id还是从1开始

| username: Hacker_qeCXjAN2

[TiDB Usage Environment] Production Environment / Testing / PoC
[TiDB Version] 6.1.7
[Reproduction Path] What operations were performed when the issue occurred
[Encountered Issue: Problem Phenomenon and Impact]
When creating a table, AUTO_INCREMENT=100001 was specified, but the auto-increment ID still starts from 1. What is the reason for this?
[Resource Configuration] Go to TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
[Attachment: Screenshot/Log/Monitoring]

| username: Hacker_qeCXjAN2 | Original post link

This should be considered a bug, version 6.1.7 is not that old, what’s going on!

| username: changpeng75 | Original post link

AUTO_INCREMENT only sets the primary key column to have the auto-increment attribute and cannot be used for assignment. You can try the @@auto_increment_offset implicit parameter.

| username: Hacker_qeCXjAN2 | Original post link

It can be assigned.

| username: changpeng75 | Original post link

It just doesn’t report an error, but it doesn’t have any effect, right?

| username: changpeng75 | Original post link

You can refer to the documentation

| username: Hacker_qeCXjAN2 | Original post link

It works.

| username: hey-hoho | Original post link

What you need is this system variable:

| username: Hacker_qeCXjAN2 | Original post link

What should I do if each table needs to set a different starting value?

| username: Hacker_qeCXjAN2 | Original post link

What should I do if each table needs to set a different starting value?

| username: hey-hoho | Original post link

Create the table using different sessions, and set auto_increment_offset to your starting value before creating the table.

| username: Hacker_qeCXjAN2 | Original post link

I’ve never encountered this problem in versions before 5.x.

| username: hey-hoho | Original post link

I think it’s unlikely.

Do you know the specific version of your 5.x?

| username: Hacker_qeCXjAN2 | Original post link

Both the 5.0 and 5.3 environments are good.

| username: Hacker_qeCXjAN2 | Original post link

This doesn’t seem reasonable. If the developers give a batch of tables to the DBA, do they have to check each one individually when creating the tables?

| username: hey-hoho | Original post link

It’s unreasonable, but for now, this is the only way to do it. Having a method is better than having no method.
In what scenario would you need to set different starting values for each table?

| username: 数据库真NB | Original post link

Using set auto_increment_offset=initial_value, did it work?

| username: 小龙虾爱大龙虾 | Original post link

Please post your testing process, and I’ll help you analyze it. My tests didn’t show any issues.

mysql> create table t2(id bigint primary key auto_increment,name varchar(10)) AUTO_INCREMENT=10;
Query OK, 0 rows affected (0.07 sec)

mysql> insert into t2 values(default,'AA');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t2;
+----+------+
| id | name |
+----+------+
| 10 | AA   |
+----+------+
1 row in set (0.00 sec)

mysql> select tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v6.1.7
Edition: Community
Git Commit Hash: 613ecc5f731b2843e1d53a43915e2cd8da795936
Git Branch: heads/refs/tags/v6.1.7
UTC Build Time: 2023-07-04 11:55:22
GoVersion: go1.19.10
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.01 sec)

mysql> 
| username: Hacker_qeCXjAN2 | Original post link

I created a new test table later and it was fine, but there were issues with a batch of tables created earlier.

| username: 小龙虾爱大龙虾 | Original post link

So, is there still a problem? Should we check the logs?