Bug #100215 insert into sql timeout spent 10s mysql 5.7
Submitted: 14 Jul 2020 13:19 Modified: 17 Aug 2020 12:06
Reporter: zhou andy Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S5 (Performance)
Version:5.7.1.6 OS:Any
Assigned to: CPU Architecture:Any

[14 Jul 2020 13:19] zhou andy
Description:
Id	User	Host	db	Command	Time	State	Info
2235340	dmall_task	10.28.39.245:41106	dmall_task	Query	1528	update	INSERT INTO task_message  ( send_user_ids,\nwarn_level,\n\nvender_id,\nrun_inst_id,\nstore_id,\nsend_time,\nmsg_content,\nyn,\n\nuser_type,\nadvice_type,\nrun_node_id,\nstatus )  VALUES  ( '11774',\n1,\n\n1,\n4085730,\n11478,\n'2020-07-14 22:26:49.804',\n'{"appType":3,"messageType":4,"methodCode":"message.appPush","params":{"content":{"actionUrl":"rn://promoteexhibit/promoteexhibit.jsbundle/execute?listData={\\\\"modelId\\\\":10038,\\\\"executeTaskId\\\\":\\\\"40401\\\\",\\\\"old\\\\":false,\\\\"sceneId\\\\":10017,\\\\"taskId\\\\":\\\\"9273658\\\\",\\\\"taskNodeInstId\\\\":9273658}","createTime":"2020-07-13T19:20:19.665","discript":"dfd:【xccvcvcv】fsadfsd,okokok","msgContent":"fd:【xccvcvcv】fsadfsd,okokok","nodeType":6,"storeId":11478,"taskId":"9273658","title":"my work"},"imgUrls":[],"importantStatus":0,"templateCode":0,"title":"my workd"},"personType":10,"receiveUserId":11774,"receiveUserName":"","sendUserId":-1,"sendUserName":"system create","sendUserType":3,"subMessageType":6,"venderId":1,"venderName":"","visible":2}',\n1,\n\n1,\n1,\n9273658,\n1 )

How to repeat:
I don't know how to repeat it, I try to find out the root cause but failed. It is happenned on my production ENV and only once.

Exception:
ERROR] 2020-07-13 19:27:04.515 [ConsumeMessageThread_1] [com.alibaba.druid.pool.DruidPooledStatement] - CommunicationsException, druid version 1.1.21, jdbcUrl : jdbc:mysql://database?connectTimeout=2000&socketTimeout=10000&characterEncoding=UTF-8&autoReconnect=true&allowMultiQueries=true&serverTimezone=Asia/Shanghai, testWhileIdle true, idle millis 10048, minIdle 4, poolingCount 14, timeBetweenEvictionRunsMillis 60000, lastValidIdleMillis 10048, driver com.mysql.cj.jdbc.Driver, exceptionSorter com.alibaba.druid.pool.vendor.MySqlExceptionSorter
[ERROR] 2020-07-13 19:27:04.523 [ConsumeMessageThread_1] [com.alibaba.druid.pool.DruidDataSource] - discard connection
com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure

The last packet successfully received from the server was 10,048 milliseconds ago. The last packet sent successfully to the server was 10,050 milliseconds ago.
        at com.mysql.cj.jdbc.exceptions.SQLError.createCommunicationsException(SQLError.java:174) ~[mysql-connector-java-8.0.19.jar!/:8.0.19]
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:64) ~[mysql-connector-java-8.0.19.jar!/:8.0.19]
--
        at com.mysql.cj.protocol.a.TimeTrackingPacketReader.readHeader(TimeTrackingPacketReader.java:41) ~[mysql-connector-java-8.0.19.jar!/:8.0.19]
        at com.mysql.cj.protocol.a.MultiPacketReader.readHeader(MultiPacketReader.java:54) ~[mysql-connector-java-8.0.19.jar!/:8.0.19]
        at com.mysql.cj.protocol.a.MultiPacketReader.readHeader(MultiPacketReader.java:44) ~[mysql-connector-java-8.0.19.jar!/:8.0.19]
        at com.mysql.cj.protocol.a.NativeProtocol.readMessage(NativeProtocol.java:533) ~[mysql-connector-java-8.0.19.jar!/:8.0.19]
        ... 62 common frames omitted

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://database?connectTimeout=2000&socketTimeout=10000&characterEncoding=UTF-8&autoReconnect=true&allowMultiQueries=true&serverTimezone=Asia/Shanghai
    username: s
    password: "pass"
    druid:
      initialSize: 4
      minIdle: 4
      maxActive: 40
      maxWait: 3000
      timeBetweenEvictionRunsMillis: 60000
      minEvictableIdleTimeMillis: 3600000
      validationQuery: SELECT 1
      testWhileIdle: true
      testOnBorrow: false
      testOnReturn: false
      poolPreparedStatements: false
      maxPoolPreparedStatementPerConnectionSize: -1
      removeAbandoned: true
      filters: wall
[15 Jul 2020 12:44] MySQL Verification Team
Hi Mr. zhou,

Thank you for your bug report.

However, we can not do anything about this report, until we have a fully repeatable test case.

Also, you have indicated that the problem is on MySQL server, and particularly in the release 5.7.1.6. A release like that does not exist for our server.

Are you sure that this is not a problem in our Connector/JDBC.

Regardless of the category, we still require a fully repeatable test case.
[16 Aug 2020 1:00] Bugs System
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".