random Error calling Connection.setAutoCommit
PeiSong
PeiSong
Here is the application error log:
DEBUG: java.sql.Connection - xxx Connection Closed
INFO : asia.glbt.dev.dao.mybatis.BaseDao - session closed.
DEBUG: asia.glbt.dev.dao.mybatis.ProfileDao - ** get top hearted users by country & page
DEBUG: asia.glbt.dev.web.admin.controller.HomepageController - getting top hearted users.
DEBUG: java.sql.Connection - ooo Connection Opened
ERROR: java.sql.Connection - Error calling Connection.setAutoCommit:
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
The last packet successfully received from the server was 143,594 milliseconds ago. The last packet sent successfully to the server was 2 milliseconds ago.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1118)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3055)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2941)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3489)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2113)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2562)
at com.mysql.jdbc.ConnectionImpl.setAutoCommit(ConnectionImpl.java:4956)
at com.mysql.jdbc.jdbc2.optional.ConnectionWrapper.setAutoCommit(ConnectionWrapper.java:155)
at com.sun.gjc.spi.base.ConnectionHolder.setAutoCommit(ConnectionHolder.java:624)
at sun.reflect.GeneratedMethodAccessor179.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.apache.ibatis.logging.jdbc.ConnectionLogger.invoke(ConnectionLogger.java:52)
at $Proxy105.setAutoCommit(Unknown Source)
at org.apache.ibatis.transaction.jdbc.JdbcTransaction.setDesiredAutoCommit(JdbcTransaction.java:42)
at org.apache.ibatis.transaction.jdbc.JdbcTransaction.<init>(JdbcTransaction.java:15)
at org.apache.ibatis.transaction.jdbc.JdbcTransactionFactory.newTransaction(JdbcTransactionFactory.java:15)
at org.apache.ibatis.session.defaults.DefaultSqlSessionFactory.openSessionFromDataSource(DefaultSqlSessionFactory.java:77)
at org.apache.ibatis.session.defaults.DefaultSqlSessionFactory.openSession(DefaultSqlSessionFactory.java:32)
at asia.glbt.dev.dao.mybatis.BaseDao.getSession(BaseDao.java:15)
at asia.glbt.dev.dao.mybatis.BaseDao.getMapper(BaseDao.java:19)
at asia.glbt.dev.dao.mybatis.ProfileDao.getTopHeartedUsersByCountryWithPage(ProfileDao.java:21)
at asia.glbt.dev.service.common.db.ProfileService.getTopHeartedUsersByCountryWithPage(ProfileService.java:128)
at asia.glbt.dev.web.admin.controller.HomepageController.getTopHeartedUsers(HomepageController.java:36)
at sun.reflect.GeneratedMethodAccessor190.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.springframework.web.bind.annotation.support.HandlerMethodInvoker.invokeHandlerMethod(HandlerMethodInvoker.java:175)
... 79 more
ERROR: asia.glbt.dev.dao.mybatis.ProfileDao - Error configuring AutoCommit. Your driver may not support getAutoCommit() or setAutoCommit(). Requested setting: false. Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
PeiSong
François Schiettecatte
I have the mysql timeouts set thus:
# Connection timeouts
connect_timeout = 10
wait_timeout = 60
interactive_timeout = 3600
and my c3p0 connection pool timeout set thus:
<property name="maxIdleTime" value="50"/>
<property name="maxIdleTimeExcessConnections" value="50"/>
the theory being that c3p0 will drop idle connection from the pool before mysql closed it.
Cheers
François
PeiSong
The wait_timeout = 60 means if there is no action in 60 seconds the connection will also be closed. So I'm wondering how those connection remains.
If so, the connection should do sth like select system variable every a few seconds right?
PeiSong
wait_timeout applies for tcp/ip connections.
so here wait_timeout rules.
also from glassfish wiki:
idle-timeout-in-seconds
maximum time in seconds, that a connection can remain idle in
the pool. After this time, the pool implementation can close
this connection. ===>Note that this does not control connection
timeouts enforced at the database server side<=======. Adminsitrators
are advised to keep this timeout shorter than the EIS
connection timeout (if such timeouts are configured on the
specific EIS), to prevent accumulation of unusable connection
in Application Server.
François Schiettecatte
You have two sets of timeouts, one at the mysql end which are defined in /etc/my.cnf, there are the ones I use:
# Connection timeouts
connect_timeout = 10
wait_timeout = 60
interactive_timeout = 3600
The important one is the wait_timeout which is the amount of time mysql idles before dropping the connection automatically, in this case 60 seconds. The connect_timeout is the amount of time mysql waits for a user name/password upon connection, and the interactive_timeout is for interactive apps like command line mysql. There are other network level timeouts but these are out of scope. You can get a lot of information about all this here:
http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html
http://dev.mysql.com/doc/refman/5.1/en/server-options.html
http://dev.mysql.com/doc/refman/5.1/en/option-files.html
At the other end, there are the connection pool timeout and how you define these is pool specific. I use C3P0 because I like it. I had to write a small amount of code to pull configuration values out of the ibatis xml configuration file, the two settings that C3P0 uses which are important to us are:
<property name="maxIdleTime" value="50"/>
<property name="maxIdleTimeExcessConnections" value="50"/>
This tells C3P0 to drop idle connections after 50 seconds, pretty much ensuring that these will be dropped before mysql drops them causing the error you see. I did some testing and 50 seconds seemed to be the value closest to 60 I could use without causing the error you see.
You can read more about the C3P0 settings here:
http://www.mchange.com/projects/c3p0/
I don't know which connection pool you are using but I expect you will find similar settings to control the connection timeouts, I used DBCP beforehand and I recall it worked pretty much the same way.
If you want to get fancy you can also use some sort of 'ping' to keep idle connections alive (using a query such as 'select 1' on mysql). This would make sense in an environment where opening a connection is expensive, but this is not the case with mysql so I don't bother.
Cheers
François