-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 11 posts ] 
Author Message
 Post subject: SOS! - SQL Error: 0, SQLState: 08S01
PostPosted: Fri Dec 18, 2009 2:49 pm 
Newbie

Joined: Fri Dec 18, 2009 2:28 pm
Posts: 7
I know that there has been much discussion on this topic on the web but even after applying the suggestions on c3p0 configurations, the issue is not getting resolved! I have been struggling with this issue for the last 3-4 days and would really appreciate it if anyone could help me with it.

The application is hosted in a QA environment where it doesn't get accessed for many hours. The symptom of the problem is that right after Tomcat service restart, the application works just fine. But if I access it after several hours (unused overnight), it shows the following exception:
Dispatcher Servlet' processing request for [/OpenClassroom/spring/messagebroker/amf]
[DEBUG] http-9080-Processor25 2009-12-18 06:09:24 (DispatcherServlet.java:doService:781) - DispatcherServlet with name 'Spring MVC

Dispatcher Servlet' processing request for [/OpenClassroom/spring/messagebroker/amf]
[DEBUG] http-9080-Processor25 2009-12-18 06:09:24 (AbstractUrlHandlerMapping.java:getHandlerInternal:176) - Mapping [/messagebroker/amf] to

handler 'flex.messaging.MessageBroker@2d95b3'
[DEBUG] http-9080-Processor25 2009-12-18 06:09:24 (AbstractUrlHandlerMapping.java:getHandlerInternal:176) - Mapping [/messagebroker/amf] to

handler 'flex.messaging.MessageBroker@2d95b3'
[ INFO] http-9080-Processor25 2009-12-18 06:09:24 (MessageBrokerHandlerAdapter.java:handle:76) - Channel endpoint my-amf received request.
[ INFO] http-9080-Processor25 2009-12-18 06:09:24 (MessageBrokerHandlerAdapter.java:handle:76) - Channel endpoint my-amf received request.
[DEBUG] http-9080-Processor25 2009-12-18 06:09:24 (DispatcherServlet.java:doDispatch:909) - Null ModelAndView returned to DispatcherServlet

with name 'Spring MVC Dispatcher Servlet': assuming HandlerAdapter completed request handling
[DEBUG] http-9080-Processor25 2009-12-18 06:09:24 (DispatcherServlet.java:doDispatch:909) - Null ModelAndView returned to DispatcherServlet

with name 'Spring MVC Dispatcher Servlet': assuming HandlerAdapter completed request handling
[DEBUG] http-9080-Processor25 2009-12-18 06:09:24 (FrameworkServlet.java:processRequest:591) - Successfully completed request
[DEBUG] http-9080-Processor25 2009-12-18 06:09:24 (FrameworkServlet.java:processRequest:591) - Successfully completed request
[DEBUG] http-9080-Processor25 2009-12-18 06:09:25 (DispatcherServlet.java:doService:781) - DispatcherServlet with name 'Spring MVC

Dispatcher Servlet' processing request for [/OpenClassroom/spring/messagebroker/amf]
[DEBUG] http-9080-Processor25 2009-12-18 06:09:25 (DispatcherServlet.java:doService:781) - DispatcherServlet with name 'Spring MVC

Dispatcher Servlet' processing request for [/OpenClassroom/spring/messagebroker/amf]
[DEBUG] http-9080-Processor25 2009-12-18 06:09:25 (AbstractUrlHandlerMapping.java:getHandlerInternal:176) - Mapping [/messagebroker/amf] to

handler 'flex.messaging.MessageBroker@2d95b3'
[DEBUG] http-9080-Processor25 2009-12-18 06:09:25 (AbstractUrlHandlerMapping.java:getHandlerInternal:176) - Mapping [/messagebroker/amf] to

