Br restore failed with the error multiple statements internally is not supported

[lilinghai]

Please answer these questions before submitting your issue. Thanks!

1. What did you do?
   If possible, provide a recipe for reproducing the error.
   tiup br:nightly restore db --db ddl1 xxx

2. What did you expect to see?
   successfully

3. What did you see instead?
   failed to restore with the log

[2021/05/13 10:04:00.883 +00:00] [ERROR] [restore.go:35] ["failed to restore"] [error="[parser:1064]You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use run multiple statements internally is not supported"] [errorVerbose="[parser:1064]You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use run multiple statements internally is not supported\ngitpro.ttaallkk.top/pingcap/errors.AddStack\n\tgitpro.ttaallkk.top/pingcap/errors@v0.11.5-0.20201126102027-b0a155152ca3/errors.go:174\ngitpro.ttaallkk.top/pingcap/errors.(*Error).GenWithStackByArgs\n\tgitpro.ttaallkk.top/pingcap/errors@v0.11.5-0.20201126102027-b0a155152ca3/normalize.go:156\ngitpro.ttaallkk.top/pingcap/tidb/util.SyntaxError\n\tgitpro.ttaallkk.top/pingcap/tidb@v1.1.0-beta.0.20210419040752-76ba3c84acbc/util/misc.go:156\ngitpro.ttaallkk.top/pingcap/tidb/session.(*session).ParseWithParams\n\tgitpro.ttaallkk.top/pingcap/tidb@v1.1.0-beta.0.20210419040752-76ba3c84acbc/session/session.go:1278\ngitpro.ttaallkk.top/pingcap/tidb/session.(*session).ExecuteInternal\n\tgitpro.ttaallkk.top/pingcap/tidb@v1.1.0-beta.0.20210419040752-76ba3c84acbc/session/session.go:1160\ngitpro.ttaallkk.top/pingcap/br/pkg/gluetidb.(*tidbSession).Execute\n\tgitpro.ttaallkk.top/pingcap/br@/pkg/gluetidb/glue.go:108\ngitpro.ttaallkk.top/pingcap/br/pkg/restore.(*DB).CreateTable\n\tgitpro.ttaallkk.top/pingcap/br@/pkg/restore/db.go:168\ngitpro.ttaallkk.top/pingcap/br/pkg/restore.(*Client).createTable\n\tgitpro.ttaallkk.top/pingcap/br@/pkg/restore/client.go:398\ngitpro.ttaallkk.top/pingcap/br/pkg/restore.(*Client).GoCreateTables.func1\n\tgitpro.ttaallkk.top/pingcap/br@/pkg/restore/client.go:450\ngitpro.ttaallkk.top/pingcap/br/pkg/restore.(*Client).createTablesWithDBPool.func1\n\tgitpro.ttaallkk.top/pingcap/br@/pkg/restore/client.go:501\ngitpro.ttaallkk.top/pingcap/br/pkg/utils.(*WorkerPool).ApplyWithIDInErrorGroup.func1\n\tgitpro.ttaallkk.top/pingcap/br@/pkg/utils/worker.go:72\ngolang.org/x/sync/errgroup.(*Group).Go.func1\n\tgolang.org/x/sync@v0.0.0-20201020160332-67f06af15bc9/errgroup/errgroup.go:57\nruntime.goexit\n\truntime/asm_amd64.s:1357"] [stack="main.runRestoreCommand\n\tgitpro.ttaallkk.top/pingcap/br@/cmd/br/restore.go:35\nmain.newDBRestoreCommand.func1\n\tgitpro.ttaallkk.top/pingcap/br@/cmd/br/restore.go:133\ngitpro.ttaallkk.top/spf13/cobra.(*Command).execute\n\tgitpro.ttaallkk.top/spf13/cobra@v1.0.0/command.go:842\ngitpro.ttaallkk.top/spf13/cobra.(*Command).ExecuteC\n\tgitpro.ttaallkk.top/spf13/cobra@v1.0.0/command.go:950\ngitpro.ttaallkk.top/spf13/cobra.(*Command).Execute\n\tgitpro.ttaallkk.top/spf13/cobra@v1.0.0/command.go:887\nmain.main\n\tgitpro.ttaallkk.top/pingcap/br@/cmd/br/main.go:56\nruntime.main\n\truntime/proc.go:203"]
[2021/05/13 10:04:00.883 +00:00] [ERROR] [main.go:58] ["br failed"] [error="[parser:1064]You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use run multiple statements internally is not supported"] [errorVerbose="[parser:1064]You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use run multiple statements internally is not supported\ngitpro.ttaallkk.top/pingcap/errors.AddStack\n\tgitpro.ttaallkk.top/pingcap/errors@v0.11.5-0.20201126102027-b0a155152ca3/errors.go:174\ngitpro.ttaallkk.top/pingcap/errors.(*Error).GenWithStackByArgs\n\tgitpro.ttaallkk.top/pingcap/errors@v0.11.5-0.20201126102027-b0a155152ca3/normalize.go:156\ngitpro.ttaallkk.top/pingcap/tidb/util.SyntaxError\n\tgitpro.ttaallkk.top/pingcap/tidb@v1.1.0-beta.0.20210419040752-76ba3c84acbc/util/misc.go:156\ngitpro.ttaallkk.top/pingcap/tidb/session.(*session).ParseWithParams\n\tgitpro.ttaallkk.top/pingcap/tidb@v1.1.0-beta.0.20210419040752-76ba3c84acbc/session/session.go:1278\ngitpro.ttaallkk.top/pingcap/tidb/session.(*session).ExecuteInternal\n\tgitpro.ttaallkk.top/pingcap/tidb@v1.1.0-beta.0.20210419040752-76ba3c84acbc/session/session.go:1160\ngitpro.ttaallkk.top/pingcap/br/pkg/gluetidb.(*tidbSession).Execute\n\tgitpro.ttaallkk.top/pingcap/br@/pkg/gluetidb/glue.go:108\ngitpro.ttaallkk.top/pingcap/br/pkg/restore.(*DB).CreateTable\n\tgitpro.ttaallkk.top/pingcap/br@/pkg/restore/db.go:168\ngitpro.ttaallkk.top/pingcap/br/pkg/restore.(*Client).createTable\n\tgitpro.ttaallkk.top/pingcap/br@/pkg/restore/client.go:398\ngitpro.ttaallkk.top/pingcap/br/pkg/restore.(*Client).GoCreateTables.func1\n\tgitpro.ttaallkk.top/pingcap/br@/pkg/restore/client.go:450\ngitpro.ttaallkk.top/pingcap/br/pkg/restore.(*Client).createTablesWithDBPool.func1\n\tgitpro.ttaallkk.top/pingcap/br@/pkg/restore/client.go:501\ngitpro.ttaallkk.top/pingcap/br/pkg/utils.(*WorkerPool).ApplyWithIDInErrorGroup.func1\n\tgitpro.ttaallkk.top/pingcap/br@/pkg/utils/worker.go:72\ngolang.org/x/sync/errgroup.(*Group).Go.func1\n\tgolang.org/x/sync@v0.0.0-20201020160332-67f06af15bc9/errgroup/errgroup.go:57\nruntime.goexit\n\truntime/asm_amd64.s:1357"] [stack="main.main\n\tgitpro.ttaallkk.top/pingcap/br@/cmd/br/main.go:58\nruntime.main\n\truntime/proc.go:203"]

