Timeout for connection to repository

[markmaslow]

 If the server is not accessed for a period of time, it seems to loose its connection to the repository.

Messages in log:

org.springframework.transaction.CannotCreateTransactionException: Could not open Hibernate Session for transaction; nested exception is org.hibernate.TransactionException: JDBC begin failed: 

com.mysql.jdbc.CommunicationsException: The last packet successfully received from the server was 62,798,203 milliseconds ago.  The last packet sent successfully to the server was 62,798,203 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 this point, it seems that the only way to get rid of the message is to restart the Tomcat service.

What is the recommended solution to this problem?

[lucianc]

Set a validation query for the JNDI data source.  Some details at http://jasperforge.org/plugins/espforum/view.php?group_id=112&forumid=102&topicid=41903

Regards,

Lucian

[tkavanagh]

There is also a sample of a validation query (and additional settings) in a context.xml in the release packages (for RC 3.7.0) and in the source code.

 

Location is:

  <js-install>/ or <js-src>/

      scripts/extra-configs/context-w-val-query.xml

 

This has a setup like so:

 

    <Resource name="jdbc/jasperserver" auth="Container" type="javax.sql.DataSource"
        maxActive="100" maxIdle="30" maxWait="10000"
        username="root" password="password"
        driverClassName="com.mysql.jdbc.Driver"
        validationQuery="select 1"
        testOnBorrow="true"
        url="jdbc:mysql://localhost:3306/jasperserver?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&autoReconnectForPools=true"/>
 

[highjo]

lucianc
Wrote:

Set a validation query for the JNDI data source.  Some details at http://jasperforge.org/plugins/espforum/view.php?group_id=112&forumid=102&topicid=41903 Regards, Lucian

hello lucianc and tkavanagh

i seem to be having the same wait_timeout error and here is basically what's in my log

 

org.quartz.SchedulerException: Job threw an unhandled exception. [see nested exception: org.springframework.transaction.CannotCreateTransactionException: Could not open Hibernate Session for transaction; nested exception is org.hibernate.TransactionException: JDBC begin failed: ]	at org.quartz.core.JobRunShell.run(JobRunShell.java:206)	at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:520)* Nested Exception (Underlying Cause) ---------------org.springframework.transaction.CannotCreateTransactionException: Could not open Hibernate Session for transaction; nested exception is org.hibernate.TransactionException: JDBC begin failed: 	at org.springframework.orm.hibernate3.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:599)	at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:374)	at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:263)	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:101)	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)	at $Proxy3.loadUserByUsername(Unknown Source)	at com.jaspersoft.jasperserver.api.engine.common.service.impl.AcegiSecurityContextProvider.setAuthenticatedUser(AcegiSecurityContextProvider.java:88)	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)	at java.lang.reflect.Method.invoke(Unknown Source)	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307)	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:198)	at $Proxy6.setAuthenticatedUser(Unknown Source)	at com.jaspersoft.jasperserver.api.engine.scheduling.quartz.ReportExecutionJob.execute(ReportExecutionJob.java:228)	at org.quartz.core.JobRunShell.run(JobRunShell.java:195)	at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:520)Caused by: org.hibernate.TransactionException: JDBC begin failed: 	at org.hibernate.transaction.JDBCTransaction.begin(JDBCTransaction.java:96)	at org.hibernate.impl.SessionImpl.beginTransaction(SessionImpl.java:1354)	at org.springframework.orm.hibernate3.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:558)	... 17 moreCaused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 42,578,794 milliseconds ago.  The last packet sent successfully to the server was 42,578,794 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)	at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)	at java.lang.reflect.Constructor.newInstance(Unknown Source)	at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)	at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1074)	at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3313)	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1940)	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2109)	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2642)	at com.mysql.jdbc.ConnectionImpl.setAutoCommit(ConnectionImpl.java:5034)	at org.apache.tomcat.dbcp.dbcp.DelegatingConnection.setAutoCommit(DelegatingConnection.java:331)	at org.apache.tomcat.dbcp.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.setAutoCommit(PoolingDataSource.java:317)	at org.hibernate.transaction.JDBCTransaction.begin(JDBCTransaction.java:91)	... 19 moreCaused by: java.net.SocketException: Software caused connection abort: socket write error	at java.net.SocketOutputStream.socketWrite0(Native Method)	at java.net.SocketOutputStream.socketWrite(Unknown Source)	at java.net.SocketOutputStream.write(Unknown Source)	at java.io.BufferedOutputStream.flushBuffer(Unknown Source)	at java.io.BufferedOutputStream.flush(Unknown Source)	at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3294)	... 26 more