handler 'flex.messaging.MessageBroker@2d95b3'
[ INFO] http-9080-Processor25 2009-12-18 06:09:25 (MessageBrokerHandlerAdapter.java:handle:76) - Channel endpoint my-amf received request.
[ INFO] http-9080-Processor25 2009-12-18 06:09:25 (MessageBrokerHandlerAdapter.java:handle:76) - Channel endpoint my-amf received request.
[DEBUG] http-9080-Processor25 2009-12-18 06:09:25 (DispatcherServlet.java:doDispatch:909) - Null ModelAndView returned to DispatcherServlet

with name 'Spring MVC Dispatcher Servlet': assuming HandlerAdapter completed request handling
[DEBUG] http-9080-Processor25 2009-12-18 06:09:25 (DispatcherServlet.java:doDispatch:909) - Null ModelAndView returned to DispatcherServlet

with name 'Spring MVC Dispatcher Servlet': assuming HandlerAdapter completed request handling
[DEBUG] http-9080-Processor25 2009-12-18 06:09:25 (FrameworkServlet.java:processRequest:591) - Successfully completed request
[DEBUG] http-9080-Processor25 2009-12-18 06:09:25 (FrameworkServlet.java:processRequest:591) - Successfully completed request
[DEBUG] http-9080-Processor22 2009-12-18 06:09:25 (DispatcherServlet.java:doService:781) - DispatcherServlet with name 'Spring MVC

Dispatcher Servlet' processing request for [/OpenClassroom/spring/messagebroker/amf]
[DEBUG] http-9080-Processor22 2009-12-18 06:09:25 (DispatcherServlet.java:doService:781) - DispatcherServlet with name 'Spring MVC

Dispatcher Servlet' processing request for [/OpenClassroom/spring/messagebroker/amf]
[DEBUG] http-9080-Processor22 2009-12-18 06:09:25 (AbstractUrlHandlerMapping.java:getHandlerInternal:176) - Mapping [/messagebroker/amf] to

handler 'flex.messaging.MessageBroker@2d95b3'
[DEBUG] http-9080-Processor22 2009-12-18 06:09:25 (AbstractUrlHandlerMapping.java:getHandlerInternal:176) - Mapping [/messagebroker/amf] to

handler 'flex.messaging.MessageBroker@2d95b3'
[ INFO] http-9080-Processor22 2009-12-18 06:09:25 (MessageBrokerHandlerAdapter.java:handle:76) - Channel endpoint my-amf received request.
[ INFO] http-9080-Processor22 2009-12-18 06:09:25 (MessageBrokerHandlerAdapter.java:handle:76) - Channel endpoint my-amf received request.
[DEBUG] http-9080-Processor22 2009-12-18 06:09:25 (AbstractPlatformTransactionManager.java:getTransaction:371) - Creating new transaction

with name [com.openclassroom.appcommon.service.AppCommonService.getSubjects]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT
[DEBUG] http-9080-Processor22 2009-12-18 06:09:25 (AbstractPlatformTransactionManager.java:getTransaction:371) - Creating new transaction

with name [com.openclassroom.appcommon.service.AppCommonService.getSubjects]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT
[DEBUG] http-9080-Processor22 2009-12-18 06:09:25 (HibernateTransactionManager.java:doBegin:496) - Opened new Session

[org.hibernate.impl.SessionImpl@fae78f] for Hibernate transaction
[DEBUG] http-9080-Processor22 2009-12-18 06:09:25 (HibernateTransactionManager.java:doBegin:496) - Opened new Session

[org.hibernate.impl.SessionImpl@fae78f] for Hibernate transaction
[DEBUG] http-9080-Processor22 2009-12-18 06:09:25 (HibernateTransactionManager.java:doBegin:526) - Not preparing JDBC Connection of

Hibernate Session [org.hibernate.impl.SessionImpl@fae78f]
[DEBUG] http-9080-Processor22 2009-12-18 06:09:25 (HibernateTransactionManager.java:doBegin:526) - Not preparing JDBC Connection of

Hibernate Session [org.hibernate.impl.SessionImpl@fae78f]
[DEBUG] http-9080-Processor22 2009-12-18 06:09:25 (HibernateTransactionManager.java:doGetTransaction:442) - Found thread-bound Session

