tidb 批量提交数据条数超过10条就会报错

🪐 TiDB 技术问题 部署&运维管理
1

[misc.go:167] [“syntax error”] [error="line 3 column 15 near “” "]

2

看报错可能是 sql 语句本身的语法存在问题，比如出现了非预期的字符。建议先检查下 sql 语句。

3

那为什么把数量降低就不会出现这个错误了呢

4

1、提供 batch 的 sql ，比如 10 条报错的示例，以及 batch 的方式，是通过应用端发起的还是直接从 mysql 客户端发起
2、目标表的表结构

5

这是sql语句，orm用的是mybatis

<insert id="batchInsert" >
    insert into dot
        (mac, account_id, counter,offline,create_time_long,pen_type,page_id,message)
    VALUES
    <foreach collection="list" item="item" separator=",">
        (
        #{item.mac},#{item.accountId},#{item.counter},#{item.offline},#{item.createTimeLong},#{item.penType},#{item.pageId},#{item.message}
        )
    </foreach>
</insert>

语言是Java

环境是多线程并发情况下

INSERT INTO dot(mac, account_id, counter, offline, create_time_long, pen_type, page_id, message) VALUES (‘123400000400’, 0, 211837708, 1, 1603469167090, 1, 16383, ‘0100000000f400d0000000000017000c0a5e000001755635c9f2000001755635c9f2’),
(‘123400000400’, 0, 211837708, 1, 1603469167090, 1, 16383, ‘0100000000f500d0000000000017000d1d0c000001755635c9f2000001755635c9f2’),
(‘123400000400’, 0, 211837708, 1, 1603469167090, 1, 16383, ‘0100000000f600d0000000000017000d2215000001755635c9f2000001755635c9f2’),
(‘123400000400’, 0, 211837708, 1, 1603469167090, 1, 16383, ‘0100000000f700d0000000000017000d2222000001755635c9f2000001755635c9f2’),
(‘123400000400’, 0, 211837708, 1, 1603469167090, 1, 16383, ‘0100000000f800cc000000000017000d0a5d000001755635c9f2000001755635c9f2’),
(‘123400000400’, 0, 211837708, 1, 1603469167090, 1, 16383, ‘0100000000f9008f000000000017000e030e000001755635c9f2000001755635c9f2’),
(‘123400000400’, 0, 211837708, 1, 1603469167090, 1, 16383, ‘0100000000fa0052000000000016000e6028000001755635c9f2000001755635c9f2’),
(‘123400000400’, 0, 211837708, 1, 1603469167090, 1, 16383, ‘0100000000fb0015000000000016000e6236000001755635c9f2000001755635c9f2’),
(‘123400000400’, 0, 211837708, 1, 1603469167090, 1, 16383, ‘0200000000fd0000000000000016000e6236000001755635c9f2000001755635c9f2’),
(‘123400000400’, 0, 211837711, 1, 1603469167090, 1, 16383, ‘0000000000fe00d6000000000018000c1527000001755635c9f2000001755635c9f2’),
(‘123400000400’, 0, 211837711, 1, 1603469167090, 1, 16383, ‘0100000000ff00d6000000000018000c1527000001755635c9f2000001755635c9f2’);

    CREATE TABLE dot (
      `mac` varchar(255) NULL,
      `account_id` bigint(0) NULL,
      `counter` bigint(255) NULL,
      `offline` int(255) NULL,
      `create_time_long` bigint(0) NULL,
      `pen_type` int(255) NULL,
      `page_id` bigint(0) NULL,
      `message` varchar(255) NULL
    )SHARD_ROW_ID_BITS = 4 PRE_SPLIT_REGIONS=3;
6

1、上述 sql 无法复现报错
2、提供拼接后的发送给数据库的原始 sql，非手动复制粘贴方式
1）如果应用日志记录了拼接后的完整的 batch sql ，可提供
2）如果 tidb server 开启了 general log ，可提供

7

问题是我不知道哪句sql报错了，这个error日志是在你们dashboard看到的，我程序这边没有任何错误日志，所有请求都是成功的， bathc 数量超过10个， 请求一半成功一半失败

image
image828×628 35.6 KB
， tidb server general log 在哪个地方

8

我发现了

image
image3076×1702 542 KB

为什么会被打上双引号，而且状态是成功
image
image3044×1722 716 KB

9

1、请将带有 “” sql 单独拿出来，并且在 tidb server 尝试运行下看下结果。
2、理论上，此处显示的均为原始 sql ，tidb 不会修改原 sql 语句。
3、从上面的记录看，带有双引号的 insert 同样会带有 "
" ,如果应用服务器是多台部署，建议检查下各个应用服务上代码的版本是否一致。

如果上面的方法都无法复现验证，可以在暂时将 tidb server 的 general log 开启，捕捉下非正常的 insert sql 是从哪里发起的，以及记录下原始 sql 语句。开启方式参考：

image
image1348×202 29.6 KB

10

在哪个目录下能看到呢

11

{deploy_dir}/tidb-xxxx/log/tidb.log 找下 ~

12