i've read the thread posted by lucianc and made necessary changes and restarted the tomcat server there is my context:

 <Resource name="jdbc/jasperserver" auth="Container" type="javax.sql.DataSource"
        maxActive="100" maxIdle="30" maxWait="180000"
        username="root" password=""
        validationQuery="select count(*) from JIRole"
        driverClassName="com.mysql.jdbc.Driver"
        url="jdbc:mysql://localhost:3306/jasperserver?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&autoReconnectForPools=true"  />

    <Resource name="jdbc/sugarcrm" auth="Container" type="javax.sql.DataSource"
        maxActive="100" maxIdle="30" maxWait="10000"
        username="root" password=""
        driverClassName="com.mysql.jdbc.Driver"
        url="jdbc:mysql://localhost:3306/sugarcrm?autoReconnect=true"/>

    <Resource name="jdbc/foodmart" auth="Container" type="javax.sql.DataSource"
        maxActive="100" maxIdle="30" maxWait="10000"
        username="root" password=""
        driverClassName="com.mysql.jdbc.Driver"
        url="jdbc:mysql://localhost:3306/foodmart?autoReconnect=true"/>
 

 

 

and that doesnt seem to have affected the server.But i had to say that the report itself runs fine and when the scheduled one failed given the error i've posted, i send the report manually by clicking on run now

and it works fine with no entry in the log. now should i add the properties bellow to the jdbc-connection-pool-params element in the jasperserverDS-jdbc.xml as suggested by developerrude in lucianc's link thread or just add wait_time out to mysql my.cnf ?

# Here follows entries for some specific programs # The MySQL server[mysqld]port= 3306socket= "C:/xampp/mysql/mysql.sock"basedir="C:/xampp/mysql" tmpdir="C:/xampp/tmp" datadir="C:/xampp/mysql/data"skip-lockingkey_buffer = 16Mmax_allowed_packet = 1Mtable_cache = 64sort_buffer_size = 512Knet_buffer_length = 8Kread_buffer_size = 256Kread_rnd_buffer_size = 512Kmyisam_sort_buffer_size = 8M# commented in by lampp security#skip-networkingskip-federatedserver-id	= 1#skip-innodbinnodb_data_home_dir = "C:/xampp/mysql/"innodb_data_file_path = ibdata1:10M:autoextendinnodb_log_group_home_dir = "C:/xampp/mysql/"#innodb_log_arch_dir = "C:/xampp/mysql/"## You can set .._buffer_pool_size up to 50 - 80 %## of RAM but beware of setting memory usage too highinnodb_buffer_pool_size = 16Minnodb_additional_mem_pool_size = 2M## Set .._log_file_size to 25 % of buffer pool sizeinnodb_log_file_size = 5Minnodb_log_buffer_size = 8Minnodb_flush_log_at_trx_commit = 1innodb_lock_wait_timeout = 50[mysqldump]quickmax_allowed_packet = 16M[mysql]no-auto-rehash# Remove the next comment character if you are not familiar with SQL#safe-updates[isamchk]key_buffer = 20Msort_buffer_size = 20Mread_buffer = 2Mwrite_buffer = 2M[myisamchk]key_buffer = 20Msort_buffer_size = 20Mread_buffer = 2Mwrite_buffer = 2M[mysqlhotcopy]interactive-timeout

we are using xampp for this.it's a work server so i've been told xampp is fine for the purpose. One thing is sure, i've started getting pressure from management so i'would like to make this solved as soon as possible.thanks for reading he who help me out will be my hero thanks again

 

[highjo]

anyone?