[org.hibernate.impl.SessionImpl@fae78f] for Hibernate transaction
[DEBUG] http-9080-Processor22 2009-12-18 06:09:25 (HibernateTransactionManager.java:doGetTransaction:442) - Found thread-bound Session

[org.hibernate.impl.SessionImpl@fae78f] for Hibernate transaction
[DEBUG] http-9080-Processor22 2009-12-18 06:09:25 (AbstractPlatformTransactionManager.java:handleExistingTransaction:469) - Participating

in existing transaction
[DEBUG] http-9080-Processor22 2009-12-18 06:09:25 (AbstractPlatformTransactionManager.java:handleExistingTransaction:469) - Participating

in existing transaction
[DEBUG] http-9080-Processor22 2009-12-18 06:09:25 (HibernateTemplate.java:doExecute:410) - Found thread-bound Session for HibernateTemplate
[DEBUG] http-9080-Processor22 2009-12-18 06:09:25 (HibernateTemplate.java:doExecute:410) - Found thread-bound Session for HibernateTemplate
[DEBUG] http-9080-Processor25 2009-12-18 06:09:25 (DispatcherServlet.java:doService:781) - DispatcherServlet with name 'Spring MVC

Dispatcher Servlet' processing request for [/OpenClassroom/spring/messagebroker/amf]
[DEBUG] http-9080-Processor25 2009-12-18 06:09:25 (DispatcherServlet.java:doService:781) - DispatcherServlet with name 'Spring MVC

Dispatcher Servlet' processing request for [/OpenClassroom/spring/messagebroker/amf]
[DEBUG] http-9080-Processor25 2009-12-18 06:09:25 (AbstractUrlHandlerMapping.java:getHandlerInternal:176) - Mapping [/messagebroker/amf] to

handler 'flex.messaging.MessageBroker@2d95b3'
[DEBUG] http-9080-Processor25 2009-12-18 06:09:25 (AbstractUrlHandlerMapping.java:getHandlerInternal:176) - Mapping [/messagebroker/amf] to

handler 'flex.messaging.MessageBroker@2d95b3'
[ INFO] http-9080-Processor25 2009-12-18 06:09:25 (MessageBrokerHandlerAdapter.java:handle:76) - Channel endpoint my-amf received request.
[ INFO] http-9080-Processor25 2009-12-18 06:09:25 (MessageBrokerHandlerAdapter.java:handle:76) - Channel endpoint my-amf received request.
[DEBUG] http-9080-Processor25 2009-12-18 06:09:25 (DispatcherServlet.java:doDispatch:909) - Null ModelAndView returned to DispatcherServlet

with name 'Spring MVC Dispatcher Servlet': assuming HandlerAdapter completed request handling
[DEBUG] http-9080-Processor25 2009-12-18 06:09:25 (DispatcherServlet.java:doDispatch:909) - Null ModelAndView returned to DispatcherServlet

with name 'Spring MVC Dispatcher Servlet': assuming HandlerAdapter completed request handling
[DEBUG] http-9080-Processor25 2009-12-18 06:09:25 (FrameworkServlet.java:processRequest:591) - Successfully completed request
[DEBUG] http-9080-Processor25 2009-12-18 06:09:25 (FrameworkServlet.java:processRequest:591) - Successfully completed request
[ WARN] http-9080-Processor22 2009-12-18 06:09:25 (JDBCExceptionReporter.java:logExceptions:100) - SQL Error: 0, SQLState: 08S01
[ WARN] http-9080-Processor22 2009-12-18 06:09:25 (JDBCExceptionReporter.java:logExceptions:100) - SQL Error: 0, SQLState: 08S01
[ERROR] http-9080-Processor22 2009-12-18 06:09:25 (JDBCExceptionReporter.java:logExceptions:101) - Communications link failure due to

underlying exception:

** BEGIN NESTED EXCEPTION **

java.io.EOFException

STACKTRACE:

