问题描述

由于许可/商业用途的原因，我们正在从 MySql 迁移到 MariaDB.

We in the process of migrating from MySql to MariaDB due to licensing/commercial usage reasons.

我们已经成功地用 MariaDB 客户端 jar 替换了 MySql 连接器 jar(第一次更改)，现在正在尝试用 MariaDB 服务器替换 MySql 服务器而不更改数据文件.

We have successfully replaced the MySql connector jar with MariaDB client jar (first change) and are now trying to replace MySql server with MariaDB server without changing the data files.

我们所有的应用程序都可以完美运行大约 8-12 小时，之后我们会看到以下异常:

All our applications run perfectly for about 8-12 hours after which we see the following exception:

org.springframework.transaction.CannotCreateTransactionException: Could not open Hibernate Session for transaction; nested exception is org.hibernate.exception.JDBCConnectionException: Cannot open connection
Caused by:
org.hibernate.exception.JDBCConnectionException: Cannot open connection
        at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:74)
        at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
        at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:29)
        at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:426)
        at org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:144)
        at org.hibernate.jdbc.JDBCContext.connection(JDBCContext.java:119)
        at org.hibernate.transaction.JDBCTransaction.begin(JDBCTransaction.java:57)
        at org.hibernate.impl.SessionImpl.beginTransaction(SessionImpl.java:1326)
        at org.springframework.orm.hibernate3.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:494)
        at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:315)
        at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:257)
        at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:102)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
        at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:209)
        at $Proxy4.getMessageCountByStatus(Unknown Source)
        at com.onmobile.cmfweb.monitoring.CmfMessagesMonitor.getMessageCounts(CmfMessagesMonitor.java:56)
        at sun.reflect.GeneratedMethodAccessor625.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at org.springframework.util.MethodInvoker.invoke(MethodInvoker.java:270)
        at com.onmobile.cmfshare.MethodInvockingBean.invoke(MethodInvockingBean.java:28)
        at org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean$MethodInvokingJob.executeInternal(MethodInvokingJobDetailFactoryBean.java:212)
        at org.springframework.scheduling.quartz.QuartzJobBean.execute(QuartzJobBean.java:79)
        at org.quartz.core.JobRunShell.run(JobRunShell.java:203)
        at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:520)
Caused by: java.sql.SQLNonTransientConnectionException: Could not connect to localhost:3306: unexpected end of stream, read 0bytes from 4
        at org.mariadb.jdbc.internal.SQLExceptionMapper.get(SQLExceptionMapper.java:136)
        at org.mariadb.jdbc.internal.SQLExceptionMapper.throwException(SQLExceptionMapper.java:106)
        at org.mariadb.jdbc.Driver.connect(Driver.java:114)
        at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:37)
        at org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:290)
        at org.apache.commons.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:840)
        at org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:95)
        at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:544)
        at org.springframework.orm.hibernate3.LocalDataSourceConnectionProvider.getConnection(LocalDataSourceConnectionProvider.java:81)
        at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:423)
        ... 21 more
Caused by: org.mariadb.jdbc.internal.common.QueryException: Could not connect to localhost:3306: unexpected end of stream, read 0bytes from 4
        at org.mariadb.jdbc.internal.mysql.MySQLProtocol.connect(MySQLProtocol.java:509)
        at org.mariadb.jdbc.internal.mysql.MySQLProtocol.connect(MySQLProtocol.java:669)
        at org.mariadb.jdbc.internal.mysql.MySQLProtocol.<init>(MySQLProtocol.java:264)
        at org.mariadb.jdbc.Driver.connect(Driver.java:110)
        ... 28 more
Caused by: java.io.EOFException: unexpected end of stream, read 0bytes from 4
        at org.mariadb.jdbc.internal.common.packet.buffer.ReadUtil.readFully(ReadUtil.java:84)
        at org.mariadb.jdbc.internal.common.packet.buffer.ReadUtil.readFully(ReadUtil.java:92)
        at org.mariadb.jdbc.internal.common.packet.RawPacket.nextPacket(RawPacket.java:77)
        at org.mariadb.jdbc.internal.common.packet.SyncPacketFetcher.getRawPacket(SyncPacketFetcher.java:67)
        at org.mariadb.jdbc.internal.mysql.MySQLProtocol.connect(MySQLProtocol.java:467)
        ... 31 more

<小时>

如果再次使用 MySql 服务器，异常就会消失.

The exceptions disappear if MySql server is used again.

从我目前调试的情况来看，MariaDB 服务器出于某种原因正在关闭客户端连接.我最初怀疑是空闲连接，但我们使用的是像testOnBorrow"这样的 Hibernate 配置，所以不应该是这种情况.

From what I have debugged so far MariaDB server is closing the client connections for some reason. I initially suspected of idle connections but we are using Hibernate configurations like 'testOnBorrow' so it shouldn't be the case.

谁能帮助我们找出原因并帮助我们解决问题?MariaDB 中是否有任何我应该做的特定配置?

Can anyone help us to find out the cause for this and help us fix the problem? Is there any specific configuration in MariaDB that I should be doing?

我什至通过增加 MariaDB 服务器中参数interactive_timeout"的值来运行我们的应用程序，但它没有帮助.

I've even run our applications by increasing the value of the param 'interactive_timeout' in the MariaDB server but it did not help.

顺便说一句，我们正在使用 Spring-Hibernate 并使用 Commons-pool jar 池化我们的连接.

Btw, we are using Spring-Hibernate and pooling our connections using Commons-pool jar.

任何形式的帮助都将不胜感激.

Any kind of help will be deeply appreciated.

推荐答案

我们最近遇到了同样的问题，由于参数组合不正确.此错误是由您的 Web 实例尝试使用不再有效的连接引起的.

We encountered the same problem recently, due to a combination of incorrect parameters. This error is caused by your web instance trying to use a connection that is no longer a valid one.

这可以通过确保以下参数正确来解决:

This can be resolved by making sure the following parameters are correct:

1. 您已为数据库连接配置了一个验证查询.即 validationQuery="SELECT 1" 在 mariadb 的情况下，在服务器配置中.
2. 您已将 wait_timeout 设置为合理的值.8小时keepAlive似乎有点乐观，我们现在使用的是wait_timeout=180
3. 确保validationInterval(您也可以在服务器配置中设置(如果是tomcat 设置，则为server.xml))设置为低于wait_timeout 值的值.在一种情况下，我将 wait_timeout 减少到 15 秒，validationInterval 值高于此值，这导致错误有时仍然出现.现在它被设置为 validationInterval=60，结合 wait_timeout=180 应该及时捕获任何断开的连接.

1. You have a validationQuery configured for your database connection. i.e. validationQuery="SELECT 1" in case of mariadb, in the server configuration.
2. You have the wait_timeout set to a reasonable value. 8 hours keepAlive seems a bit optimistic, we are now using wait_timeout=180
3. Make sure the validationInterval, which you can also set in the server configuration (server.xml in case of a tomcat setup), is set to a value that is lower than the wait_timeout value. I reduced the wait_timeout to 15s in one case, with the validationInterval value being higher than that, which caused the error to still appear at times. Now it is set to validationInterval=60, combined with the wait_timeout=180 which should catch any broken connections in time.

这篇关于从 MySql 迁移:MariaDB 服务器意外关闭客户端连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！