Unexpected side-effects of setting auto_increment

[buchuitoudegou]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

create table test1(
 a bigint(20) auto_increment,
 b int,
 primary key(a));

alter table test1 auto_increment=9223372036854775800;

insert into test1(b) values(1);

insert into test1(b) values(2);

insert into test1(b) values(3);

alter table test1 auto_increment=9223372036854775807;

alter table test1 auto_increment=9223372036854775803;

insert into test1(b) values(4);

2. What did you expect to see? (Required)

Successfully inserting (9223372036854775803, 4)

Same sql done in MySQL:

mysql> select * from test1;
+---------------------+------+
| a                   | b    |
+---------------------+------+
| 9223372036854775800 |    1 |
| 9223372036854775801 |    2 |
| 9223372036854775802 |    3 |
| 9223372036854775803 |    4 |
| 9223372036854775804 |    5 |
| 9223372036854775805 |    6 |
+---------------------+------+

3. What did you see instead (Required)

tidb reports error:

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

Additionally, 9223372036854775807 is within the range of BIGINT and thus, TiDB has no reason to report an error.

4. What is your TiDB version? (Required)

ebdc784

[CbcWestwolf]

Some additional information: a warning would be given in TiDB but not in MySQL after alter table test1 auto_increment=9223372036854775807;:

+---------+------+------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                    |
+---------+------+------------------------------------------------------------------------------------------------------------+
| Warning | 1105 | Can't reset AUTO_INCREMENT to 9223372036854775807 without FORCE option, using -9223372036854775808 instead |
+---------+------+------------------------------------------------------------------------------------------------------------+

[buchuitoudegou]

Some additional information: a warning would be given in TiDB but not in MySQL after alter table test1 auto_increment=9223372036854775807;:

+---------+------+------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                    |
+---------+------+------------------------------------------------------------------------------------------------------------+
| Warning | 1105 | Can't reset AUTO_INCREMENT to 9223372036854775807 without FORCE option, using -9223372036854775808 instead |
+---------+------+------------------------------------------------------------------------------------------------------------+

Thanks for your help. How can I get this warning BTW? I just did this successfully in TiDB without warning:

mysql> create table test1(
    -> a bigint auto_increment,
    -> b int,
    -> primary key(a));
Query OK, 0 rows affected (0.11 sec)

mysql> alter table test1 auto_increment=9223372036854775807;
Query OK, 0 rows affected (0.12 sec)

mysql> insert into test1(b) values(1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test1;
+---------------------+------+
| a                   | b    |
+---------------------+------+
| 9223372036854775807 |    1 |
+---------------------+------+
1 row in set (0.00 sec)

[CbcWestwolf]

/assign

[tangenta]

alter table test1 auto_increment=9223372036854775807;
alter table test1 auto_increment=9223372036854775803;   -- This takes no effect!

In the current implementation, TiDB does not allow to 'ATLER' the ID to a smaller value to avoid possible duplicate IDs in a distributed environment. No error is reported as this is compatible with MySQL:

mysql> create table t (a int auto_increment primary key);
Query OK, 0 rows affected (0.06 sec)

mysql> alter table t auto_increment = 100;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into t values ();
Query OK, 1 row affected (0.02 sec)

mysql> alter table t auto_increment = 1;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into t values ();
Query OK, 1 row affected (0.02 sec)

mysql> select * from t;
+-----+
| a   |
+-----+
| 100 |
| 101 |
+-----+
2 rows in set (0.01 sec)

TiDB has no idea if the 'ALTER' ID is larger than the maximum ID in the table. Therefore, this must be achieved by the keyword 'FORCE':

alter table test1 auto_increment=9223372036854775807;         -- Oops, it is too large
alter table test1 FORCE auto_increment=9223372036854775803;   -- This takes effect!

[buchuitoudegou]

One more question:

create table test(
    a bigint auto_increment,
    b int,
    primary key(a)
);

I tried inserting values:

mysql> insert into test values(1,2),
    -> (9223372036854775806,3);
mysql> insert into test(b) values(100);
ERROR 1467 (HY000): Failed to read auto-increment value from storage engine
mysql> select * from test;
+---------------------+------+
| a                   | b    |
+---------------------+------+
|                   1 |    2 |
| 9223372036854775806 |    3 |
+---------------------+------+
2 rows in set (0.00 sec)

mysql> insert into test values(9223372036854775807, 100);
Query OK, 1 row affected (0.01 sec)

mysql> select * from test;
+---------------------+------+
| a                   | b    |
+---------------------+------+
|                   1 |    2 |
| 9223372036854775806 |    3 |
| 9223372036854775807 |  100 |
+---------------------+------+
3 rows in set (0.00 sec)

It's obvious that 9223372036854775807 is within the maximum of bigint and is also a valid key for this table. Why I can't insert a new tuple with key 9223372036854775807 by auto_increment.

[tangenta]

It's obvious that 9223372036854775807 is within the maximum of bigint and is also a valid key for this table. Why I can't insert a new tuple with key 9223372036854775807 by auto_increment.

@CbcWestwolf Could you fix this issue in your PR?

[CbcWestwolf]

@tangenta I'll have a try to fix it.