Tidb auto analyze returns error [parser:1064]

It looks like [stats] auto analyze failed · Issue #35948 · pingcap/tidb · GitHub is the issue responsible for some of the problems here, but this doesn’t completely explain it.

The error runtime error: slice bounds out of range [-1:] is the same problem as [stats] auto analyze failed · Issue #35948 · pingcap/tidb · GitHub.

The syntax error is a different problem. Note that it may not relate to unsupported multiple statements.

The error handling of ParseWithParams is wrong and overwrites the actual syntax error to the error of run multiple statements internally is not supported.

Here is the output of two analyze in a row using MySQL client, same database, same tidb cluster, for table types it works fine, for table active - doesn’t work:

MySQL [(none)]> use masked-name;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MySQL [masked-name]> analyze table types;
Query OK, 0 rows affected, 1 warning (0.327 sec)

MySQL [masked-name]> analyze table active;
ERROR 1064 (42000): 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
MySQL [masked-name]> 

Bot tables contain some data, they’re not empty.

table types has warning ,could you show the warnings

MySQL [masked-name]> analyze table types;
Query OK, 0 rows affected, 1 warning (0.365 sec)

MySQL [masked-name]> SHOW WARNINGS;
+-------+------+-------------------------------------------------------------------------+
| Level | Code | Message                                                                 |
+-------+------+-------------------------------------------------------------------------+
| Note  | 1105 | Analyze use auto adjusted sample rate 1.000000 for table masked-name.types |
+-------+------+-------------------------------------------------------------------------+
1 row in set (0.001 sec)

You can edit your tidb config file to enable error stack, so we can get where the erroneous
internal sql is from, which can help to find the problem. @Eugen_Korentsov

Follow the steps:

  1. Add enable-error-stack = true to tidb.toml (make sure it’s under [log] table)
  2. grep tidb.log, key word is “check the manual that corresponds to your TiDB version for the right syntax to use run multiple statements internally is not supported”. Then you can get the error stack.
[2023/04/22 10:08:24.275 +00:00] [WARN] [server.go:614] ["Server.onConn handshake"] [conn=2076221000868954587] [error="read tcp 10.12.14.19:4000->10.12.14.1:33888: read: connection reset by peer"] ["remote addr"=10.12.14.1:33888]



