Hit ERROR 1467 when insert into the max unsigned bigint values(18446744073709551615) with tidb_enable_clustered_index=off

[mayjiang0203]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

MySQL [test]> set tidb_enable_clustered_index=off;
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> create table t(id bigint unsigned auto_increment primary key);
ERROR 8027 (HY000): Information schema is out of date: schema failed to update in 1 lease, please make sure TiDB can connect to TiKV
MySQL [test]> create table t(id bigint unsigned auto_increment primary key);
Query OK, 0 rows affected (0.58 sec)

MySQL [test]> insert into t values(18446744073709551615);
ERROR 1467 (HY000): Failed to read auto-increment value from storage engine
MySQL [test]> set tidb_enable_clustered_index=on;
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> create table t1(id bigint unsigned auto_increment primary key);
Query OK, 0 rows affected (0.11 sec)

MySQL [test]> insert into t1 values(18446744073709551614);
Query OK, 1 row affected (0.01 sec)

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

3. What did you see instead (Required)

4. What is your TiDB version? (Required)

v8.5.0

[tiancaiamao]

mysql> insert into t values (1);
ERROR 1467 (HY000): Failed to read auto-increment value from storage engine

Even insert 1 result in the same error, there must be something wrong

[mayjiang0203]

/assign @tiancaiamao

[bb7133]

I think this is expected: #982 due to the allocation of both row id and auto id, so it don't have to be fixed.

It can be resolved with AUTO_ID_CACHE=1: #39041.

[tiancaiamao]

I think this is expected: #982 due to the allocation of both row id and auto id, so it don't have to be fixed.

It can be resolved with AUTO_ID_CACHE=1: #39041.

No, this is not the same issue @bb7133
The error is throw in this line

tidb/pkg/meta/autoid/autoid.go

Lines 978 to 980 in b71ad38

	// Condition alloc.base+n1 > alloc.end will overflow when alloc.base + n1 > MaxInt64. So need this.
	if math.MaxUint64-uint64(alloc.base) <= uint64(n1) {
	return 0, 0, ErrAutoincReadFailed

It says when the alloc.base is 18446744073709551615 already, alloc.base + 1 > math.MaxUint64 , this would cause overflow, so the error is thrown

There must be something wrong, the maxinum value we can insert successfully is 18446744073709551613, this behavior is not expected.

[tiancaiamao]

This is not the default setting, and it's unlikely for 18446744073709551615 to appear in a real use case, so I set the severity to minor.

[tiancaiamao]

I found it's difficult to support 18446744073709551615 using current representation.
The to-be -allocated id is in range [start, end]
If current is [18446744073709551614, 18446744073709551615], ok, the next allocate get 18446744073709551614
and the new range become [18446744073709551615, 18446744073709551615]

How to represent the next allocation? let 18446744073709551615 overflow is risky, that's the biggest value for the range already.

[tiancaiamao]

I think this is expected: #982 due to the allocation of both row id and auto id, so it don't have to be fixed.

It can be resolved with AUTO_ID_CACHE=1: #39041.

OK, after taking a second look, @bb7133 is correct.

When we don't seperate auto id and row id, each time allocating a new record consumes two ids.
The rowid need one and auto id need another one.

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

mysql> select _tidb_rowid, id from t;
+-------------+-----+
| _tidb_rowid | id  |
+-------------+-----+
|         124 | 123 |
+-------------+-----+
1 row in set (0.00 sec)

When we insert 123, the id is 123 and row id is 124 ...

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

mysql> select _tidb_rowid, id from t;
+-------------+----------------------+
| _tidb_rowid | id                   |
+-------------+----------------------+
|         124 |                  123 |
|          -1 | 18446744073709551614 |
+-------------+----------------------+
2 rows in set (0.00 sec)

Here the -1 is indeed 18446744073709551615

If we want to insert into t values(18446744073709551614 );
That should result in

mysql> select _tidb_rowid, id from t;
+-------------+----------------------+
| _tidb_rowid | id                   |
+-------------+----------------------+
|          18446744073709551616 | 18446744073709551615 |
+-------------+----------------------+
2 rows in set (0.00 sec)

And as you can see, _tidb_rowid = 18446744073709551616 overflows, so it should fail.