从MySql迁移：MariaDB服务器意外关闭客户端连接 [英] Migrating from MySql: MariaDB server closing client connections unexpectedly

问题描述

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

$ b 我们已经成功地用MariaDB客户端jar替换了MySql连接器jar（第一个更改），现在正试图用MariaDB服务器替换MySql服务器而不更改数据文件。

我们的所有应用程序都运行完好约8-12小时，之后我们看到以下例外：

---

  org.springframework.transaction.CannotCreateTransactionException：无法打开Hibernate交易会议;嵌套异常是org.hibernate.exception.JDBCConnectionException：无法打开连接
原因：
 org.hibernate.exception.JDBCConnectionException：无法在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）
在org.hibernate.jdbc.ConnectionManager.openConnection（ConnectionManager.java:426）
在org.hibernate.jdbc.ConnectionManager.getConnection（ConnectionManager.java:144）
在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）
在org.springframework.orm.hibernate3.HibernateTransactionManager.do开始（HibernateTransactionManager.java:494）
 at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction（AbstractPlatformTransactionManager.java:315）
 at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary（TransactionAspectSupport。 
在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.DelegatingMethodAccessorI mpl.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）
导致：java.sql.SQLNonTransientConnectionException：无法连接到本地主机：3306：意外的流结束，从4 
读取0bytes org.mariadb.jdbc.internal.SQLExceptionMapper.get（SQLExceptionMapper.java:136）
 at org.mariadb.jdbc.inter nal.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。 
 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 
引起：org.mariadb.jdbc.internal.common.QueryException：无法连接ect到localhost：3306：流的意外结束，从org.mariadb.jdbc.internal.mysql.MySQLProtocol.connect（MySQLProtocol.java:509）处的$ 4 $ b读取0bytes $ or $ $ $ $ org.mariadb.jdbc .internal.mysql.MySQLProtocol.connect（MySQLProtocol.java:669）
位于org.mariadb.jdbc.internal.mysql.MySQLProtocol。< init>（MySQLProtocol.java:264）
 at org。 mariadb.jdbc.Driver.connect（Driver.java:110）
 ... 28 more 
导致：java.io.EOFException：流的意外结束，从4 
读取0bytes 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）
，位于org.mariadb.jdbc.internal.mysql.MySQLProtocol。 connect（MySQLProtocol.java:467）
 ... 31 more 
  

---

如果再次使用MySql服务器，异常消失。

从我调试到目前为止MariaDB服务器正在关闭客户端连接。我最初怀疑有空闲连接，但我们使用的是像'testOnBorrow'这样的Hibernate配置，因此它不应该是这样。

任何人都可以帮助我们找出原因这并帮助我们解决问题？ MariaDB中有任何特定的配置，我应该做什么？

我甚至通过增加MariaDB服务器中param'interactive_timeout'的值来运行我们的应用程序，但是它没有帮助。

顺便说一句，我们使用Spring-Hibernate并使用Commons-pool jar来连接我们的连接。

解决方案

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

这可以通过确保以下参数来解决是正确的：

1. 你有一个为数据库连接配置的验证查询。例如，在服务器配置中使用mariadb时， validationQuery =SELECT 1。
   
2. 您将wait_timeout设置为合理的值。 8小时keepAlive似乎有点乐观，我们现在使用 wait_timeout = 180


3. 确保您可以设置的validationInterval服务器配置（在Tomcat设置的情况下为server.xml）设置为低于wait_timeout值的值。在一种情况下，我将wait_timeout减少到15秒，其中validationInterval值高于此值，导致错误仍然有时显示。
   现在它被设置为 validationInterval = 60 ，加上 wait_timeout = 180 及时连接。

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

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.

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

---

The exceptions disappear if MySql server is used again.

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.

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?

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

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

Any kind of help will be deeply appreciated.

解决方案

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. 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服务器意外关闭客户端连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！