[misc.go:167] [“syntax error”] [error="line 3 column 15 near “” "]
看报错可能是 sql 语句本身的语法存在问题,比如出现了非预期的字符。建议先检查下 sql 语句。
那为什么把数量降低就不会出现这个错误了呢
1、提供 batch 的 sql ,比如 10 条报错的示例,以及 batch 的方式,是通过应用端发起的还是直接从 mysql 客户端发起
2、目标表的表结构
这是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;
1、上述 sql 无法复现报错
2、提供拼接后的发送给数据库的原始 sql,非手动复制粘贴方式
1)如果应用日志记录了拼接后的完整的 batch sql ,可提供
2)如果 tidb server 开启了 general log ,可提供
问题是我不知道哪句sql报错了,这个error日志是在你们dashboard看到的,我程序这边没有任何错误日志,所有请求都是成功的, bathc 数量超过10个, 请求一半成功一半失败
, tidb server general log 在哪个地方1、请将带有 “” sql 单独拿出来,并且在 tidb server 尝试运行下看下结果。
2、理论上,此处显示的均为原始 sql ,tidb 不会修改原 sql 语句。
3、从上面的记录看,带有双引号的 insert 同样会带有 "
" ,如果应用服务器是多台部署,建议检查下各个应用服务上代码的版本是否一致。
如果上面的方法都无法复现验证,可以在暂时将 tidb server 的 general log 开启,捕捉下非正常的 insert sql 是从哪里发起的,以及记录下原始 sql 语句。开启方式参考:
在哪个目录下能看到呢
{deploy_dir}/tidb-xxxx/log/tidb.log 找下 ~
[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
因为有\ 才报错的,但是\ 不知道是从哪出现的
应用端发起的 sql ,原则上 tidb 不会二次拼装 sql ,请从应用端检查应用端服务器上的版本是否一致 ~
我只部署了一个服务
现在从 tidb 端能够看到确实有非法字符的 sql 出现,所以建议将应用端拼接好的 sql 以日志的形式输出,然后再次确认下问题的原因~~
但是为什么其他的不会呢,
从 tidb 的日志可见,确实存在非法字符,理论上 tidb 不会修改客户端的 sql 。建议将应用端拼接后的原 sql 输出到文件中,然后排查下,是否有非预期 sql ~
报错是这个,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 可能是空的。
如果values为0 都不会调用这个insert执行方法