bug: Setting AUTO_INCREMENT starting value does not work

[davidshiz]

Have you read the Contributing Guidelines on issues?

• I have read the Contributing Guidelines on issues.

Please confirm if bug report does NOT exists already ?

• I confirm there is no existing issue for this

Describe the problem

We can change the starting value for the AUTO_INCREMENT field if you don't want to start at 1.
But setting AUTO_INCREMENT starting value does not work in Tianmu engine.

mysql> CREATE TABLE st8 (
    -> id INT NOT NULL AUTO_INCREMENT,
    -> name VARCHAR(20) NOT NULL,
    -> PRIMARY KEY(ID)
    -> )AUTO_INCREMENT=100;
Query OK, 0 rows affected (0.04 sec)

mysql> select * from st8;
Empty set (0.02 sec)

mysql> INSERT INTO st8 (name)VALUES('Java');
Query OK, 1 row affected (0.00 sec)

mysql> select * from st8;
+----+------+
| id | name |
+----+------+
|  1 | Java |
+----+------+

Expected behavior

mysql> CREATE TABLE st8 (
    -> id INT NOT NULL AUTO_INCREMENT,
    -> name VARCHAR(20) NOT NULL,
    -> PRIMARY KEY(ID)
    -> )AUTO_INCREMENT=100;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from st8;
Empty set (0.00 sec)

mysql> INSERT INTO st8 (name)VALUES('Java');
Query OK, 1 row affected (0.00 sec)

mysql> select * from st8;
+-----+------+
| id  | name |
+-----+------+
| 100 | Java |
+-----+------+
1 row in set (0.00 sec)

How To Reproduce

CREATE TABLE st8 (	
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
PRIMARY KEY(ID)
)AUTO_INCREMENT=100;

INSERT INTO st8 (name)VALUES('Java');

select * from st8;

Environment

[root@HAST04 ~]# /stonedb57/install/bin/mysqld --version
/stonedb57/install/bin/mysqld  Ver 5.7.36-StoneDB for Linux on x86_64 (build-)
build information as follow:
        Repository address: https://github.com/stoneatom/stonedb.git:stonedb-5.7-dev
        Branch name: stonedb-5.7-dev
        Last commit ID: 7f69d33
        Last commit time: Date:   Tue Nov 22 06:31:04 2022 +0000
        Build time: Date: Tue Nov 22 16:53:27 CST 2022
[root@HAST04 ~]# cat /etc/system-release
CentOS Linux release 7.9.2009 (Core)

Are you interested in submitting a PR to solve the problem?

• Yes, I will!

[isredstar]

ACK

[davidshiz]

The issue is not fixed @wisehead

[haitaoguan]

In tianmu engine, the table structure will not display keyword "AUTO_INCREMENT".

[haitaoguan]

If you set auto increment, the table structure will display keyword "AUTO_INCREMENT". Otherwise,you won't know what value to take next time.

[wisehead]

@davidshiz please verify it, @isredstar already delivered the fix.