java.io.EOFException
at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1845)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2292)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2788)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1534)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1625)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2297)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2226)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1812)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1657)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1812)
at org.hibernate.loader.Loader.doQuery(Loader.java:697)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
at org.hibernate.loader.Loader.doList(Loader.java:2232)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2129)
at org.hibernate.loader.Loader.list(Loader.java:2124)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:401)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:363)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1149)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102)
at org.springframework.orm.hibernate3.HibernateTemplate$30.doInHibernate(HibernateTemplate.java:930)
at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:419)
at org.springframework.orm.hibernate3.HibernateTemplate.executeWithNativeSession(HibernateTemplate.java:374)
at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:921)
at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:913)
at com.openclassroom.appcommon.dao.hibernate.AppCommonDaoImpl.getSubjects(AppCommonDaoImpl.java:16)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
at $Proxy19.getSubjects(Unknown Source)
at com.openclassroom.appcommon.service.impl.AppCommonServiceImpl.getSubjects(AppCommonServiceImpl.java:35)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
at $Proxy20.getSubjects(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at flex.messaging.services.remoting.adapters.JavaAdapter.invoke(JavaAdapter.java:421)
at flex.messaging.services.RemotingService.serviceMessage(RemotingService.java:183)
at flex.messaging.MessageBroker.routeMessageToService(MessageBroker.java:1503)
at flex.messaging.endpoints.AbstractEndpoint.serviceMessage(AbstractEndpoint.java:884)
at flex.messaging.endpoints.amf.MessageBrokerFilter.invoke(MessageBrokerFilter.java:121)
at flex.messaging.endpoints.amf.LegacyFilter.invoke(LegacyFilter.java:158)
at flex.messaging.endpoints.amf.SessionFilter.invoke(SessionFilter.java:44)
at flex.messaging.endpoints.amf.BatchProcessFilter.invoke(BatchProcessFilter.java:67)
at flex.messaging.endpoints.amf.SerializationFilter.invoke(SerializationFilter.java:146)
at flex.messaging.endpoints.BaseHTTPEndpoint.service(BaseHTTPEndpoint.java:278)
at org.springframework.flex.messaging.servlet.MessageBrokerHandlerAdapter.handle(MessageBrokerHandlerAdapter.java:79)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:875)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:807)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:571)
at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:511)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:710)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:269)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:174)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:108)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:174)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:874)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:665)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:528)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:81)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:689)
at java.lang.Thread.run(Thread.java:595)


** END NESTED EXCEPTION **


The application has the following server-side technologies and hosting environment:
* Spring 2.5.6
* Hibernate 3
* Hibernate Annotations 3.4.0
* c3p0 database connection pool (c3p0-0.9.0.jar)
* Linux
* Tomcat 5.5
* MySQL 5.0.45 (hosted on the same server as Tomcat)

Hibernate setting in application.properties file:
hibernate.show_sql=true
hibernate.format_sql=true
hibernate.transaction.factory_class=org.hibernate.transaction.JDBCTransactionFactory
hibernate.dialect=org.hibernate.dialect.MySQLDialect
hibernate.c3p0.min_size=5
hibernate.c3p0.max_size=20
hibernate.c3p0.timeout=1800
hibernate.c3p0.max_statements=50
hibernate.c3p0.idle_test_period=30

Hibernate configuration in Spring's applicationContext.xml
<property name="hibernateProperties">
<props>
<prop key="hibernate.show_sql">${hibernate.show_sql}</prop>
<prop key="hibernate.format_sql">${hibernate.format_sql}</prop>
<prop key="hibernate.transaction.factory_class">${hibernate.transaction.factory_class}</prop>
<prop key="hibernate.dialect">${hibernate.dialect}</prop>
<prop key="hibernate.c3p0.min_size">${hibernate.c3p0.min_size}</prop>
<prop key="hibernate.c3p0.max_size">${hibernate.c3p0.max_size}</prop>
<prop key="hibernate.c3p0.timeout">${hibernate.c3p0.timeout}</prop>
<prop key="hibernate.c3p0.max_statements">${hibernate.c3p0.max_statements}</prop>
<prop key="hibernate.c3p0.idle_test_period">${hibernate.c3p0.idle_test_period}</prop>
<prop key="hibernate.connection.driver_class">${jdbc.driverClassName}</prop>
<prop key="hibernate.connection.url">${jdbc.url}</prop>
<prop key="hibernate.connection.username">${jdbc.username}</prop>
<prop key="hibernate.connection.password">${jdbc.password}</prop>
</props>
</property>