[2020/10/27 16:01:42.560 +08:00] [ERROR] [misc.go:167] [“syntax error”] [error="line 3 column 15 near “” “]
[2020/10/27 16:01:42.560 +08:00] [INFO] [conn.go:780] [“command dispatched failed”] [conn=19860] [connInfo=“id:19860, addr:127.0.0.1:37978 status:10, collation:utf8_general_ci, user:root”] [command=Query] [status=“inTxn:0, autocommit:1”] [sql=“insert into dot_1\ (mac, account_id, counter,offline,create_time_long,pen_type,page_id,message)\ VALUES”] [txn_mode=PESSIMISTIC] [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 line 3 column 15 near “” "]
[2020/10/27 16:01:42.560 +08:00] [WARN] [session.go:1128] [“parse SQL failed”] [conn=19860] [error="line 3 column 15 near “” "] [SQL=“insert into dot_1\ (mac, account_id, counter,offline,create_time_long,pen_type,page_id,message)\ VALUES”]
[2020/10/27 16:01:42.560 +08:00] [ERROR] [misc.go:167] [“syntax error”] [error="line 3 column 15 near “” “]
[2020/10/27 16:01:42.560 +08:00] [INFO] [conn.go:780] [“command dispatched failed”] [conn=19860] [connInfo=“id:19860, addr:127.0.0.1:37978 status:10, collation:utf8_general_ci, user:root”] [command=Query] [status=“inTxn:0, autocommit:1”] [sql=“insert into dot_1\ (mac, account_id, counter,offline,create_time_long,pen_type,page_id,message)\ VALUES”] [txn_mode=PESSIMISTIC] [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 line 3 column 15 near “” "]
[2020/10/27 16:01:42.561 +08:00] [WARN] [session.go:1128] [“parse SQL failed”] [conn=19880] [error="line 3 column 15 near “” "] [SQL=“insert into dot_1\ (mac, account_id, counter,offline,create_time_long,pen_type,page_id,message)\ VALUES”]
[2020/10/27 16:01:42.561 +08:00] [ERROR] [misc.go:167] [“syntax error”] [error="line 3 column 15 near “” “]
[2020/10/27 16:01:42.561 +08:00] [INFO] [conn.go:780] [“command dispatched failed”] [conn=19880] [connInfo=“id:19880, addr:127.0.0.1:38154 status:10, collation:utf8_general_ci, user:root”] [command=Query] [status=“inTxn:0, autocommit:1”] [sql=“insert into dot_1\ (mac, account_id, counter,offline,create_time_long,pen_type,page_id,message)\ VALUES”] [txn_mode=PESSIMISTIC] [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 line 3 column 15 near “” "]
[2020/10/27 16:01:42.561 +08:00] [WARN] [session.go:1128] [“parse SQL failed”] [conn=19874] [error="line 3 column 15 near “” "] [SQL=“insert into dot_1\ (mac, account_id, counter,offline,create_time_long,pen_type,page_id,message)\ VALUES”]
[2020/10/27 16:01:42.561 +08:00] [ERROR] [misc.go:167] [“syntax error”] [error="line 3 column 15 near “” “]
[2020/10/27 16:01:42.561 +08:00] [INFO] [conn.go:780] [“command dispatched failed”] [conn=19874] [connInfo=“id:19874, addr:127.0.0.1:38092 status:10, collation:utf8_general_ci, user:root”] [command=Query] [status=“inTxn:0, autocommit:1”] [sql=“insert into dot_1\ (mac, account_id, counter,offline,create_time_long,pen_type,page_id,message)\ VALUES”] [txn_mode=PESSIMISTIC] [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 line 3 column 15 near “” "]
[2020/10/27 16:01:42.561 +08:00] [WARN] [session.go:1128] [“parse SQL failed”] [conn=19860] [error="line 3 column 15 near “” "] [SQL=“insert into dot_1\ (mac, account_id, counter,offline,create_time_long,pen_type,page_id,message)\ VALUES”]
[2020/10/27 16:01:42.561 +08:00] [ERROR] [misc.go:167] [“syntax error”] [error="line 3 column 15 near “” “]
[2020/10/27 16:01:42.562 +08:00] [INFO] [conn.go:780] [“command dispatched failed”] [conn=19860] [connInfo=“id:19860, addr:127.0.0.1:37978 status:10, collation:utf8_general_ci, user:root”] [command=Query] [status=“inTxn:0, autocommit:1”] [sql=“insert into dot_1\ (mac, account_id, counter,offline,create_time_long,pen_type,page_id,message)\ VALUES”] [txn_mode=PESSIMISTIC] [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 line 3 column 15 near “” "]
[2020/10/27 16:01:42.562 +08:00] [WARN] [session.go:1128] [“parse SQL failed”] [conn=19874] [error="line 3 column 15 near “” "] [SQL=“insert into dot_1\ (mac, account_id, counter,offline,create_time_long,pen_type,page_id,message)\ VALUES”]^C

13

因为有\ 才报错的，但是\ 不知道是从哪出现的

14

应用端发起的 sql ，原则上 tidb 不会二次拼装 sql ，请从应用端检查应用端服务器上的版本是否一致 ~

15

我只部署了一个服务

16

现在从 tidb 端能够看到确实有非法字符的 sql 出现，所以建议将应用端拼接好的 sql 以日志的形式输出，然后再次确认下问题的原因~~

17

但是为什么其他的不会呢，

18

从 tidb 的日志可见，确实存在非法字符，理论上 tidb 不会修改客户端的 sql 。建议将应用端拼接后的原 sql 输出到文件中，然后排查下，是否有非预期 sql ~

19

报错是这个，values 后面就没了。
sql=“insert into dot_1\ (mac, account_id, counter,offline,create_time_long,pen_type,page_id,message)\ VALUES”
这个有些批量插入的 values 个数是 0 吧。
这里 <foreach collection="list" item="item" separator=","> 的 collection 可能是空的。

20

如果values为0 都不会调用这个insert执行方法