SQL提示Communications link failure

为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。

  • 【TiDB 版本】:3.0
  • 【问题描述】:SQL提示Communications link failure

执行sql为 select wr.id as roleid, wr.role_group_id, wr.name, wr.code, wr.alias_name, wr.status, wr.flag, wu.id as userid, wu.id, wu.parent_id, wu.loginname, wu.password, wu.salt, wu.phone, wu.wxid, wu.username, wu.sex, wu.avatar, wu.email, wu.status, wu.custom_menu, wu.last_login_time, wu.last_login_ip, wu.create_time, wu.modify_time from work_user_role_unit as wuru left join work_role wr on wr.id = wuru.role_id left join work_user wu on wuru.user_id and wu.id where flag = @flag and wu.pid > 0 and wr.pid > 0 and wu.status = ‘NORMAL’ and wuru.status = ‘NORMAL’ and wr.status = ‘NORMAL’ group by userid, roleid,wuru.id

报错如下 [2020-03-26 15:55:33] [08S01] Communications link failure [2020-03-26 15:55:33] The last packet successfully received from the server was 20 milliseconds ago. The last packet sent successfully to the server was 20 milliseconds ago. [2020-03-26 15:55:33] java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.

驱动版本:image

嗯,这边麻烦检查下 java 驱动中长链接的保持时间

感觉跟长连接无关,这个是sql过去瞬间就断连了 配置如下

