MySQL Bugs: #96464: bug about json

Bug #96464	bug about json_table function
Submitted:	8 Aug 2019 8:39	Modified:	23 Sep 2019 16:15
Reporter:	bilong lu	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
when i use sql like "select xxx,jt.*  from table1,json_table(xxx, xxx columns(xxxx)) jt" .if the result.rows>one number about 20000 ,  mysql service on the service machine was shut down by the sql;

How to repeat:
mysql workbenck  error log :
16:09:38	insert into xcxddatabase.sycms_items(`uselessinfoforetl`,`datetype`,`starttime`,`endtime`,`cateid`,`itemid`,`iteminfo`,`picurl`,`detailurl`,`userid`,`shopinfo`,`payrateindex`,`tradeindex`)      select replace( urlpath->'$[4]','"','') as dim,replace(querystring->'$.dateType','"','') as dp,left(replace( querystring->'$.dateRange','"',''),10) as starttime,right(replace( querystring->'$.dateRange','"',''),10) as endtime,replace(querystring->'$.cateId','"','') as cid, jt.itemid,jt.iteminfo,jt.picurl,jt.detailurl,jt.userid,jt.shop,if(jt.payRateIndex='null',0,jt.payRateIndex),jt.tradeIndex from xcxddatabase.sycmsdatabdl, json_table(datajsontext,'$[*]' columns( itemid varchar(50) path '$.itemId.value', iteminfo varchar(200) path '$.item.title', picurl varchar(200) path '$.item.pictUrl', detailurl varchar(200) path '$.item.detailUrl', userid varchar(200) path '$.shop.userId', shop json path '$.shop', payRateIndex varchar(300) path '$.payRateIndex.value', tradeIndex varchar(300) path '$.tradeIndex.value' )) jt where jsonfilename='hotsale' having dim='item'  limit 12000,2000 on duplicate key update shopinfo=values(shopinfo),picurl=values(picurl),detailurl=values(detailurl),userid=values(userid),payrateindex=values(payrateindex),tradeindex=values(tradeindex),iteminfo=values(iteminfo)	Error Code: 2013. Lost connection to MySQL server during query	2.094 sec

Suggested fix:
i hope that i can use the json_table function like using nomal table; however how many rows select from nomal table ,mysql service on the service machine never shut down,i always can get the rusult

Hello bilong lu,

Thank you for the report.
Could you please provide exact repeatable test case(show create table, logical dump of the table and exact query which triggering this issue), MySQL Server version to investigate this issue at our end? Thank you!

regards,
Umesh

CREATE TABLE `sycmsdatabdl` (
  `jsonfilename` varchar(200) DEFAULT NULL,
  `urlpath` varchar(200) DEFAULT NULL,
  `querystring` varchar(2000) DEFAULT NULL,
  `datajsontext` longtext,
  `inserttime` datetime DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

my mysql version is 8.0.16

Hello Mr. lu,

When we ask you for the feedback, please provide us with full feedback that we asked for. Hence, .....

Could you please provide exact repeatable test case , which should include also  logical dump of the table and exact query which triggering this issue.  Also, let us know whether that SELECT , when ran without INSERT is leading to the loss of connection. Last, but not least, let us know how long does it take before connection is lost and what are your settings for all the relevant timeout.

Many thanks in advance.....

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".