[2023/04/22 10:08:36.819 +00:00] [INFO] [handle.go:1702] ["[stats] incrementally update modifyCount"] [tableID=874] [curModifyCnt=0] [results.BaseModifyCnt=0] [modifyCount=0]
[2023/04/22 10:08:36.819 +00:00] [INFO] [handle.go:1724] ["[stats] directly update count"] [tableID=874] [results.Count=7438] [count=7438]
[2023/04/22 10:08:36.875 +00:00] [ERROR] [analyze.go:318] ["save table stats to storage failed"] [conn=2076221000868954593] [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\ngithub.com/pingcap/errors.AddStack\n\t/go/pkg/mod/github.com/pingcap/errors@v0.11.5-0.20220729040631-518f63d66278/errors.go:174\ngithub.com/pingcap/errors.(*Error).GenWithStackByArgs\n\t/go/pkg/mod/github.com/pingcap/errors@v0.11.5-0.20220729040631-518f63d66278/normalize.go:164\ngithub.com/pingcap/tidb/util.SyntaxError\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/util/misc.go:159\ngithub.com/pingcap/tidb/session.(*session).ParseWithParams\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/session/session.go:1786\ngithub.com/pingcap/tidb/session.(*session).ExecuteInternal\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/session/session.go:1670\ngithub.com/pingcap/tidb/statistics/handle.saveTopNToStorage\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/statistics/handle/handle.go:1577\ngithub.com/pingcap/tidb/statistics/handle.SaveTableStatsToStorage\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/statistics/handle/handle.go:1757\ngithub.com/pingcap/tidb/statistics/handle.(*Handle).SaveTableStatsToStorage\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/statistics/handle/handle.go:1643\ngithub.com/pingcap/tidb/executor.(*AnalyzeExec).handleResultsError\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/analyze.go:315\ngithub.com/pingcap/tidb/executor.(*AnalyzeExec).Next\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/analyze.go:185\ngithub.com/pingcap/tidb/executor.Next\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/executor.go:328\ngithub.com/pingcap/tidb/executor.(*ExecStmt).next\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/adapter.go:1137\ngithub.com/pingcap/tidb/executor.(*ExecStmt).handleNoDelayExecutor\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/adapter.go:910\ngithub.com/pingcap/tidb/executor.(*ExecStmt).handleNoDelay\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/adapter.go:758\ngithub.com/pingcap/tidb/executor.(*ExecStmt).Exec\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/adapter.go:558\ngithub.com/pingcap/tidb/session.runStmt\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/session/session.go:2350\ngithub.com/pingcap/tidb/session.(*session).ExecuteStmt\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/session/session.go:2214\ngithub.com/pingcap/tidb/server.(*TiDBContext).ExecuteStmt\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/driver_tidb.go:252\ngithub.com/pingcap/tidb/server.(*clientConn).handleStmt\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/conn.go:2122\ngithub.com/pingcap/tidb/server.(*clientConn).handleQuery\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/conn.go:1972\ngithub.com/pingcap/tidb/server.(*clientConn).dispatch\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/conn.go:1403\ngithub.com/pingcap/tidb/server.(*clientConn).Run\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/conn.go:1152\ngithub.com/pingcap/tidb/server.(*Server).onConn\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/server.go:648\nruntime.goexit\n\t/usr/local/go/src/runtime/asm_amd64.s:1594"] [tableID=874]
[2023/04/22 10:08:36.885 +00:00] [INFO] [analyze.go:575] ["analyze table `masked-name`.`active` has failed"] [partition=] ["job info"="analyze table all columns with 256 buckets, 500 topn, 1 samplerate"] ["start time"=2023/04/22 10:08:36.736 +00:00] ["end time"=2023/04/22 10:08:36.875 +00:00] [cost=139.004031ms]
[2023/04/22 10:08:36.885 +00:00] [INFO] [tidb.go:270] ["rollbackTxn called due to ddl/autocommit failure"]
[2023/04/22 10:08:36.885 +00:00] [WARN] [session.go:2219] ["run statement failed"] [conn=2076221000868954593] [schemaVersion=1507] [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\ngithub.com/pingcap/errors.AddStack\n\t/go/pkg/mod/github.com/pingcap/errors@v0.11.5-0.20220729040631-518f63d66278/errors.go:174\ngithub.com/pingcap/errors.(*Error).GenWithStackByArgs\n\t/go/pkg/mod/github.com/pingcap/errors@v0.11.5-0.20220729040631-518f63d66278/normalize.go:164\ngithub.com/pingcap/tidb/util.SyntaxError\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/util/misc.go:159\ngithub.com/pingcap/tidb/session.(*session).ParseWithParams\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/session/session.go:1786\ngithub.com/pingcap/tidb/session.(*session).ExecuteInternal\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/session/session.go:1670\ngithub.com/pingcap/tidb/statistics/handle.saveTopNToStorage\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/statistics/handle/handle.go:1577\ngithub.com/pingcap/tidb/statistics/handle.SaveTableStatsToStorage\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/statistics/handle/handle.go:1757\ngithub.com/pingcap/tidb/statistics/handle.(*Handle).SaveTableStatsToStorage\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/statistics/handle/handle.go:1643\ngithub.com/pingcap/tidb/executor.(*AnalyzeExec).handleResultsError\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/analyze.go:315\ngithub.com/pingcap/tidb/executor.(*AnalyzeExec).Next\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/analyze.go:185\ngithub.com/pingcap/tidb/executor.Next\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/executor.go:328\ngithub.com/pingcap/tidb/executor.(*ExecStmt).next\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/adapter.go:1137\ngithub.com/pingcap/tidb/executor.(*ExecStmt).handleNoDelayExecutor\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/adapter.go:910\ngithub.com/pingcap/tidb/executor.(*ExecStmt).handleNoDelay\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/adapter.go:758\ngithub.com/pingcap/tidb/executor.(*ExecStmt).Exec\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/adapter.go:558\ngithub.com/pingcap/tidb/session.runStmt\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/session/session.go:2350\ngithub.com/pingcap/tidb/session.(*session).ExecuteStmt\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/session/session.go:2214\ngithub.com/pingcap/tidb/server.(*TiDBContext).ExecuteStmt\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/driver_tidb.go:252\ngithub.com/pingcap/tidb/server.(*clientConn).handleStmt\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/conn.go:2122\ngithub.com/pingcap/tidb/server.(*clientConn).handleQuery\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/conn.go:1972\ngithub.com/pingcap/tidb/server.(*clientConn).dispatch\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/conn.go:1403\ngithub.com/pingcap/tidb/server.(*clientConn).Run\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/conn.go:1152\ngithub.com/pingcap/tidb/server.(*Server).onConn\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/server.go:648\nruntime.goexit\n\t/usr/local/go/src/runtime/asm_amd64.s:1594"] [session="{\n  \"currDBName\": \"masked-name\",\n  \"id\": 2076221000868954593,\n  \"status\": 514,\n  \"strictMode\": true,\n  \"user\": {\n    \"Username\": \"root\",\n    \"Hostname\": \"10.100.0.99\",\n    \"CurrentUser\": false,\n    \"AuthUsername\": \"root\",\n    \"AuthHostname\": \"%\"\n  }\n}"]
[2023/04/22 10:08:36.885 +00:00] [INFO] [conn.go:1181] ["command dispatched failed"] [conn=2076221000868954593] [connInfo="id:2076221000868954593, addr:10.100.0.99:1617 status:1000000010, collation:utf8mb4_general_ci, user:root"] [command=Query] [status="inTxn:0, autocommit:1"] [sql="/* ApplicationName=DBeaver 22.3.1 - SQLEditor <Script-8.sql> */ analyze table active"] [txn_mode=PESSIMISTIC] [timestamp=440967657342107649] [err="[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"]
[2023/04/22 10:08:37.234 +00:00] [WARN] [server.go:614] ["Server.onConn handshake"] [conn=7143878889381560781] [error="read tcp 10.12.13.17:4000->10.12.13.1:45182: read: connection reset by peer"] ["remote addr"=10.12.13.1:45182]
[2023/04/22 10:08:48.059 +00:00] [INFO] [domain.go:2309] ["refreshServerIDTTL succeed"] [serverID=3248660] ["lease id"=41a87a3d1710402]
[2023/04/22 10:08:48.290 +00:00] [INFO] [gc_worker.go:307] ["[gc worker] there's already a gc job running, skipped"] ["leaderTick on"=61ea1ade2480008]
[2023/04/22 10:08:49.903 +00:00] [INFO] [gc_worker.go:721] ["[gc worker] start delete ranges"] [uuid=61ea1ade2480008] [ranges=0]
[2023/04/22 10:08:49.903 +00:00] [INFO] [gc_worker.go:768] ["[gc worker] finish delete ranges"] [uuid=61ea1ade2480008] ["num of ranges"=0] ["cost time"=557ns]
[2023/04/22 10:08:49.905 +00:00] [INFO] [gc_worker.go:791] ["[gc worker] start redo-delete ranges"] [uuid=61ea1ade2480008] ["num of ranges"=0]
[2023/04/22 10:08:49.905 +00:00] [INFO] [gc_worker.go:820] ["[gc worker] finish redo-delete ranges"] [uuid=61ea1ade2480008] ["num of ranges"=0] ["cost time"=160ns]
[2023/04/22 10:08:49.914 +00:00] [INFO] [gc_worker.go:1667] ["[gc worker] sent safe point to PD"] [uuid=61ea1ade2480008] ["safe point"=440967471639298048]

1 Like

Thx. Will reply as soon as possible.

Problem was solved by removing NO_BACKSLASH_ESCAPES from sql_mode and restarting tidb.