4. What version of BR and TiDB/TiKV/PD are you using?
   The cluster is master nightly version
   tiup br:nightly -V
   Starting component br: /home/llh/.tiup/components/br/v5.0.0-nightly-20210511/br -V
   Release Version: v5.0.0-master
   Git Commit Hash: 7c98ff0
   Git Branch: master
   Go Version: go1.13
   UTC Build Time: 2021-05-10 08:45:15
   Race Enabled: false

5. Operation logs

   • Please upload br.log for BR if possible
   • Please upload tidb-lightning.log for TiDB-Lightning if possible
   • Please upload tikv-importer.log from TiKV-Importer if possible
   • Other interesting logs

6. Configuration of the cluster and the task

   • tidb-lightning.toml for TiDB-Lightning if possible
   • tikv-importer.toml for TiKV-Importer if possible
   • topology.yml if deployed by TiUP

7. Screenshot/exported-PDF of Grafana dashboard or metrics' graph in Prometheus if possible

[lilinghai]

@cyliu0 @3pointer can call me the br store if needed.

[ZipFast]

This issue is similar to #951, it's a boundary issue that br restore cannot handle properly with an auto_increment table. The related pr had proposed #458 .

The corresponding restore error log is as following:

[2021/05/27 12:54:58.972 +08:00] [ERROR] [db.go:173] ["restore meta sql failed"] [query="alter table `ddl1`.`IDT_23881` auto_increment = -9223372036854775808;"] [db=ddl1] [table=IDT_23881] [error="[parser:1064]You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use run multiple statements internally is not supported"] [errorVerbose="[parser:1064]You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use run multiple statements internally is not supported\ngitpro.ttaallkk.top/pingcap/errors.AddStack\n\tgitpro.ttaallkk.top/pingcap/errors@v0.11.5-0.20201126102027-b0a155152ca3/errors.go:174\ngitpro.ttaallkk.top/pingcap/errors.(*Error).GenWithStackByArgs\n\tgitpro.ttaallkk.top/pingcap/errors@v0.11.5-0.20201126102027-b0a155152ca3/normalize.go:156\ngitpro.ttaallkk.top/pingcap/tidb/util.SyntaxError\n\tgitpro.ttaallkk.top/pingcap/tidb@v1.1.0-beta.0.20210513061339-f2c2fbda476f/util/misc.go:156\ngitpro.ttaallkk.top/pingcap/tidb/session.(*session).ParseWithParams\n\tgitpro.ttaallkk.top/pingcap/tidb@v1.1.0-beta.0.20210513061339-f2c2fbda476f/session/session.go:1349\ngitpro.ttaallkk.top/pingcap/tidb/session.(*session).ExecuteInternal\n\tgitpro.ttaallkk.top/pingcap/tidb@v1.1.0-beta.0.20210513061339-f2c2fbda476f/session/session.go:1231\ngitpro.ttaallkk.top/pingcap/br/pkg/gluetidb.(*tidbSession).Execute\n\tgitpro.ttaallkk.top/pingcap/br/pkg/gluetidb/glue.go:108\ngitpro.ttaallkk.top/pingcap/br/pkg/restore.(*DB).CreateTable\n\tgitpro.ttaallkk.top/pingcap/br/pkg/restore/db.go:168\ngitpro.ttaallkk.top/pingcap/br/pkg/restore.(*Client).createTable\n\tgitpro.ttaallkk.top/pingcap/br/pkg/restore/client.go:398\ngitpro.ttaallkk.top/pingcap/br/pkg/restore.(*Client).GoCreateTables.func1\n\tgitpro.ttaallkk.top/pingcap/br/pkg/restore/client.go:450\ngitpro.ttaallkk.top/pingcap/br/pkg/restore.(*Client).createTablesWithDBPool.func1\n\tgitpro.ttaallkk.top/pingcap/br/pkg/restore/client.go:502\ngitpro.ttaallkk.top/pingcap/br/pkg/utils.(*WorkerPool).ApplyWithIDInErrorGroup.func1\n\tgitpro.ttaallkk.top/pingcap/br/pkg/utils/worker.go:77\ngolang.org/x/sync/errgroup.(*Group).Go.func1\n\tgolang.org/x/sync@v0.0.0-20201020160332-67f06af15bc9/errgroup/errgroup.go:57\nruntime.goexit\n\truntime/asm_amd64.s:1371"] [stack="github.com/pingcap/br/pkg/restore.(*DB).CreateTable\n\tgitpro.ttaallkk.top/pingcap/br/pkg/restore/db.go:173\ngitpro.ttaallkk.top/pingcap/br/pkg/restore.(*Client).createTable\n\tgitpro.ttaallkk.top/pingcap/br/pkg/restore/client.go:398\ngitpro.ttaallkk.top/pingcap/br/pkg/restore.(*Client).GoCreateTables.func1\n\tgitpro.ttaallkk.top/pingcap/br/pkg/restore/client.go:450\ngitpro.ttaallkk.top/pingcap/br/pkg/restore.(*Client).createTablesWithDBPool.func1\n\tgitpro.ttaallkk.top/pingcap/br/pkg/restore/client.go:502\ngitpro.ttaallkk.top/pingcap/br/pkg/utils.(*WorkerPool).ApplyWithIDInErrorGroup.func1\n\tgitpro.ttaallkk.top/pingcap/br/pkg/utils/worker.go:77\ngolang.org/x/sync/errgroup.(*Group).Go.func1\n\tgolang.org/x/sync@v0.0.0-20201020160332-67f06af15bc9/errgroup/errgroup.go:57"]