java - mariadb get 上的 TransactionException

Question

对有点误导性的标题表示歉意。

我有一个 device-id 应用程序，它基本上保存发送到系统的任何新 deviceId 或根据请求返回任何 deviceId 的详细信息.

这个问题不是很常见，但它确实时不时地出现，而且 Google 没有帮助解决它!

数据存储位于 MariaDB-10.1.35 。服务的 QPS Activity 约为 2.5k，几个 google 链接建议增加 wait_timeout，但考虑到 QPS，600 秒的 wait_timeout 是一个安全的选择。

产品配置有:

validationQuery: "/* Health Check */ SELECT 1"
wait_timeout: 600

标准函数(围绕 hystrix 包装器构建如下)

public Optional<DeviceId> get(final String token) throws DeviceIdException {
    try {
        return CommandFactory.<Optional<DeviceId>>create("DeviceId", "Get")
                .executor(() -> DaoRegistry.getDeviceIdLookupDao().get(token))
                .toObservable()
                .toBlocking()
                .single();
    } catch (Exception e) {
        log.error("Get error {}", e);
        throw DeviceIdException.builder()
                .code(Response.Status.INTERNAL_SERVER_ERROR.getStatusCode())
                .errorCode("DI000")
                .message("Oops! Something went wrong!")
                .build();
    }
}

堆栈转储如下

Caused by: org.hibernate.TransactionException: JDBC begin transaction failed:
    at org.hibernate.resource.jdbc.internal.AbstractLogicalConnectionImplementor.begin(AbstractLogicalConnectionImplementor.java:73)
    at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.begin(LogicalConnectionManagedImpl.java:263)
    at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.begin(JdbcResourceLocalTransactionCoordinatorImpl.java:214)
    at org.hibernate.engine.transaction.internal.TransactionImpl.begin(TransactionImpl.java:56)
    at org.hibernate.internal.AbstractSharedSessionContract.beginTransaction(AbstractSharedSessionContract.java:409)
    at io.dropwizard.sharding.utils.TransactionHandler.beginTransaction(TransactionHandler.java:96)
    at io.dropwizard.sharding.utils.TransactionHandler.beforeStart(TransactionHandler.java:48)
    at io.dropwizard.sharding.utils.Transactions.execute(Transactions.java:52)
    at io.dropwizard.sharding.utils.Transactions.execute(Transactions.java:46)
    at io.dropwizard.sharding.dao.LookupDao.get(LookupDao.java:192)
    at io.dropwizard.sharding.dao.LookupDao.get(LookupDao.java:176)
    at com.myOrganisation.MyCustomService.commands.DeviceIdCommands.lambda$get$1(DeviceIdCommands.java:73)
    at io.appform.core.hystrix.GenericHystrixCommand$1.run(GenericHystrixCommand.java:43)
    at com.netflix.hystrix.HystrixCommand$2.call(HystrixCommand.java:302)
    at com.netflix.hystrix.HystrixCommand$2.call(HystrixCommand.java:298)
    at rx.internal.operators.OnSubscribeDefer.call(OnSubscribeDefer.java:46)
    ... 26 common frames omitted
Caused by: java.sql.SQLNonTransientConnectionException: (conn=2997305) unexpected end of stream, read 0 bytes from 4 (socket was closed by server)
    at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.get(ExceptionMapper.java:234)
    at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.getException(ExceptionMapper.java:165)
    at org.mariadb.jdbc.MariaDbStatement.executeExceptionEpilogue(MariaDbStatement.java:238)
    at org.mariadb.jdbc.MariaDbStatement.executeInternal(MariaDbStatement.java:356)
    at org.mariadb.jdbc.MariaDbStatement.executeUpdate(MariaDbStatement.java:545)
    at org.mariadb.jdbc.MariaDbConnection.setAutoCommit(MariaDbConnection.java:751)
    at sun.reflect.GeneratedMethodAccessor39.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.apache.tomcat.jdbc.pool.ProxyConnection.invoke(ProxyConnection.java:126)
    at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:108)
    at org.apache.tomcat.jdbc.pool.interceptor.AbstractCreateStatementInterceptor.invoke(AbstractCreateStatementInterceptor.java:79)
    at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:108)
    at org.apache.tomcat.jdbc.pool.DisposableConnectionFacade.invoke(DisposableConnectionFacade.java:81)
    at com.sun.proxy.$Proxy53.setAutoCommit(Unknown Source)
    at org.hibernate.resource.jdbc.internal.AbstractLogicalConnectionImplementor.begin(AbstractLogicalConnectionImplementor.java:67)
    ... 41 common frames omitted
Caused by: java.sql.SQLException: unexpected end of stream, read 0 bytes from 4 (socket was closed by server)
Query is: set autocommit=0
java thread: hystrix-DeviceId.Get-4
    at org.mariadb.jdbc.internal.util.LogQueryTool.exceptionWithQuery(LogQueryTool.java:126)
    at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:222)
    at org.mariadb.jdbc.MariaDbStatement.executeInternal(MariaDbStatement.java:350)
    ... 53 common frames omitted
Caused by: java.io.EOFException: unexpected end of stream, read 0 bytes from 4 (socket was closed by server)
    at org.mariadb.jdbc.internal.io.input.StandardPacketInputStream.getPacketArray(StandardPacketInputStream.java:238)
    at org.mariadb.jdbc.internal.io.input.StandardPacketInputStream.getPacket(StandardPacketInputStream.java:207)
    at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readPacket(AbstractQueryProtocol.java:1427)
    at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.getResult(AbstractQueryProtocol.java:1407)
    at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:219)
    ... 54 common frames omitted

有人可以帮助我们找到正确的方向，以了解可能会损坏/丢失的内容吗？

Answer 1

看起来像是 wait_timeout 问题。如果该值设置为以小时为单位；将其减少到合理的时间；说600秒。期望连接能够保持几个小时就过于乐观了。希望这会有所帮助。