SQL提示Communications link failure

Limbo · 2020 年3 月 26 日 07:58

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

【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.

驱动版本:

zzzzzz · 2020 年3 月 26 日 09:05

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

Limbo · 2020 年3 月 26 日 09:06

感觉跟长连接无关，这个是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

zzzzzz · 2020 年3 月 26 日 12:12

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