allowLoadLocalInfile true
allowMultiQueries false
allowNanAndInf false
allowUrlInLocalInfile false
alwaysSendSetIsolation true
autoClosePStmtStreams false
replicationConnectionGroup
allowMasterDownConnections false
allowSlaveDownConnections false
readFromMasterWhenNoSlaves false
autoDeserialize false
autoGenerateTestcaseScript false
autoReconnect true
autoReconnectForPools false
blobSendChunkSize 1048576
autoSlowLog true
blobsAreStrings false
functionsNeverReturnBlobs false
cacheCallableStmts false
cachePrepStmts false
cacheResultSetMetadata false
serverConfigCacheFactory com.mysql.jdbc.PerVmServerConfigCacheFactory
cacheServerConfiguration false
callableStmtCacheSize 100
capitalizeTypeNames true
characterEncoding utf8
characterSetResults utf8
connectionAttributes
clientInfoProvider com.mysql.jdbc.JDBC4CommentClientInfoProvider
clobberStreamingResults false
clobCharacterEncoding
compensateOnDuplicateKeyUpdateCounts false
connectionCollation
connectionLifecycleInterceptors
connectTimeout 0
continueBatchOnError true
createDatabaseIfNotExist false
defaultFetchSize 0
useServerPrepStmts false
dontTrackOpenResources false
dumpQueriesOnException false
dynamicCalendars false
elideSetAutoCommits false
emptyStringsConvertToZero true
emulateLocators false
emulateUnsupportedPstmts true
enablePacketDebug false
enableQueryTimeouts true
explainSlowQueries false
exceptionInterceptors
failOverReadOnly true
gatherPerfMetrics false
generateSimpleParameterMetadata false
holdResultsOpenOverStatementClose false
includeInnodbStatusInDeadlockExceptions false
includeThreadDumpInDeadlockExceptions false
includeThreadNamesAsStatementComment false
ignoreNonTxTables false
initialTimeout 2
interactiveClient false
jdbcCompliantTruncation true
largeRowSizeThreshold 2048
loadBalanceStrategy random
serverAffinityOrder
loadBalanceBlacklistTimeout 0
loadBalancePingTimeout 0
loadBalanceValidateConnectionOnSwapServer false
loadBalanceConnectionGroup
loadBalanceExceptionChecker com.mysql.jdbc.StandardLoadBalanceExceptionChecker
loadBalanceSQLStateFailover
loadBalanceSQLExceptionSubclassFailover
loadBalanceEnableJMX false
loadBalanceHostRemovalGracePeriod 15000
loadBalanceAutoCommitStatementRegex
loadBalanceAutoCommitStatementThreshold 0
localSocketAddress
locatorFetchBufferSize 1048576
logger com.mysql.jdbc.log.StandardLogger
logSlowQueries false
logXaCommands false
maintainTimeStats true
maxQuerySizeToLog 2048
maxReconnects 3
retriesAllDown 120
maxRows -1
metadataCacheSize 50
netTimeoutForStreamingResults 600
noAccessToProcedureBodies false
noDatetimeStringSync false
noTimezoneConversionForTimeType false
noTimezoneConversionForDateType true
cacheDefaultTimezone true
nullCatalogMeansCurrent true
nullNamePatternMatchesAll true
packetDebugBufferSize 20
padCharsWithSpace false
paranoid false
pedantic false
pinGlobalTxToPhysicalConnection false
populateInsertRowWithDefaultValues false
prepStmtCacheSize 25
prepStmtCacheSqlLimit 256
parseInfoCacheFactory com.mysql.jdbc.PerConnectionLRUFactory
processEscapeCodesForPrepStmts true
profilerEventHandler com.mysql.jdbc.profiler.LoggingProfilerEventHandler
profileSql
profileSQL false
propertiesTransform
queriesBeforeRetryMaster 50
queryTimeoutKillsConnection false
reconnectAtTxEnd false
relaxAutoCommit false
reportMetricsIntervalMillis 30000
requireSSL false
resourceId
resultSetSizeThreshold 100
retainStatementAfterResultSetClose false
rewriteBatchedStatements false
rollbackOnPooledClose true
roundRobinLoadBalance false
runningCTS13 false
secondsBeforeRetryMaster 30
selfDestructOnPingSecondsLifetime 0
selfDestructOnPingMaxOperations 0
replicationEnableJMX false
serverTimezone
sessionVariables
slowQueryThresholdMillis 2000
slowQueryThresholdNanos 0
socketFactory com.mysql.jdbc.StandardSocketFactory
socksProxyHost
socksProxyPort 1080
socketTimeout 0
statementInterceptors
strictFloatingPoint false
strictUpdates true
overrideSupportsIntegrityEnhancementFacility false
tcpNoDelay true
tcpKeepAlive true
tcpRcvBuf 0
tcpSndBuf 0
tcpTrafficClass 0
tinyInt1isBit false
traceProtocol false
treatUtilDateAsTimestamp true
transformedBitIsBoolean false
useBlobToStoreUTF8OutsideBMP false
utf8OutsideBmpExcludedColumnNamePattern
utf8OutsideBmpIncludedColumnNamePattern
useCompression false
useColumnNamesInFindColumn false
useConfigs
useCursorFetch false
useDynamicCharsetInfo true
useDirectRowUnpack true
useFastIntParsing true
useFastDateParsing true
useHostsInPrivileges true
useInformationSchema false
useJDBCCompliantTimezoneShift false
useLocalSessionState false
useLocalTransactionState false
useLegacyDatetimeCode true
sendFractionalSeconds true
useNanosForElapsedTime false
useOldAliasMetadataBehavior false
useOldUTF8Behavior false
useOnlyServerErrorMessages true
useReadAheadInput true
useSqlStateCodes true
useSSL false
useSSPSCompatibleTimezoneShift false
useStreamLengthsInPrepStmts true
useTimezone false
ultraDevHack false
useUnbufferedInput true
useUnicode true
useUsageAdvisor false
yearIsDateType false
zeroDateTimeBehavior convertToNull
useJvmCharsetConverters false
useGmtMillisForDatetimes false
dumpMetadataOnColumnNotFound false
clientCertificateKeyStoreUrl
trustCertificateKeyStoreUrl
clientCertificateKeyStoreType JKS
clientCertificateKeyStorePassword
trustCertificateKeyStoreType JKS
trustCertificateKeyStorePassword
verifyServerCertificate true
useAffectedRows false
passwordCharacterEncoding
maxAllowedPacket -1
authenticationPlugins
disabledAuthenticationPlugins
defaultAuthenticationPlugin com.mysql.jdbc.authentication.MysqlNativePasswordPlugin
disconnectOnExpiredPasswords true
getProceduresReturnsFunctions true
detectCustomCollations false
serverRSAPublicKeyFile
allowPublicKeyRetrieval false
dontCheckOnDuplicateKeyUpdateInSQL false
readOnlyPropagatesToServer true
enabledSSLCipherSuites
enabledTLSProtocols
enableEscapeProcessing true

检查 wait_timeout 系统变量,可以参考 mysql 的相关处理方式。