MySQL 'wait timeout' property is 28,800

Based on the research I've done on the web, it looks like c3p0 connection pool loses connection with MySQL and so when the request comes calling after 'many' hours of inactivity, it throws this exception and that makes me wonder why idle_test_period is not getting used.

Let me know if you need any additional info.

Thanks again for any and all the help with this,

Dilip Shah


Top
 Profile  
 
 Post subject: Re: SOS! - SQL Error: 0, SQLState: 08S01
PostPosted: Mon Jan 04, 2010 1:14 am 
Newbie

Joined: Mon Dec 18, 2006 1:54 am
Posts: 5
We faced similar problem in our project, and added the following in our DataSource beans for every database we were using.
Code:
<bean id="newDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
   <!-- userid, password etc. other properties -->
   <property name="validationQuery">
      <value>select 1</value>
   </property>
   <property name="maxIdle">
      <value>5</value>
   </property>
   <property name="maxActive">
      <value>20</value>
   </property>
   <property name="initialSize">
      <value>5</value>
   </property>
   <property name="timeBetweenEvictionRunsMillis">
      <value>1800000</value>
   </property>
   <property name="testWhileIdle">
      <value>true</value>
   </property>
   <property name="testOnBorrow">
      <value>false</value>
   </property>
</bean>


Here the validationQuery property makes query "select 1" (as specified) before every database operation. And it allows the actual database operation (that you intended) to be performed only when the above query is successfully executed. So it keeps firing the above query continuously until it is executed successfully. Also, the first request after the idle period may take slightly longer to execute for the same reason.

Please note that this solution is kind of expensive and you may have to look for alternatives again. We have not put this on production yet, but on QA it works fine.

Also, this link may prove helpful

_________________
neel


Top
 Profile  
 
 Post subject: Re: SOS! - SQL Error: 0, SQLState: 08S01
PostPosted: Wed Jan 13, 2010 12:42 pm 
Newbie

Joined: Fri Dec 18, 2009 2:28 pm
Posts: 7
Hi Neel,

Thanks for the suggestions but I couldn't fine the suggested parameters in the c3p0 documentation: http://www.mchange.com/projects/c3p0/in ... l#contents

Are you using c3p0 on your project?

Dilip


Top
 Profile  
 
 Post subject: Re: SOS! - SQL Error: 0, SQLState: 08S01
PostPosted: Wed Jan 13, 2010 12:53 pm 
Newbie

Joined: Fri Dec 18, 2009 2:28 pm
Posts: 7
It looks like the latest version of c3p0 has preferredTestQuery parameter (v/s validationQuery parameter mentioned in your post).

I'll implement this parameter and post the outcome on this forum. In the meanwhile, if you have any other comments / observations on this subject, please let me know.

Dilip


Top
 Profile  
 
 Post subject: Re: SOS! - SQL Error: 0, SQLState: 08S01
PostPosted: Thu Jan 14, 2010 1:33 pm 
Newbie

Joined: Fri Dec 18, 2009 2:28 pm
Posts: 7
Hi Neel,

Your solution seems to have worked... thanks a lot... really appreciate it!

If you find a more efficient solution for production environment, do post it on this forum.

Dilip


Top
 Profile  
 
 Post subject: Re: SOS! - SQL Error: 0, SQLState: 08S01
PostPosted: Thu May 05, 2011 4:03 am 
Newbie

