Further MySQL woes - possible timeouts again

[James Green <ja...@gmail.com>]

One of our servers (our "live" environment) has "fallen over" two days in a
row. It seems to be something to do with the MySQL connectivity being lost.

Caused by:
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No
operations allowed after connection closed.
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native
Method)
        at
sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
        at
sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:525)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
        at com.mysql.jdbc.Util.getInstance(Util.java:386)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1014)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:988)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:974)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:919)
        at
com.mysql.jdbc.ConnectionImpl.throwConnectionClosedException(ConnectionImpl.java:1290)
        at
com.mysql.jdbc.ConnectionImpl.checkClosed(ConnectionImpl.java:1282)
        at
com.mysql.jdbc.ConnectionImpl.setAutoCommit(ConnectionImpl.java:5236)
        at sun.reflect.GeneratedMethodAccessor139.invoke(Unknown Source)
        at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:601)
        at
org.apache.tomcat.jdbc.pool.ProxyConnection.invoke(ProxyConnection.java:126)
        at
org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:109)
        at
org.apache.tomcat.jdbc.pool.DisposableConnectionFacade.invoke(DisposableConnectionFacade.java:80)
        at com.sun.proxy.$Proxy109.setAutoCommit(Unknown Source)
        at
org.apache.openejb.resource.jdbc.managed.local.LocalXAResource.start(LocalXAResource.java:66)
        ... 76 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException:
Communications link failure

The last packet successfully received from the server was 35,780,425
milliseconds ago. The last packet sent successfully to the server was 1
milliseconds ago.
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native
Method)
        at
sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
        at
sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:525)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
        at
com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1117)
        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3603)
        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3492)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4043)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2503)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2664)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2788)
        at
com.mysql.jdbc.ConnectionImpl.setAutoCommit(ConnectionImpl.java:5282)
        ... 84 more
Caused by: java.io.EOFException: Can not read response from server.
Expected to read 4 bytes, read 0 bytes before connection was unexpectedly
lost.
        at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:3052)
        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3503)
        ... 90 more

Reading around this is quite common although I've so far failed to
reproduce on internal kit. I am however trying to configure the tomee.xml
Resource instances to validate the connection with a query and set up
testing of idle pooled connections. So far I have as follows:

<Resource id="jdbc/accounts" type="DataSource">
  JdbcDriver com.mysql.jdbc.Driver
  JdbcUrl jdbc:mysql://saas-dispatch.qa.int/account
  UserName saas
  Password saas
  JtaManaged true
  ConnectionProperties zeroDateTimeBehavior=convertToNull
  DataSourceCreator = tomcat
  # See also http://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html
  driverClassName = com.mysql.jdbc.Driver
  validationQuery = SELECT 1
  jmxEnabled = true # specific to tomcat pooling
  logAbandoned = true # During low volumes only
  testWhileIdle = true
</Resource>

Is there anything else I should be looking at to close this issue down?

Behaviourally it fails on the first invocation of a connection that was
last used several hours previously. We know this because our connections go
idle overnight.

Unfortunately the only way to wake our server up is to restart TomEE once
this exception has been thrown so obviously it needs fixing.

Thanks,

James

Re: Further MySQL woes - possible timeouts again

[Anthony Fryer <ap...@hotmail.com>]

Maybe try the autoReconnect=true option at the end of the JdbcUrl

eg.

<Resource id="MySQL Database" type="DataSource">
  JdbcDriver                  com.mysql.jdbc.Driver
  JdbcUrl                      
jdbc:mysql://localhost:3306/lemonadeStand?autoReconnect=true
  UserName                   lc
  Password                   lc
  JtaManaged                true
  MaxActive                  10
  MaxIdle                      5
  MinIdle                       5
  TimeBetweenEvictionRunsMillis	600000
  NumTestsPerEvictionRun        5
  ValidationQuery               select 1
  TestWhileIdle                 true
</Resource>



--
View this message in context: http://openejb.979440.n4.nabble.com/Further-MySQL-woes-possible-timeouts-again-tp4664233p4664236.html
Sent from the OpenEJB User mailing list archive at Nabble.com.