4 Replies Latest reply on Feb 25, 2013 11:42 AM by Mircea Markus

    MySQL drops connections after 8 hours...

    Edoardo Schepis Newbie

      I see the following error after some inactivity of my Infinispan clients. Infinispan 5.1.6 is using MySQL as cachestore.

      Googling around it seems that the suggested "autoReconnect=true" property couldn't work (http://hibernatedb.blogspot.it/2009/05/automatic-reconnect-from-hibernate-to.html).

      Any suggestion? Do I have some control on these settings from Infinispan?

       

      Thanks

      Edoardo

       

       

      ERROR [org.infinispan.loaders.jdbc.stringbased.JdbcStringBasedCacheStore] (http--127.8.96.129-8080-2) ISPN008024: Error while storing string key to database; key: 'EC:85:2F:03:B3:CC', buffer size of value: 133 bytes: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 210,721,446 milliseconds ago.  The last packet sent successfully to the server was 210,721,446 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.

                at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) [rt.jar:1.7.0_09-icedtea]

                at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57) [rt.jar:1.7.0_09-icedtea]

                at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) [rt.jar:1.7.0_09-icedtea]

                at java.lang.reflect.Constructor.newInstance(Constructor.java:525) [rt.jar:1.7.0_09-icedtea]

                at com.mysql.jdbc.Util.handleNewInstance(Util.java:411) [mysql-connector-java-5.1.22-bin.jar:]

                at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1117) [mysql-connector-java-5.1.22-bin.jar:]

                at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3851) [mysql-connector-java-5.1.22-bin.jar:]

                at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2471) [mysql-connector-java-5.1.22-bin.jar:]

                at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2651) [mysql-connector-java-5.1.22-bin.jar:]

                at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2734) [mysql-connector-java-5.1.22-bin.jar:]

                at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155) [mysql-connector-java-5.1.22-bin.jar:]

                at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2458) [mysql-connector-java-5.1.22-bin.jar:]

                at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2375) [mysql-connector-java-5.1.22-bin.jar:]

                at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2359) [mysql-connector-java-5.1.22-bin.jar:]

                at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:105) [c3p0-0.9.1.2.jar:0.9.1.2]

                at org.infinispan.loaders.jdbc.stringbased.JdbcStringBasedCacheStore.storeLockSafe(JdbcStringBasedCacheStore.java:243) [infinispan-cachestore-jdbc.jar:5.1.6.FINAL]

                at org.infinispan.loaders.jdbc.stringbased.JdbcStringBasedCacheStore.storeLockSafe(JdbcStringBasedCacheStore.java:87) [infinispan-cachestore-jdbc.jar:5.1.6.FINAL]

                at org.infinispan.loaders.LockSupportCacheStore.store(LockSupportCacheStore.java:207) [infinispan-core-5.1.6.FINAL.jar:5.1.6.FINAL]

                at org.infinispan.interceptors.CacheStoreInterceptor.visitPutKeyValueCommand(CacheStoreInterceptor.java:235) [infinispan-core-5.1.6.FINAL.jar:5.1.6.FINAL]

                at org.infinispan.commands.write.PutKeyValueCommand.acceptVisitor(PutKeyValueCommand.java:77) [infinispan-core-5.1.6.FINAL.jar:5.1.6.FINAL]

                at org.infinispan.interceptors.base.CommandInterceptor.invokeNextInterceptor(CommandInterceptor.java:116) [infinispan-core-5.1.6.FINAL.jar:5.1.6.FINAL]

                at org.infinispan.interceptors.CacheLoaderInterceptor.visitPutKeyValueCommand(CacheLoaderInterceptor.java:90) [infinispan-core-5.1.6.FINAL.jar:5.1.6.FINAL]

                at org.infinispan.commands.write.PutKeyValueCommand.acceptVisitor(PutKeyValueCommand.java:77) [infinispan-core-5.1.6.FINAL.jar:5.1.6.FINAL]

                at org.infinispan.interceptors.base.CommandInterceptor.invokeNextInterceptor(CommandInterceptor.java:116) [infinispan-core-5.1.6.FINAL.jar:5.1.6.FINAL]

                at org.infinispan.interceptors.EntryWrappingInterceptor.invokeNextAndApplyChanges(EntryWrappingInterceptor.java:212) [infinispan-core-5.1.6.FINAL.jar:5.1.6.FINAL]

                at org.infinispan.interceptors.EntryWrappingInterceptor.visitPutKeyValueCommand(EntryWrappingInterceptor.java:147) [infinispan-core-5.1.6.FINAL.jar:5.1.6.FINAL]

                at org.infinispan.commands.write.PutKeyValueCommand.acceptVisitor(PutKeyValueCommand.java:77) [infinispan-core-5.1.6.FINAL.jar:5.1.6.FINAL]

                at org.infinispan.interceptors.base.CommandInterceptor.invokeNextInterceptor(CommandInterceptor.java:116) [infinispan-core-5.1.6.FINAL.jar:5.1.6.FINAL]

                at org.infinispan.interceptors.locking.NonTransactionalLockingInterceptor.visitPutKeyValueCommand(NonTransactionalLockingInterceptor.java:68) [infinispan-core-5.1.6.FINAL.jar:5.1.6.FINAL]

                at org.infinispan.commands.write.PutKeyValueCommand.acceptVisitor(PutKeyValueCommand.java:77) [infinispan-core-5.1.6.FINAL.jar:5.1.6.FINAL]

                at org.infinispan.interceptors.base.CommandInterceptor.invokeNextInterceptor(CommandInterceptor.java:116) [infinispan-core-5.1.6.FINAL.jar:5.1.6.FINAL]

                at org.infinispan.interceptors.base.CommandInterceptor.handleDefault(CommandInterceptor.java:130) [infinispan-core-5.1.6.FINAL.jar:5.1.6.FINAL]

                at org.infinispan.commands.AbstractVisitor.visitPutKeyValueCommand(AbstractVisitor.java:62) [infinispan-core-5.1.6.FINAL.jar:5.1.6.FINAL]

                at org.infinispan.commands.write.PutKeyValueCommand.acceptVisitor(PutKeyValueCommand.java:77) [infinispan-core-5.1.6.FINAL.jar:5.1.6.FINAL]

                at org.infinispan.interceptors.base.CommandInterceptor.invokeNextInterceptor(CommandInterceptor.java:116) [infinispan-core-5.1.6.FINAL.jar:5.1.6.FINAL]

                at org.infinispan.interceptors.InvocationContextInterceptor.handleAll(InvocationContextInterceptor.java:132) [infinispan-core-5.1.6.FINAL.jar:5.1.6.FINAL]

                at org.infinispan.interceptors.InvocationContextInterceptor.handleDefault(InvocationContextInterceptor.java:91) [infinispan-core-5.1.6.FINAL.jar:5.1.6.FINAL]

                at org.infinispan.commands.AbstractVisitor.visitPutKeyValueCommand(AbstractVisitor.java:62) [infinispan-core-5.1.6.FINAL.jar:5.1.6.FINAL]

                at org.infinispan.commands.write.PutKeyValueCommand.acceptVisitor(PutKeyValueCommand.java:77) [infinispan-core-5.1.6.FINAL.jar:5.1.6.FINAL]

                at org.infinispan.interceptors.InterceptorChain.invoke(InterceptorChain.java:345) [infinispan-core-5.1.6.FINAL.jar:5.1.6.FINAL]

                at org.infinispan.CacheImpl.executeCommandAndCommitIfNeeded(CacheImpl.java:1006) [infinispan-core-5.1.6.FINAL.jar:5.1.6.FINAL]

                at org.infinispan.CacheImpl.put(CacheImpl.java:702) [infinispan-core-5.1.6.FINAL.jar:5.1.6.FINAL]

                at org.infinispan.CacheImpl.put(CacheImpl.java:694) [infinispan-core-5.1.6.FINAL.jar:5.1.6.FINAL]

                at org.infinispan.rest.Server.org$infinispan$rest$Server$$putInCache(Server.scala:150) [classes:]

                at org.infinispan.rest.Server$$anonfun$putEntry$1.apply(Server.scala:133) [classes:]

                at org.infinispan.rest.Server$$anonfun$putEntry$1.apply(Server.scala:120) [classes:]

                at org.infinispan.rest.Server.protectCacheNotFound(Server.scala:217) [classes:]

                at org.infinispan.rest.Server.putEntry(Server.scala:120) [classes:]

                at sun.reflect.GeneratedMethodAccessor10.invoke(Unknown Source) [:1.7.0_09-icedtea]

                at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.7.0_09-icedtea]

                at java.lang.reflect.Method.invoke(Method.java:601) [rt.jar:1.7.0_09-icedtea]

                at org.jboss.resteasy.core.MethodInjectorImpl.invoke(MethodInjectorImpl.java:155) [resteasy-jaxrs-2.3.1.GA.jar:]

                at org.jboss.resteasy.core.ResourceMethod.invokeOnTarget(ResourceMethod.java:257) [resteasy-jaxrs-2.3.1.GA.jar:]

                at org.jboss.resteasy.core.ResourceMethod.invoke(ResourceMethod.java:222) [resteasy-jaxrs-2.3.1.GA.jar:]

                at org.jboss.resteasy.core.ResourceMethod.invoke(ResourceMethod.java:211) [resteasy-jaxrs-2.3.1.GA.jar:]

                at org.jboss.resteasy.core.SynchronousDispatcher.getResponse(SynchronousDispatcher.java:525) [resteasy-jaxrs-2.3.1.GA.jar:]

                at org.jboss.resteasy.core.SynchronousDispatcher.invoke(SynchronousDispatcher.java:502) [resteasy-jaxrs-2.3.1.GA.jar:]

                at org.jboss.resteasy.core.SynchronousDispatcher.invoke(SynchronousDispatcher.java:119) [resteasy-jaxrs-2.3.1.GA.jar:]

                at org.jboss.resteasy.plugins.server.servlet.ServletContainerDispatcher.service(ServletContainerDispatcher.java:208) [resteasy-jaxrs-2.3.1.GA.jar:]

                at org.jboss.resteasy.plugins.server.servlet.HttpServletDispatcher.service(HttpServletDispatcher.java:55) [resteasy-jaxrs-2.3.1.GA.jar:]

                at org.jboss.resteasy.plugins.server.servlet.HttpServletDispatcher.service(HttpServletDispatcher.java:50) [resteasy-jaxrs-2.3.1.GA.jar:]

                at javax.servlet.http.HttpServlet.service(HttpServlet.java:847) [jboss-servlet-api_3.0_spec-1.0.0.Final.jar:1.0.0.Final]

                at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:329) [jbossweb-7.0.10.Final.jar:]

                at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:248) [jbossweb-7.0.10.Final.jar:]

                at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:275) [jbossweb-7.0.10.Final.jar:]

                at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:161) [jbossweb-7.0.10.Final.jar:]

                at org.jboss.as.web.security.SecurityContextAssociationValve.invoke(SecurityContextAssociationValve.java:154) [jboss-as-web-7.1.0.Final.jar:7.1.0.Final]

                at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:155) [jbossweb-7.0.10.Final.jar:]

                at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102) [jbossweb-7.0.10.Final.jar:]

                at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109) [jbossweb-7.0.10.Final.jar:]

                at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:368) [jbossweb-7.0.10.Final.jar:]

                at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:877) [jbossweb-7.0.10.Final.jar:]

                at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:671) [jbossweb-7.0.10.Final.jar:]

                at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:930) [jbossweb-7.0.10.Final.jar:]

                at java.lang.Thread.run(Thread.java:722) [rt.jar:1.7.0_09-icedtea]

      Caused by: java.net.SocketException: Broken pipe

                at java.net.SocketOutputStream.socketWrite0(Native Method) [rt.jar:1.7.0_09-icedtea]

                at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:109) [rt.jar:1.7.0_09-icedtea]

                at java.net.SocketOutputStream.write(SocketOutputStream.java:153) [rt.jar:1.7.0_09-icedtea]

                at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82) [rt.jar:1.7.0_09-icedtea]

                at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:140) [rt.jar:1.7.0_09-icedtea]

                at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3832) [mysql-connector-java-5.1.22-bin.jar:]

                ... 68 more

        • 1. Re: MySQL drops connections after 8 hours...
          Sanne Grinovero Master

          Hi Edoardo,

          Infinispan doesn't implement a data source validity checker: it's best to use a properly pooled and monitored DataSource, for example by using the one in JBoss so that it can pre-check validity of connections.

          • 2. Re: MySQL drops connections after 8 hours...
            gibsosmat Newbie

            from your logs I guess you are using c3p0 connection pooled datasource. try setting your idleConnectionTestPeriod of c3p0 pool to a value less than the wait_timeout setting on db. so c3p0 keeps the pooled & uncheckedout connections 'alive' by firing test query, hence db dosent kill the idle connection.

            • 3. Re: MySQL drops connections after 8 hours...
              Edoardo Schepis Newbie

              Hi Sandeep/Sanne,

              thanks for your help.

              Sandeep how can I setup the idleConnectionTestPeriod of c3p0 pool?

              I'm just using infinispan.xml and don't know where to set properties of the c3p0 pool and actually don't know if I can touch those properties that are in charge of infinispan.


              Thanks

              Edoardo

               

               

              ----

              <namedCache name="openvoters">

                <loaders passivation="false" shared="false">

                <loader

                class="org.infinispan.loaders.jdbc.stringbased.JdbcStringBasedCacheStore"

                fetchPersistentState="false" ignoreModifications="false"

                purgeOnStartup="false">

                <properties>

                <property name="databaseType" value="MYSQL" />

                <property name="stringsTableNamePrefix" value="ISPN_STRING_TABLE" />

                <property name="idColumnName" value="ID_COLUMN" />

                <property name="idColumnType" value="VARCHAR(255)" />

               

                <property name="dataColumnName" value="DATA_COLUMN" />

                <property name="dataColumnType" value="BLOB" />

               

                <!-- -->

                <property name="timestampColumnName" value="TIMESTAMP_COLUMN" />

                <property name="timestampColumnType" value="BIGINT" />

               

                <property name="connectionFactoryClass"

                value="org.infinispan.loaders.jdbc.connectionfactory.PooledConnectionFactory" />

                <property name="connectionUrl" value="jdbc:mysql://127.8.96.129:3306/votoio?autoReconnect=true" />

               

                <property name="userName" value="admin" />

                <property name="password" value="xxx-b" />

               

                <property name="driverClass" value="com.mysql.jdbc.Driver" />

               

                <property name="dropTableOnExit" value="false" />

                <property name="createTableOnStart" value="true" />

                </properties>

               

                </loader>

                </loaders>

              </namedCache>