Joined: Thu May 05, 2011 3:31 am
Posts: 8
hi, I am having the same problem so I have followed "neel" advice and deployed the app on test server but this error occurs after a day or I guess after mysql default "Wait_timeout" values which is 8 hrs.

I want to ask that did you reduced the time out value to some testable time like say wait_timeout=180 (second) so that you one can test application relatively quickly instead of waiting for 8 hours.

any advices?


Top
 Profile  
 
 Post subject: Re: SOS! - SQL Error: 0, SQLState: 08S01
PostPosted: Thu May 05, 2011 5:01 am 
Newbie

Joined: Thu May 05, 2011 4:45 am
Posts: 3
The application is hosted in a QA environment where it doesn't get accessed for many hours.

_________________
Give up you is not my fault, and the wrong is lack of the money-tiffany jewelry
While there is life, there is hope tiffany jewelry cheap.


Top
 Profile  
 
 Post subject: Re: SOS! - SQL Error: 0, SQLState: 08S01
PostPosted: Thu May 05, 2011 9:01 am 
Newbie

Joined: Thu May 05, 2011 3:31 am
Posts: 8
well in my case its sort of .. its hosted on a test server, what I am trying to now is that, reducing wait_timeout on Mysql instance will produce this same error sooner right ?

because no body would want to wait for default 'wait_timeout' which in mysql case is 8 hours.


ed1122 wrote:
The application is hosted in a QA environment where it doesn't get accessed for many hours.


Top
 Profile  
 
 Post subject: Re: SOS! - SQL Error: 0, SQLState: 08S01
PostPosted: Thu May 05, 2011 9:04 am 
Newbie

Joined: Thu May 05, 2011 3:31 am
Posts: 8
I am asking this because I changed the @@global.wait_timeout for MySQL 5 to 30 sec just to see if the hosted applications connection goes in a stale status after 30 seconds, but it didnt, it should be, I think but it didnt ...

I think I should switch to c3p0 Provider and data sources instead of using apaches dbcp? give me advice in this regard if any can ? :) I am in middle of this decision....


Top
 Profile  
 
 Post subject: Re: SOS! - SQL Error: 0, SQLState: 08S01
PostPosted: Mon May 09, 2011 12:11 pm 
Newbie

Joined: Thu May 05, 2011 3:31 am
Posts: 8
hi,
I have tried your solution..
did you found its parallel solution in C3p0 ?


imndneel wrote:
We faced similar problem in our project, and added the following in our DataSource beans for every database we were using.
Code:
<bean id="newDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
   <!-- userid, password etc. other properties -->
   <property name="validationQuery">
      <value>select 1</value>
   </property>
   <property name="maxIdle">
      <value>5</value>
   </property>
   <property name="maxActive">
      <value>20</value>
   </property>
   <property name="initialSize">
      <value>5</value>
   </property>
   <property name="timeBetweenEvictionRunsMillis">
      <value>1800000</value>
   </property>
   <property name="testWhileIdle">
      <value>true</value>
   </property>
   <property name="testOnBorrow">
      <value>false</value>
   </property>
</bean>


Here the validationQuery property makes query "select 1" (as specified) before every database operation. And it allows the actual database operation (that you intended) to be performed only when the above query is successfully executed. So it keeps firing the above query continuously until it is executed successfully. Also, the first request after the idle period may take slightly longer to execute for the same reason.

Please note that this solution is kind of expensive and you may have to look for alternatives again. We have not put this on production yet, but on QA it works fine.

Also, this link may prove helpful


Top
 Profile  
 
 Post subject: Re: SOS! - SQL Error: 0, SQLState: 08S01
PostPosted: Mon May 09, 2011 12:13 pm 
Newbie

Joined: Thu May 05, 2011 3:31 am
Posts: 8
further I used :

Code:
   <property name="testOnBorrow">
      <value>true</value>
   </property>


so that every time a connection is given to app must be tested if its alive...
but stale connections are still given to app which create problem ..

any help will be appreciable..

Thanx.


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 11 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.