Error Setting Auto-Increment Value During Lightning Local Import

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

Original topic: ligthning local导入 设置自增值报错

| username: h5n1

When using Local mode to import data (10 tables with 10 million rows each initialized by sysbench), an error occurs at the final stage when setting the auto-increment value, indicating that the table does not exist. Lightning exits after reporting the error, and upon checking, the database indeed does not contain the tables. It seems that the schema information is not fully established before executing ALTER TABLE AUTO_INCREMENT.

[2023/12/22 14:57:16.104 +08:00] [INFO] [tidb.go:317] ["alter table auto_increment start"] [table=`sbtest`.`sbtest2`] [auto_increment=10132488]
[2023/12/22 14:57:16.105 +08:00] [WARN] [util.go:197] ["alter table auto_increment failed with no retry"] [table=`sbtest`.`sbtest2`] [auto_increment=10132488] [query="ALTER TABLE `sbtest`.`sbtest2`  AUTO_INCREMENT=10132488"] [args=null] [retryCnt=0] [error="Error 1146 (42S02): Table 'sbtest.sbtest2' doesn't exist"]
[2023/12/22 14:57:16.105 +08:00] [ERROR] [tidb.go:320] ["alter table auto_increment failed"] [table=`sbtest`.`sbtest2`] [auto_increment=10132488] [takeTime=649.124µs] [error="alter table auto_increment failed: Error 1146 (42S02): Table 'sbtest.sbtest2' doesn't exist"]
[2023/12/22 14:57:16.105 +08:00] [ERROR] [tidb.go:322] ["alter table auto_increment failed, please perform the query manually (this is needed no matter the table has an auto-increment column or not)"] [table=`sbtest`.`sbtest2`] [auto_increment=10132488] [query="ALTER TABLE `sbtest`.`sbtest2`  AUTO_INCREMENT=10132488"]
[2023/12/22 14:57:16.105 +08:00] [INFO] [tidb.go:317] ["alter table auto_increment start"] [table=`sbtest`.`sbtest1`] [auto_increment=10144716]
[2023/12/22 14:57:16.105 +08:00] [ERROR] [import.go:1672] ["failed to import table"] [table=`sbtest`.`sbtest2`] [error="ALTER TABLE `sbtest`.`sbtest2`  AUTO_INCREMENT=10132488: alter table auto_increment failed: Error 1146 (42S02): Table 'sbtest.sbtest2' doesn't exist"] [errorVerbose="Error 1146 (42S02): Table 'sbtest.sbtest2' doesn't exist\ngithub.com/pingcap/errors.AddStack\n\t/go/pkg/mod/github.com/pingcap/errors@v0.11.5-0.20221009092201-b66cddb77c32/errors.go:174\ngithub.com/pingcap/errors.Trace\n\t/go/pkg/mod/github.com/pingcap/errors@v0.11.5-0.20221009092201-b66cddb77c32/juju_adaptor.go:15\ngithub.com/pingcap/tidb/br/pkg/lightning/common.SQLWithRetry.Exec.func1\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/pkg/lightning/common/util.go:290\ngithub.com/pingcap/tidb/br/pkg/lightning/common.Retry\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/pkg/lightning/common/util.go:186\ngithub.com/pingcap/tidb/br/pkg/lightning/common.SQLWithRetry.perform\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/pkg/lightning/common/util.go:171\ngithub.com/pingcap/tidb/br/pkg/lightning/common.SQLWithRetry.Exec\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/pkg/lightning/common/util.go:288\ngithub.com/pingcap/tidb/br/pkg/lightning/importer.AlterAutoIncrement\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/pkg/lightning/importer/tidb.go:319\ngithub.com/pingcap/tidb/br/pkg/lightning/importer.(*TableImporter).postProcess\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/pkg/lightning/importer/table_import.go:913\ngithub.com/pingcap/tidb/br/pkg/lightning/importer.(*TableImporter).importTable\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/pkg/lightning/importer/table_import.go:218\ngithub.com/pingcap/tidb/br/pkg/lightning/importer.(*Controller).importTables.func6\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/pkg/lightning/importer/import.go:1670\nruntime.goexit\n\t/usr/local/go/src/runtime/asm_arm64.s:1172\nalter table auto_increment failed\nALTER TABLE `sbtest`.`sbtest2`  AUTO_INCREMENT=10132488"]
[2023/12/22 14:57:16.106 +08:00] [ERROR] [import.go:1676] ["restore table failed"] [table=`sbtest`.`sbtest2`] [takeTime=47.586969605s] [error="[Lightning:Restore:ErrRestoreTable]restore table `sbtest`.`sbtest2` failed: ALTER TABLE `sbtest`.`sbtest2`  AUTO_INCREMENT=10132488: alter table auto_increment failed: Error 1146 (42S02): Table 'sbtest.sbtest2' doesn't exist"]
[2023/12/22 14:57:16.106 +08:00] [WARN] [util.go:197] ["alter table auto_increment failed with no retry"] [table=`sbtest`.`sbtest1`] [auto_increment=10144716] [query="ALTER TABLE `sbtest`.`sbtest1`  AUTO_INCREMENT=10144716"] [args=null] [retryCnt=0] [error="Error 1146 (42S02): Table 'sbtest.sbtest1' doesn't exist"]
[2023/12/22 14:57:16.106 +08:00] [ERROR] [tidb.go:320] ["alter table auto_increment failed"] [table=`sbtest`.`sbtest1`] [auto_increment=10144716] [takeTime=319.292µs] [error="alter table auto_increment failed: Error 1146 (42S02): Table 'sbtest.sbtest1' doesn't exist"]
[2023/12/22 14:57:16.106 +08:00] [ERROR] [tidb.go:322] ["alter table auto_increment failed, please perform the query manually (this is needed no matter the table has an auto-increment column or not)"] [table=`sbtest`.`sbtest1`] [auto_increment=10144716] [query="ALTER TABLE `sbtest`.`sbtest1`  AUTO_INCREMENT=10144716"]
[2023/12/22 14:57:16.106 +08:00] [ERROR] [import.go:1672] ["failed to import table"] [table=`sbtest`.`sbtest1`] [error="ALTER TABLE `sbtest`.`sbtest1`  AUTO_INCREMENT=10144716: alter table auto_increment failed: Error 1146 (42S02): Table 'sbtest.sbtest1' doesn't exist"] [errorVerbose="Error 1146 (42S02): Table 'sbtest.sbtest1' doesn't exist\ngithub.com/pingcap/errors.AddStack\n\t/go/pkg/mod/github.com/pingcap/errors@v0.11.5-0.20221009092201-b66cddb77c32/errors.go:174\ngithub.com/pingcap/errors.Trace\n\t/go/pkg/mod/github.com/pingcap/errors@v0.11.5-0.20221009092201-b66cddb77c32/juju_adaptor.go:15\ngithub.com/pingcap/tidb/br/pkg/lightning/common.SQLWithRetry.Exec.func1\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/pkg/lightning/common/util.go:290\ngithub.com/pingcap/tidb/br/pkg/lightning/common.Retry\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/pkg/lightning/common/util.go:186\ngithub.com/pingcap/tidb/br/pkg/lightning/common.SQLWithRetry.perform\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/pkg/lightning/common/util.go:171\ngithub.com/pingcap/tidb/br/pkg/lightning/common.SQLWithRetry.Exec\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/pkg/lightning/common/util.go:288\ngithub.com/pingcap/tidb/br/pkg/lightning/importer.AlterAutoIncrement\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/pkg/lightning/importer/tidb.go:319\ngithub.com/pingcap/tidb/br/pkg/lightning/importer.(*TableImporter).postProcess\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/pkg/lightning/importer/table_import.go:913\ngithub.com/pingcap/tidb/br/pkg/lightning/importer.(*TableImporter).importTable\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/pkg/lightning/importer/table_import.go:218\ngithub.com/pingcap/tidb/br/pkg/lightning/importer.(*Controller).importTables.func6\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/pkg/lightning/importer/import.go:1670\nruntime.goexit\n\t/usr/local/go/src/runtime/asm_arm64.s:1172\nalter table auto_increment failed\nALTER TABLE `sbtest`.`sbtest1`  AUTO_INCREMENT=10144716"]
[2023/12/22 14:57:16.106 +08:00] [ERROR] [import.go:1676] ["restore table failed"] [table=`sbtest`.`sbtest1`] [takeTime=47.586598214s] [error="[Lightning:Restore:ErrRestoreTable]restore table `sbtest`.`sbtest1` failed: ALTER TABLE `sbtest`.`sbtest1`  AUTO_INCREMENT=10144716: alter table auto_increment failed: Error 1146 (42S02): Table 'sbtest.sbtest1' doesn't exist"]
[2023/12/22 14:57:16.106 +08:00] [INFO] [import.go:1385] ["cancel periodic actions"] [do=true]
[2023/12/22 14:57:16.106 +08:00] [INFO] [import.go:1990] ["switch import mode"] [mode=Normal]
[2023/12/22 14:57:16.373 +08:00] [ERROR] [import.go:1633] ["restore all tables data failed"] [takeTime=3m58.638539339s] [error="[Lightning:Restore:ErrRestoreTable]restore table `sbtest`.`sbtest4` failed: ALTER TABLE `sbtest`.`sbtest4`  AUTO_INCREMENT=10104593: alter table auto_increment failed: Error 1146 (42S02): Table 'sbtest.sbtest4' doesn't exist"]
| username: h5n1 | Original post link

Delete the target database

Import error

Check DDL only for database creation

TiDB log
tidb.log (24.1 KB)

tidb_owner.log
tidb_owner.log (8.5 KB)

Found [“TiKV Min Version”=6.2.0-alpha] in TiDB startup log

[2023/12/22 10:24:11.552 +08:00] [INFO] [printer.go:48] ["Welcome to TiDB."] ["Release Version"=v7.1.3] [Edition=Community] ["Git Commit Hash"=aab2da047904901725e5a3bf745b47b1488eb8c7] ["Git Branch"=heads/refs/tags/v7.1.3] ["UTC Build Time"="2023-12-18 14:56:09"] [GoVersion=go1.20.12] ["Race Enabled"=false] ["Check Table Before Drop"=false] ["TiKV Min Version"=6.2.0-alpha]

Checked another 7.5 startup without TiKV Min Version information

Using configuration file method also reports an error

| username: WalterWj | Original post link

Is it because lightning is configured with no-schema?

| username: aytrack | Original post link

Could you please provide the complete lightning log? It’s a bit strange that there is no CREATE table DDL here.

| username: h5n1 | Original post link

lightning.log.2023-12-22T20.45.54.log (649.5 KB)