lighting: local backend import failed if data has largest number as bigint auto_increment column

[fubinzh]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

1. Prepare test data, with biggest number (9223372036854775807) as bigint auto_increment column.
   [root@idc-4-30 ~]# cat /minio/tools_test_data/PK_AUTO_INCREMENT9096/new_data1.PK_AUTO_INCREMENT9096.000000000.csv
   "COL1","COL2","COL4","COL3","COL5"
   1,"鐚傆Ē榉牖纜径鉾抍镒获肾É鷓开纻緖扻鏶囮","8431-02-26 15:01:24",5737257244447821569,-1.20365e38
   9223372036854775807,"多鵾嚟咕勓鞒轊嵵呮觧栽榨娅猋荪桍舉宰籕僸","0032-10-07 22:42:25",-6700431522805297345,-2.51818e38

[root@idc-4-30 ~]# cat /minio/tools_test_data/PK_AUTO_INCREMENT9096/new_data1.PK_AUTO_INCREMENT9096-schema.sql
/!40101 SET NAMES binary/;
CREATE TABLE PK_AUTO_INCREMENT9096 (
COL1 bigint(20) NOT NULL AUTO_INCREMENT,
COL2 varchar(20) DEFAULT NULL,
COL4 datetime DEFAULT NULL,
COL3 bigint(20) DEFAULT NULL,
COL5 float DEFAULT NULL,
PRIMARY KEY (COL1) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

2, Import the data with local backend
[root@idc-4-30 ~]# tiup tidb-lightning --tidb-host=172.16.4.30 --tidb-port=4000 --check-requirements=false --backend=local --sorted-kv-dir=/home/sorted-kvs -d 's3://nfs/tools_test_data/PK_AUTO_INCREMENT9096/?access-key=minioadmin&secret-access-key=minioadmin&endpoint=http%3a%2f%2fminio.pingcap.net%3a9000&force-path-style=true' -config lightning_min.toml -log-file PK_AUTO_INCREMENT9096.log.6
Starting component tidb-lightning: /root/.tiup/components/tidb-lightning/v5.2.0/tidb-lightning --tidb-host=172.16.4.30 --tidb-port=4000 --check-requirements=false --backend=local --sorted-kv-dir=/home/sorted-kvs -d s3://nfs/tools_test_data/PK_AUTO_INCREMENT9096/?access-key=minioadmin&secret-access-key=minioadmin&endpoint=http%3a%2f%2fminio.pingcap.net%3a9000&force-path-style=true -config lightning_min.toml -log-file PK_AUTO_INCREMENT9096.log.6
Verbose debug logs will be written to PK_AUTO_INCREMENT9096.log.6
...
Error: restore table new_data1.PK_AUTO_INCREMENT9096 failed: ALTER TABLE new_data1.PK_AUTO_INCREMENT9096 AUTO_INCREMENT=-9223372036854775808: alter table auto_increment failed: Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use line 1 column 64 near "-9223372036854775808"
tidb lightning encountered error: restore table new_data1.PK_AUTO_INCREMENT9096 failed: ALTER TABLE new_data1.PK_AUTO_INCREMENT9096 AUTO_INCREMENT=-9223372036854775808: alter table auto_increment failed: Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use line 1 column 64 near "-9223372036854775808"

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

Import succeed

3. What did you see instead (Required)

Import failed with error: ALTER TABLE new_data1.PK_AUTO_INCREMENT9096 AUTO_INCREMENT=-9223372036854775808

Note: import is OK for tidb backend.

4. What is your TiDB version? (Required)

TiDB: v5.0.0-nightly-20210908
[root@idc-4-30 ~]# tiup tidb-lightning -V
Starting component tidb-lightning: /root/.tiup/components/tidb-lightning/v5.2.0/tidb-lightning -V
Release Version: v5.2.0
Git Commit Hash: 05d2210
Git Branch: heads/refs/tags/v5.2.0
Go Version: go1.16.4
UTC Build Time: 2021-08-27 05:56:11
Race Enabled: false

[ethercflow]

@fubinzh There are garbled characters in the description of your reproduction steps :)

[fubinzh]

@fubinzh There are garbled characters in the description of your reproduction steps :)

Sorry, missed your comments somehow, the garbled characters doesn't impact the reproduction of the issue, it was auto generated, you can replace it. @ethercflow

[glorv]

@kennytm What do you think of this issue? As when tidb reach the maximum auto_increment value the auto increment counter will overflow and any other insert statement with implicit auto_increment column will result in ERROR 1467 (HY000): Failed to read auto-increment value from storage engine, it's hard to do the same thing with SQL interface.

What about when we find the auto_increment value is to overflow, we just set it as the maximum value and output a warn log. Then any other insert statement will first result in a duplicate key error and then Failed to read auto-increment value from storage engine for any other request.

[kennytm]

@glorv LGTM. But I think ensuring the input is an unsigned number (uint64) rather than signed (int64) is sufficient, since the error here is a Syntax Error.

[niubell]

/assign buchuitoudegou