Java 从 MySql 迁移：MariaDB 服务器意外关闭客户端连接

Question

提问by Vivek

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

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

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.

我们已经成功地用 MariaDB 客户端 jar 替换了 MySql 连接器 jar（第一次更改），现在正在尝试在不更改数据文件的情况下用 MariaDB 服务器替换 MySql 服务器。

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

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

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.

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

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 服务器由于某种原因正在关闭客户端连接。我最初怀疑空闲连接，但我们正在使用像“testOnBorrow”这样的 Hibernate 配置，所以它不应该是这种情况。

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 中是否有我应该做的特定配置？

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

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

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

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

Any kind of help will be deeply appreciated.

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

Answer 1

回答by Vladislav Vaintroub

You can try to increase 'wait_timeout' instead of 'interactive_timeout'. 8-12 hours until error correlates quite well with the default value of wait_timeout which is 10 hours. JDBC driver does not set interactive client flag when connecting, so change to 'interactive_timeout' might have no effect. I also replied to the cross-post in Maria KB ;)

您可以尝试增加“wait_timeout”而不是“interactive_timeout”。8-12 小时直到错误与 wait_timeout 的默认值 10 小时非常相关。JDBC 驱动程序在连接时不会设置交互式客户端标志，因此更改为 'interactive_timeout' 可能无效。我也回复了 Maria KB 中的交叉帖子；)

Answer 2

回答by Rick James

Look at

看着

SHOW VARIABLES LIKE 'max_connections';
SHOW GLOBAL STATUS LIKE '%connect%';

Compare the my.cnf values between the two systems; you may see a difference in things like above that make a difference. If the problem is still not obvious, please provide those values for further discussion.

比较两个系统之间的 my.cnf 值；您可能会发现上述情况有所不同。如果问题仍然不明显，请提供这些值以供进一步讨论。

Answer 3

回答by Filip

According to the findings in this articleyou could get away with two options:

根据本文的调查结果，您可以有两种选择：

You should try turning innodb_file_per_tableoff
or...
Increasing the open file limits, this tells you the current value on yout machine $ ulimit -n, should you be running on high spec machines

你应该尝试innodb_file_per_table关闭
或者...
增加打开文件限制，这会告诉您您机器上的当前值$ ulimit -n，如果您在高规格机器上运行

Answer 4

回答by Stathis Andronikos

I think that the problem is with MariaDB Connector/J. Try to use a stable one like MariaDB Connector/J 1.2.3for example. Also, study Failover behaviourwith Basic failover when autoReconnectis set to true or Standard failover.

我认为问题出在 MariaDB Connector/J 上。例如，尝试使用稳定的版本，例如MariaDB Connector/J 1.2.3。此外，研究在autoReconnect设置为 true 或标准故障转移时使用基本故障转移的故障转移行为。

Answer 5

回答by Jamie De Palmenaer

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.

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

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

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

You have a validationQuery configured for your database connection. i.e. validationQuery="SELECT 1"in case of mariadb, in the server configuration.
You have the wait_timeout set to a reasonable value. 8 hours keepAlive seems a bit optimistic, we are now using wait_timeout=180
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=180which should catch any broken connections in time.

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

Answer 6

回答by Toni_Mashengo

I also had an error like this one Could not connect to localhost:3306: unexpected end of stream, read 0 bytes from 4

我也有这样的错误 Could not connect to localhost:3306: unexpected end of stream, read 0 bytes from 4

My architecture was an webapp of Tomcat that was inserting data into MariaDB.

我的架构是一个 Tomcat 的 webapp，它将数据插入到 MariaDB 中。

I was using the connector version 1.5.1RC! I switched to the 1.3.1 connector versionand now runs fine!

我使用的是1.5.1RC 版本的连接器！我切换到1.3.1 连接器版本，现在运行良好！

Answer 7

回答by user799473

I also faced this issue. The issue goes away with lowering the wait_timeout.

我也遇到过这个问题。降低wait_timeout 后问题就会消失。

SHOW SESSION VARIABLES LIKE 'wait_timeout'; ->> would be 8 hours. which is huge.
SET session wait_timeout=300; ->> this should fix the issue.

Java 从 MySql 迁移：MariaDB 服务器意外关闭客户端连接

提问by Vivek

回答by Vladislav Vaintroub

回答by Rick James

回答by Filip

回答by Stathis Andronikos

回答by Jamie De Palmenaer

回答by Toni_Mashengo

回答by user799473

相关推荐

最近更新

标签

Java 从 MySql 迁移：MariaDB 服务器意外关闭客户端连接

提问by Vivek

回答by Vladislav Vaintroub

回答by Rick James

回答by Filip

回答by Stathis Andronikos

回答by Jamie De Palmenaer

回答by Toni_Mashengo

回答by user799473

相关推荐

Java Eclipse - 调试器不会在断点处停止

如何在JAVA循环中从文件中读取固定数量的字节？

Java 如何创建一个 cmd 文件来运行 jars？

构造函数可以在 Java 中抛出异常吗？

相关推荐

最近更新

标签