AnsweredAssumed Answered

How to resolve MySQL connection death after 8 hours?

Question asked by Natalie Sus on Dec 4, 2017
Latest reply on Dec 28, 2017 by Diego Mainou

Hi all!

 

Could you please let me know how you solve JNDI connection error? I get next errors:

 

#1. SQLBaseComponent.ERROR_0006 - Could not execute test.xaction

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.

 

#2. Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 236,949,355 milliseconds ago.  The last packet sent successfully to the server was 236,949,361 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.

 

I've read:

- How to use c3p0 driver in kettle

- Configuring for MySQL - BI Platform - Pentaho Wiki

- BI Server | Interesting IT Tip's

etc

and all articles I found suggest the same - to use c3p0

 

It looks like c3p0 is installed with Pentaho BI:

- /usr/share/tomcat/webapps/pentaho/WEB-INF/lib/hibernate-c3p0-3.6.9.Final.jar

- /usr/share/tomcat/webapps/pentaho/WEB-INF/lib/c3p0-0.9.1.2.jar

So I modified pentaho-solutions/system/hibernate/mysql5.hibernate.cfg.xml this way:

pentaho-solutions/system/hibernate/mysql5.hibernate.cfg.xml - session-factory content

<session-factory>

<!-- hibernate c3p0 settings -->

<property name="connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>

<property name="hibernate.c3p0.acquire_increment">3</property>

<property name="hibernate.c3p0.idle_test_period">10</property>

<property name="hibernate.c3p0.min_size">5</property>

<property name="hibernate.c3p0.max_size">75</property>

<property name="hibernate.c3p0.max_statements">0</property>

<property name="hibernate.c3p0.timeout">25200</property>

<property name="hibernate.c3p0.preferredTestQuery">select 1</property>

<property name="hibernate.c3p0.testConnectionOnCheckout">true</property>

<property name=”hibernate.c3p0.idleConnectionTestPeriod”>300</property>

 

 

 

<property name="cache.provider_class">net.sf.ehcache.hibernate.SingletonEhCacheProvider</property>

<property name="hibernate.generate_statistics">true</property>

<property name="hibernate.cache.use_query_cache">true</property>

 

 

 

<!--  MySQL Configuration -->

<property name="connection.driver_class">com.mysql.jdbc.Driver</property>

<property name="connection.url">jdbc:mysql://projectdb:3306/hibernate?autoReconnect=true</property>

<property name="dialect">org.hibernate.dialect.MySQL5InnoDBDialect</property>

<property name="connection.username">hibuser</property>

<property name="connection.password">password</property>

<property name="connection.pool_size">10</property>

<property name="show_sql">false</property>

<property name="hibernate.jdbc.use_streams_for_binary">true</property>

 

 

 

<!-- replaces DefinitionVersionManager -->

<property name="hibernate.hbm2ddl.auto">update</property>

 

 

 

<!-- load resource from classpath -->

<mapping resource="hibernate/mysql5innodb.hbm.xml" />

</session-factory>

 

I am not sure if it helps, but I also changed connection.url adding "?autoReconnect=true".

 

Regardless what I did, I still get MySQLNonTransientConnectionException and CommunicationsException.

 

Could you please advise where I was wrong? Thank you!

 

Another thing I noticed that after I started to use c3p0, I get:

at java.lang.Thread.run(Thread.java:748)

java.lang.NoClassDefFoundError: Could not initialize class org.pentaho.platform.repository.hibernate.HibernateUtil

at org.pentaho.platform.repository.hibernate.HibernateUtil.systemExitPoint(HibernateUtil.java:695)

Attachments

Outcomes