Recover Informix JDBC connections in Tomcat after loss of Network Connectivity
A Tomcat/Hibernate app using several databases recovered automatically after a network outage for Oracle, MySQL and SQL Server, but Informix JDBC connections stayed broken (GenericJDBCException / "System or internal error" during prepareStatement) until Tomcat was restarted. Replies suggested the issue lay in the JDBC driver or pool holding dead sockets and in cached/prepared statements not being re-prepared, and advised enabling JDBC tracing to get the real driver error code (possibly -79716), checking for a firewall, and using a pool with connection validation/reconnection. The poster showed his c3p0 settings; no resolution is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi everyone. My application makes use of Informix, Oracle, SQL Server and My SQL. Of all these databases, whenever my web server loses connectivity with a database server, only the Informix connections do not recover. For all the other databases, restoring connectivity between the two servers will cause JDBC queries for Oracle, MySQL and MS SQL to resume as normal. Informix, however, gets a GenericJDBCException on any queries after the loss and recovery of network connectivity. The only way to get Informix connected again is to restart Tomcat. There has to be a way to make Informix able to recover its connection pool once connectivity is restored. Can anybody point me in the right direction? Thanks!
Jason P wrote: > Hi everyone. My application makes use of Informix, Oracle, SQL Server > and My SQL. Of all these databases, whenever my web server loses > connectivity with a database server, only the Informix connections do > not recover. For all the other databases, restoring connectivity > between the two servers will cause JDBC queries for Oracle, MySQL and > MS SQL to resume as normal. Informix, however, gets a > GenericJDBCException on any queries after the loss and recovery of > network connectivity. The only way to get Informix connected again is > to restart Tomcat. There has to be a way to make Informix able to > recover its connection pool once connectivity is restored. Can > anybody point me in the right direction? Thanks! The problem is not Informix, but probably the JDBC driver. I'd like to know what the explicit error code(s) are, as that will point up the problem. It looks like the other drivers are reconnecting their connection pools immediately upon connectivity being restored and the IBM Informix driver is not, or more likely, the Informix JDBC library is PREPAREing your queries initially for efficiency and not repreparing them when it discovers that connectivity was lost while the other libraries are not using prepared statements. Can't know without seeing the error codes. Art S. Kagel Oninit =========================================================================================== Please access the attached hyperlink for an important electronic communications disclaimer: http://www.oninit.com/home/disclaimer.php ===========================================================================================
On Mar 6, 10:21 am, "Art S. Kagel (Oninit)" <a...@oninit.com> wrote: > Jason P wrote: > > Hi everyone. My application makes use of Informix, Oracle, SQL Server > > and My SQL. Of all these databases, whenever my web server loses > > connectivity with a database server, only the Informix connections do > > not recover. For all the other databases, restoring connectivity > > between the two servers will cause JDBC queries for Oracle, MySQL and > > MS SQL to resume as normal. Informix, however, gets a > > GenericJDBCException on any queries after the loss and recovery of > > network connectivity. The only way to get Informix connected again is > > to restart Tomcat. There has to be a way to make Informix able to > > recover its connection pool once connectivity is restored. Can > > anybody point me in the right direction? Thanks! > > The problem is not Informix, but probably the JDBC driver. I'd like to > know what the explicit error code(s) are, as that will point up the > problem. It looks like the other drivers are reconnecting their > connection pools immediately upon connectivity being restored and the > IBM Informix driver is not, or more likely, the Informix JDBC library is > PREPAREing your queries initially for efficiency and not repreparing > them when it discovers that connectivity was lost while the other > libraries are not using prepared statements. Can't know without seeing > the error codes. > > Art S. Kagel > Oninit > > =========================================================================================== > Please access the attached hyperlink for an important electronic communications disclaimer: > > http://www.oninit.com/home/disclaimer.php > > =========================================================================================== Hi Art. Thanks for replying. I honestly don't know if this will help you. It's the stack trace. If it doesn't help, I will have to do something to intercept the actual Informix code. org.hibernate.exception.GenericJDBCException: could not execute query at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java: 103) at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java: 91) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java: 43) at org.hibernate.loader.Loader.doList(Loader.java:2205) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2086) at org.hibernate.loader.Loader.list(Loader.java:2081) at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:404) at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java: 338) at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java: 172) at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121) at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79) 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: 335) at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java: 181) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java: 148) at com.bsl.core.hibernate.HibernateQueryInterceptor.invoke(HibernateQueryInterceptor.java: 154) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java: 170) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java: 176) at $Proxy4.list(Unknown Source) at com.bsl.insight.plugin.types.InstrumentBase.list(InstrumentBase.java: 868) at com.bsl.insight.plugin.builtin.instrument.EmptyLocations.execute(EmptyLocations.java: 158) at com.bsl.insight.plugin.builtin.instrument.EmptyLocations.executeNoReflect(EmptyLocations.java: 101) at com.bsl.insight.core.InstrumentExecutor.executeInstrument(InstrumentExecutor.java: 686) at com.bsl.insight.core.InstrumentExecutor $CallableInstrument.call(InstrumentExecutor.java:130) at edu.emory.mathcs.backport.java.util.concurrent.FutureTask.run(FutureTask.java: 166) at edu.emory.mathcs.backport.java.util.concurrent.ThreadPoolExecutor $Worker.runTask(ThreadPoolExecutor.java:643) at edu.emory.mathcs.backport.java.util.concurrent.ThreadPoolExecutor $Worker.run(ThreadPoolExecutor.java:668) at java.lang.Thread.run(Unknown Source) Caused by: java.sql.SQLException: System or internal error at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:373) at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2292) at com.informix.jdbc.IfxSqli.executePrepare(IfxSqli.java:1153) at com.informix.jdbc.IfxPreparedStatement.e(IfxPreparedStatement.java: 318) at com.informix.jdbc.IfxPreparedStatement.a(IfxPreparedStatement.java: 298) at com.informix.jdbc.IfxPreparedStatement.<init>(IfxPreparedStatement.java: 168) at com.informix.jdbc.IfxSqliConnect.h(IfxSqliConnect.java:5918) at com.informix.jdbc.IfxSqliConnect.prepareStatement(IfxSqliConnect.java: 1999) at org.apache.tomcat.dbcp.dbcp.DelegatingConnection.prepareStatement(DelegatingConnection.java: 185) at org.apache.tomcat.dbcp.dbcp.PoolingDataSource $PoolGuardConnectionWrapper.prepareStatement(PoolingDataSource.java: 278) at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java: 497) at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java: 415) at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java: 139) at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java: 1619) at org.hibernate.loader.Loader.doQuery(Loader.java:666) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java: 229) at org.hibernate.loader.Loader.doList(Loader.java:2202) ... 27 more
Jason P wrote: > On Mar 6, 10:21 am, "Art S. Kagel (Oninit)" <a...@oninit.com> wrote: >> Jason P wrote: >>> Hi everyone. My application makes use of Informix, Oracle, SQL Server >>> and My SQL. Of all these databases, whenever my web server loses >>> connectivity with a database server, only the Informix connections do >>> not recover. For all the other databases, restoring connectivity >>> between the two servers will cause JDBC queries for Oracle, MySQL and >>> MS SQL to resume as normal. Informix, however, gets a >>> GenericJDBCException on any queries after the loss and recovery of >>> network connectivity. The only way to get Informix connected again is >>> to restart Tomcat. There has to be a way to make Informix able to >>> recover its connection pool once connectivity is restored. Can >>> anybody point me in the right direction? Thanks! >> The problem is not Informix, but probably the JDBC driver. I'd like to >> know what the explicit error code(s) are, as that will point up the >> problem. It looks like the other drivers are reconnecting their >> connection pools immediately upon connectivity being restored and the >> IBM Informix driver is not, or more likely, the Informix JDBC library is >> PREPAREing your queries initially for efficiency and not repreparing >> them when it discovers that connectivity was lost while the other >> libraries are not using prepared statements. Can't know without seeing >> the error codes. >> >> Art S. Kagel >> Oninit >> >> ==========================================================================='================ >> Please access the attached hyperlink for an important electronic communications disclaimer: >> >> http://www.oninit.com/home/disclaimer.php >> >> ==========================================================================='================ > > Hi Art. Thanks for replying. > > I honestly don't know if this will help you. It's the stack trace. If > it doesn't help, I will have to do something to intercept the actual > Informix code. > > org.hibernate.exception.GenericJDBCException: could not execute query > at > org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java: > 103) > at > org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java: > 91) > at > org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java: > 43) > at org.hibernate.loader.Loader.doList(Loader.java:2205) > at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2086) > at org.hibernate.loader.Loader.list(Loader.java:2081) > at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:404) > at > org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java: > 338) > at > org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java: > 172) > at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121) > at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79) > 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: > 335) > at > org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java: > 181) > at > org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java: > 148) > at > com.bsl.core.hibernate.HibernateQueryInterceptor.invoke(HibernateQueryInterceptor.java: > 154) > at > org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java: > 170) > at > org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java: > 176) > at $Proxy4.list(Unknown Source) > at > com.bsl.insight.plugin.types.InstrumentBase.list(InstrumentBase.java: > 868) > at > com.bsl.insight.plugin.builtin.instrument.EmptyLocations.execute(EmptyLocations.java: > 158) > at > com.bsl.insight.plugin.builtin.instrument.EmptyLocations.executeNoReflect(EmptyLocations.java: > 101) > at > com.bsl.insight.core.InstrumentExecutor.executeInstrument(InstrumentExecutor.java: > 686) > at com.bsl.insight.core.InstrumentExecutor > $CallableInstrument.call(InstrumentExecutor.java:130) > at > edu.emory.mathcs.backport.java.util.concurrent.FutureTask.run(FutureTask.java: > 166) > at edu.emory.mathcs.backport.java.util.concurrent.ThreadPoolExecutor > $Worker.runTask(ThreadPoolExecutor.java:643) > at edu.emory.mathcs.backport.java.util.concurrent.ThreadPoolExecutor > $Worker.run(ThreadPoolExecutor.java:668) > at java.lang.Thread.run(Unknown Source) > Caused by: java.sql.SQLException: System or internal error > at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:373) > at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2292) > at com.informix.jdbc.IfxSqli.executePrepare(IfxSqli.java:1153) > at com.informix.jdbc.IfxPreparedStatement.e(IfxPreparedStatement.java: > 318) > at com.informix.jdbc.IfxPreparedStatement.a(IfxPreparedStatement.java: > 298) > at > com.informix.jdbc.IfxPreparedStatement.<init>(IfxPreparedStatement.java: > 168) > at com.informix.jdbc.IfxSqliConnect.h(IfxSqliConnect.java:5918) > at > com.informix.jdbc.IfxSqliConnect.prepareStatement(IfxSqliConnect.java: > 1999) > at > org.apache.tomcat.dbcp.dbcp.DelegatingConnection.prepareStatement(DelegatingConnection.java: > 185) > at org.apache.tomcat.dbcp.dbcp.PoolingDataSource > $PoolGuardConnectionWrapper.prepareStatement(PoolingDataSource.java: > 278) > at > org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java: > 497) > at > org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java: > 415) > at > org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java: > 139) > at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java: > 1619) > at org.hibernate.loader.Loader.doQuery(Loader.java:666) > at > org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java: > 229) > at org.hibernate.loader.Loader.doList(Loader.java:2202) > ... 27 more Do you have firewall between the two servers? If you can simulate the situation you can enable tracing on the driver... This involves just some changes in the JDBC URL and the properties to add will depend on the version of the JDBC driver. The error you're showing seems like an error given by hibernate and not the driver itself. It would help to see the driver error code... I imagine that the difference between Informix and the others lies on the hibernate layer. Possibly Hibernate is catching and understanding the errors from other JDBC drivers and is not doing the same for Informix. My experience tells me you may be having the error -79716 and a message talking about socket problems. This usually happens when the driver holds reference to sockets that are not connected anymore. Many times this is apparently
<Snip> > > Hi Art. Thanks for replying. > > I honestly don't know if this will help you. It's the stack trace. If > it doesn't help, I will have to do something to intercept the actual > Informix code. > > org.hibernate.exception.GenericJDBCException: could not execute query > at > org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java: > 103) > at > org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java: > 91) > at > org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java: > 43) > at org.hibernate.loader.Loader.doList(Loader.java:2205) > at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2086) > at org.hibernate.loader.Loader.list(Loader.java:2081) > at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:404) > at > org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java: > 338) > at > org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java: > 172) > at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121) > at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79) > 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: > 335) > at > org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java: > 181) > at > org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java: > 148) > at > com.bsl.core.hibernate.HibernateQueryInterceptor.invoke(HibernateQueryInterceptor.java: > 154) > at > org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java: > 170) > at > org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java: > 176) > at $Proxy4.list(Unknown Source) > at > com.bsl.insight.plugin.types.InstrumentBase.list(InstrumentBase.java: > 868) > at > com.bsl.insight.plugin.builtin.instrument.EmptyLocations.execute(EmptyLocations.java: > 158) > at > com.bsl.insight.plugin.builtin.instrument.EmptyLocations.executeNoReflect(EmptyLocations.java: > 101) > at > com.bsl.insight.core.InstrumentExecutor.executeInstrument(InstrumentExecutor.java: > 686) > at com.bsl.insight.core.InstrumentExecutor > $CallableInstrument.call(InstrumentExecutor.java:130) > at > edu.emory.mathcs.backport.java.util.concurrent.FutureTask.run(FutureTask.java: > 166) > at edu.emory.mathcs.backport.java.util.concurrent.ThreadPoolExecutor > $Worker.runTask(ThreadPoolExecutor.java:643) > at edu.emory.mathcs.backport.java.util.concurrent.ThreadPoolExecutor > $Worker.run(ThreadPoolExecutor.java:668) > at java.lang.Thread.run(Unknown Source) > Caused by: java.sql.SQLException: System or internal error > at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:373) > at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2292) > at com.informix.jdbc.IfxSqli.executePrepare(IfxSqli.java:1153) > at com.informix.jdbc.IfxPreparedStatement.e(IfxPreparedStatement.java: > 318) > at com.informix.jdbc.IfxPreparedStatement.a(IfxPreparedStatement.java: > 298) > at > com.informix.jdbc.IfxPreparedStatement.<init>(IfxPreparedStatement.java: > 168) > at com.informix.jdbc.IfxSqliConnect.h(IfxSqliConnect.java:5918) > at > com.informix.jdbc.IfxSqliConnect.prepareStatement(IfxSqliConnect.java: > 1999) > at > org.apache.tomcat.dbcp.dbcp.DelegatingConnection.prepareStatement(DelegatingConnection.java: > 185) > at org.apache.tomcat.dbcp.dbcp.PoolingDataSource > $PoolGuardConnectionWrapper.prepareStatement(PoolingDataSource.java: > 278) > at > org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java: > 497) > at > org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java: > 415) > at > org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java: > 139) > at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java: > 1619) > at org.hibernate.loader.Loader.doQuery(Loader.java:666) > at > org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java: > 229) > at org.hibernate.loader.Loader.doList(Loader.java:2202) > ... 27 more > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > > Hibernate does like to cache its queries on startup. What does your hibernate.properties file look like? There are several connection pooling libraries out there that allow for validation of database connections and automatic reconnection. Usually these are relatively easy to plug into an existing app. It might be worth while to take a look at: http://www.informit.com/articles/article.aspx?p=353736&seqNum=4 (I haven't read it in depth but he seems to be pointing in something of the right direction, i.e. a connection pooling library) Also, here's a link for dbcp http://commons.apache.org/dbcp/ -- -------------------------------------------- Chris Salch Programmer/Analyst LeTourneau University
On Mar 6, 6:07 pm, Fernando Nunes <s...@onlinedomus.net> wrote: > Jason P wrote: > > On Mar 6, 10:21 am, "Art S. Kagel (Oninit)" <a...@oninit.com> wrote: > >> Jason P wrote: > >>> Hi everyone. My application makes use of Informix, Oracle, SQL Server > >>> and My SQL. Of all these databases, whenever my web server loses > >>> connectivity with a database server, only the Informix connections do > >>> not recover. For all the other databases, restoring connectivity > >>> between the two servers will cause JDBC queries for Oracle, MySQL and > >>> MS SQL to resume as normal. Informix, however, gets a > >>> GenericJDBCException on any queries after the loss and recovery of > >>> network connectivity. The only way to get Informix connected again is > >>> to restart Tomcat. There has to be a way to make Informix able to > >>> recover its connection pool once connectivity is restored. Can > >>> anybody point me in the right direction? Thanks! > >> The problem is not Informix, but probably the JDBC driver. I'd like to > >> know what the explicit error code(s) are, as that will point up the > >> problem. It looks like the other drivers are reconnecting their > >> connection pools immediately upon connectivity being restored and the > >> IBM Informix driver is not, or more likely, the Informix JDBC library is > >> PREPAREing your queries initially for efficiency and not repreparing > >> them when it discovers that connectivity was lost while the other > >> libraries are not using prepared statements. Can't know without seeing > >> the error codes. > > >> Art S. Kagel > >> Oninit > > >> =========================================================================================== > >> Please access the attached hyperlink for an important electronic communications disclaimer: > > >>http://www.oninit.com/home/disclaimer.php > > >> =========================================================================================== > > > Hi Art. Thanks for replying. > > > I honestly don't know if this will help you. It's the stack trace. If > > it doesn't help, I will have to do something to intercept the actual > > Informix code. > > > org.hibernate.exception.GenericJDBCException: could not execute query > > at > > org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java: > > 103) > > at > > org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java: > > 91) > > at > > org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java: > > 43) > > at org.hibernate.loader.Loader.doList(Loader.java:2205) > > at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2086) > > at org.hibernate.loader.Loader.list(Loader.java:2081) > > at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:404) > > at > > org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java: > > 338) > > at > > org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java: > > 172) > > at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121) > > at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79) > > 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: > > 335) > > at > > org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java: > > 181) > > at > > org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java: > > 148) > > at > > com.bsl.core.hibernate.HibernateQueryInterceptor.invoke(HibernateQueryInterceptor.java: > > 154) > > at > > org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java: > > 170) > > at > > org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java: > > 176) > > at $Proxy4.list(Unknown Source) > > at > > com.bsl.insight.plugin.types.InstrumentBase.list(InstrumentBase.java: > > 868) > > at > > com.bsl.insight.plugin.builtin.instrument.EmptyLocations.execute(EmptyLocations.java: > > 158) > > at > > com.bsl.insight.plugin.builtin.instrument.EmptyLocations.executeNoReflect(EmptyLocations.java: > > 101) > > at > > com.bsl.insight.core.InstrumentExecutor.executeInstrument(InstrumentExecutor.java: > > 686) > > at com.bsl.insight.core.InstrumentExecutor > > $CallableInstrument.call(InstrumentExecutor.java:130) > > at > > edu.emory.mathcs.backport.java.util.concurrent.FutureTask.run(FutureTask.java: > > 166) > > at edu.emory.mathcs.backport.java.util.concurrent.ThreadPoolExecutor > > $Worker.runTask(ThreadPoolExecutor.java:643) > > at edu.emory.mathcs.backport.java.util.concurrent.ThreadPoolExecutor > > $Worker.run(ThreadPoolExecutor.java:668) > > at java.lang.Thread.run(Unknown Source) > > Caused by: java.sql.SQLException: System or internal error > > at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:373) > > at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2292) > > at com.informix.jdbc.IfxSqli.executePrepare(IfxSqli.java:1153) > > at com.informix.jdbc.IfxPreparedStatement.e(IfxPreparedStatement.java: > > 318) > > at com.informix.jdbc.IfxPreparedStatement.a(IfxPreparedStatement.java: > > 298) > > at > > com.informix.jdbc.IfxPreparedStatement.<init>(IfxPreparedStatement.java: > > 168) > > at com.informix.jdbc.IfxSqliConnect.h(IfxSqliConnect.java:5918) > > at > > com.informix.jdbc.IfxSqliConnect.prepareStatement(IfxSqliConnect.java: > > 1999) > > at > > org.apache.tomcat.dbcp.dbcp.DelegatingConnection.prepareStatement(DelegatingConnection.java: > > 185) > > at org.apache.tomcat.dbcp.dbcp.PoolingDataSource > > $PoolGuardConnectionWrapper.prepareStatement(PoolingDataSource.java: > > 278) > > at > > org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java: > > 497) > > at > > org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java: > > 415) > > at > > org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java: > > 139) > > at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java: > > 1619) > > at org.hibernate.loader.Loader.doQuery(Loader.java:666) > > at > > org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java: > > 229) > > at org.hibernate.loader.Loader.doList(Loader.java:2202) > > ... 27 more > > Do you have firewall between the two servers? > If you can simulate the situation you can enable tracing on the driver... This > involves just some changes in the JDBC URL and the properties to add will > depend on the version of the JDBC driver. > The error you're showing seems like an error given by hibernate and not the > driver itself. It would help to see the driver error code... > > I imagine
On Mar 7, 11:49 am, Chris Salch <ChrisSa...@letu.edu> wrote: > <Snip> > > > > > > > Hi Art. Thanks for replying. > > > I honestly don't know if this will help you. It's the stack trace. If > > it doesn't help, I will have to do something to intercept the actual > > Informix code. > > > org.hibernate.exception.GenericJDBCException: could not execute query > > at > > org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java: > > 103) > > at > > org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java: > > 91) > > at > > org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java: > > 43) > > at org.hibernate.loader.Loader.doList(Loader.java:2205) > > at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2086) > > at org.hibernate.loader.Loader.list(Loader.java:2081) > > at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:404) > > at > > org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java: > > 338) > > at > > org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java: > > 172) > > at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121) > > at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79) > > 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: > > 335) > > at > > org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java: > > 181) > > at > > org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java: > > 148) > > at > > com.bsl.core.hibernate.HibernateQueryInterceptor.invoke(HibernateQueryInterceptor.java: > > 154) > > at > > org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java: > > 170) > > at > > org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java: > > 176) > > at $Proxy4.list(Unknown Source) > > at > > com.bsl.insight.plugin.types.InstrumentBase.list(InstrumentBase.java: > > 868) > > at > > com.bsl.insight.plugin.builtin.instrument.EmptyLocations.execute(EmptyLocations.java: > > 158) > > at > > com.bsl.insight.plugin.builtin.instrument.EmptyLocations.executeNoReflect(EmptyLocations.java: > > 101) > > at > > com.bsl.insight.core.InstrumentExecutor.executeInstrument(InstrumentExecutor.java: > > 686) > > at com.bsl.insight.core.InstrumentExecutor > > $CallableInstrument.call(InstrumentExecutor.java:130) > > at > > edu.emory.mathcs.backport.java.util.concurrent.FutureTask.run(FutureTask.java: > > 166) > > at edu.emory.mathcs.backport.java.util.concurrent.ThreadPoolExecutor > > $Worker.runTask(ThreadPoolExecutor.java:643) > > at edu.emory.mathcs.backport.java.util.concurrent.ThreadPoolExecutor > > $Worker.run(ThreadPoolExecutor.java:668) > > at java.lang.Thread.run(Unknown Source) > > Caused by: java.sql.SQLException: System or internal error > > at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:373) > > at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2292) > > at com.informix.jdbc.IfxSqli.executePrepare(IfxSqli.java:1153) > > at com.informix.jdbc.IfxPreparedStatement.e(IfxPreparedStatement.java: > > 318) > > at com.informix.jdbc.IfxPreparedStatement.a(IfxPreparedStatement.java: > > 298) > > at > > com.informix.jdbc.IfxPreparedStatement.<init>(IfxPreparedStatement.java: > > 168) > > at com.informix.jdbc.IfxSqliConnect.h(IfxSqliConnect.java:5918) > > at > > com.informix.jdbc.IfxSqliConnect.prepareStatement(IfxSqliConnect.java: > > 1999) > > at > > org.apache.tomcat.dbcp.dbcp.DelegatingConnection.prepareStatement(DelegatingConnection.java: > > 185) > > at org.apache.tomcat.dbcp.dbcp.PoolingDataSource > > $PoolGuardConnectionWrapper.prepareStatement(PoolingDataSource.java: > > 278) > > at > > org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java: > > 497) > > at > > org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java: > > 415) > > at > > org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java: > > 139) > > at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java: > > 1619) > > at org.hibernate.loader.Loader.doQuery(Loader.java:666) > > at > > org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java: > > 229) > > at org.hibernate.loader.Loader.doList(Loader.java:2202) > > ... 27 more > > _______________________________________________ > > Informix-list mailing list > > Informix-l...@iiug.org > >http://www.iiug.org/mailman/listinfo/informix-list > > Hibernate does like to cache its queries on startup. What does your > hibernate.properties file look like? > > There are several connection pooling libraries out there that allow for > validation of database connections and automatic reconnection. Usually > these are relatively easy to plug into an existing app. > > It might be worth while to take a look at:http://www.informit.com/articles/article.aspx?p=353736&seqNum=4 > (I haven't read it in depth but he seems to be pointing in something of > the right direction, i.e. a connection pooling library) > > Also, here's a link for dbcphttp://commons.apache.org/dbcp/ > > -- > -------------------------------------------- > Chris Salch > Programmer/Analyst > LeTourneau University- Hide quoted text - > > - Show quoted text - c3p0.testConnectionOnCheckin=true c3p0.checkoutTimeout=10000 c3p0.maxIdleTime=1800 c3p0.acquireRetryAttempts=100000 c3p0.acquireRetryDelay=5000 c3p0.breakAfterAcquireFailure=false c3p0.maxConnectionAge=1800 hibernate.c3p0.min_size=5 hibernate.c3p0.max_size=20 hibernate.c3p0.timeout=1800 hibernate.c3p0.max_statements=50 hibernate.c3p0.acquire_increment=5 hibernate.c3p0.idle_test_period=120 hibernate.jdbc.fetch_size=100 hibernate.show_sql=false As you can see we're using c3p0. r2d2 wasn't quite working out ;-)
Jason P wrote: > On Mar 6, 6:07 pm, Fernando Nunes <s...@onlinedomus.net> wrote: >> Jason P wrote: >>> On Mar 6, 10:21 am, "Art S. Kagel (Oninit)" <a...@oninit.com> wrote: >>>> Jason P wrote: >>>>> Hi everyone. My application makes use of Informix, Oracle, SQL Server >>>>> and My SQL. Of all these databases, whenever my web server loses >>>>> connectivity with a database server, only the Informix connections do >>>>> not recover. For all the other databases, restoring connectivity >>>>> between the two servers will cause JDBC queries for Oracle, MySQL and >>>>> MS SQL to resume as normal. Informix, however, gets a >>>>> GenericJDBCException on any queries after the loss and recovery of >>>>> network connectivity. The only way to get Informix connected again is >>>>> to restart Tomcat. There has to be a way to make Informix able to >>>>> recover its connection pool once connectivity is restored. Can >>>>> anybody point me in the right direction? Thanks! >>>> The problem is not Informix, but probably the JDBC driver. I'd like to >>>> know what the explicit error code(s) are, as that will point up the >>>> problem. It looks like the other drivers are reconnecting their >>>> connection pools immediately upon connectivity being restored and the >>>> IBM Informix driver is not, or more likely, the Informix JDBC library is >>>> PREPAREing your queries initially for efficiency and not repreparing >>>> them when it discovers that connectivity was lost while the other >>>> libraries are not using prepared statements. Can't know without seeing >>>> the error codes. >>>> Art S. Kagel >>>> Oninit >>>> ===========================================================================''================ >>>> Please access the attached hyperlink for an important electronic communications disclaimer: >>>> http://www.oninit.com/home/disclaimer.php >>>> ===========================================================================''================ >>> Hi Art. Thanks for replying. >>> I honestly don't know if this will help you. It's the stack trace. If >>> it doesn't help, I will have to do something to intercept the actual >>> Informix code. >>> org.hibernate.exception.GenericJDBCException: could not execute query >>> at >>> org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLSt'ateConverter.java: >>> 103) >>> at >>> org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java: >>> 91) >>> at >>> org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.jav'a: >>> 43) >>> at org.hibernate.loader.Loader.doList(Loader.java:2205) >>> at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2086) >>> at org.hibernate.loader.Loader.list(Loader.java:2081) >>> at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:404) >>> at >>> org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java: >>> 338) >>> at >>> org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java: >>> 172) >>> at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121) >>> at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79) >>> 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(Aop'Utils.java: >>> 335) >>> at >>> org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoin't(ReflectiveMethodInvocation.java: >>> 181) >>> at >>> org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(Reflec'tiveMethodInvocation.java: >>> 148) >>> at >>> com.bsl.core.hibernate.HibernateQueryInterceptor.invoke(HibernateQueryInter'ceptor.java: >>> 154) >>> at >>> org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(Reflec'tiveMethodInvocation.java: >>> 170) >>> at >>> org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopPr'oxy.java: >>> 176) >>> at $Proxy4.list(Unknown Source) >>> at >>> com.bsl.insight.plugin.types.InstrumentBase.list(InstrumentBase.java: >>> 868) >>> at >>> com.bsl.insight.plugin.builtin.instrument.EmptyLocations.execute(EmptyLocat'ions.java: >>> 158) >>> at >>> com.bsl.insight.plugin.builtin.instrument.EmptyLocations.executeNoReflect(E'mptyLocations.java: >>> 101) >>> at >>> com.bsl.insight.core.InstrumentExecutor.executeInstrument(InstrumentExecuto'r.java: >>> 686) >>> at com.bsl.insight.core.InstrumentExecutor >>> $CallableInstrument.call(InstrumentExecutor.java:130) >>> at >>> edu.emory.mathcs.backport.java.util.concurrent.FutureTask.run(FutureTask.ja'va: >>> 166) >>> at edu.emory.mathcs.backport.java.util.concurrent.ThreadPoolExecutor >>> $Worker.runTask(ThreadPoolExecutor.java:643) >>> at edu.emory.mathcs.backport.java.util.concurrent.ThreadPoolExecutor >>> $Worker.run(ThreadPoolExecutor.java:668) >>> at java.lang.Thread.run(Unknown Source) >>> Caused by: java.sql.SQLException: System or internal error >>> at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:373) >>> at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2292) >>> at com.informix.jdbc.IfxSqli.executePrepare(IfxSqli.java:1153) >>> at com.informix.jdbc.IfxPreparedStatement.e(IfxPreparedStatement.java: >>> 318) >>> at com.informix.jdbc.IfxPreparedStatement.a(IfxPreparedStatement.java: >>> 298) >>> at >>> com.informix.jdbc.IfxPreparedStatement.<init>(IfxPreparedStatement.java: >>> 168) >>> at com.informix.jdbc.IfxSqliConnect.h(IfxSqliConnect.java:5918) >>> at >>> com.informix.jdbc.IfxSqliConnect.prepareStatement(IfxSqliConnect.java: >>> 1999) >>> at >>> org.apache.tomcat.dbcp.dbcp.DelegatingConnection.prepareStatement(Delegatin'gConnection.java: >>> 185) >>> at org.apache.tomcat.dbcp.dbcp.PoolingDataSource >>> $PoolGuardConnectionWrapper.prepareStatement(PoolingDataSource.java: >>> 278) >>> at >>> org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.jav'a: >>> 497) >>> at >>> org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.jav'a: >>> 415) >>> at >>> org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.ja'va: >>> 139) >>> at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java: >>> 1619) >>> at org.hibernate.loader.Loader.doQuery(Loader.java:666) >>> at >>> org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.j'ava: >>> 229) >>> at org.hibernate.loader.Loader.doList(Loader.java:2202) >>> ... 27 more >> Do you have firewall between the two servers? >> If you can simulate the situation you can enable tracing on the driver... This >> involves just some changes in the JDBC URL and the properties to add will >> depend on the version of the JDBC driver. >> The error you're showing seems like an error given by hibernate and not the >> driver itself. It would help to see the driver error code... >>@