Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Migrating from MySql: MariaDB server closing client connections unexpectedly

Tags:

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.

like image 995
Vivek Avatar asked Sep 15 '13 20:09

Vivek


People also ask

Can I migrate from MySQL to MariaDB?

The existing Databases of MySQL need to be backed up to be transferred from MySQL to MariaDB. The command mysqldump can be used to export all the existing databases in the form of files. Binary Logging is required for this command to work, and this can be enabled in the MySQL configurations by modifying my.

How many concurrent connections can MariaDB handle?

MariaDB Max connection by default is configured for connections up to 150, also additionally one more connection is operated for the root access but in case if not used already, thus it becomes 151 connections.

Can MySQL client connect to MariaDB?

You can connect to the MariaDB database from the same computer where it is installed with the mysql client tool. You will be prompted to enter the root user password. This is the same as the application password.


2 Answers

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.
like image 87
Jamie De Palmenaer Avatar answered Oct 01 '22 14:10

Jamie De Palmenaer


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 ;)

like image 41
Vladislav Vaintroub Avatar answered Oct 01 '22 13:10

Vladislav Vaintroub