> [TDS Driver]Read timed out
This looks to me like a network-level timeout, not a database timeout.
I've only used the open-source jdbc driver for SQL Server but we have
had problems with connection instability if our app is idle for a long
time (MSSQL2005), a ping-query fixed that though. Doesn't seem like
this is your problem but it doe snot seem like actual performance is
your problem either.
-J
On Jan 24, 2008 7:00 PM, charlie bird <[EMAIL PROTECTED]> wrote:
> Hi - Hope someone can help.
>
> I am using ibatis 2.3.0.667 within Spring 2.0 on
> Tomcat 5.5.9A connecting to SQL Server 7 and Merlia
> SQL server driver and getting this stack trace:
>
> 2008-01-24 10:45:49,276 DEBUG [java.sql.Connection] -
> <{conn-100027} Connection>
> 2008-01-24 10:45:49,292 DEBUG [java.sql.Connection] -
> <{conn-100027} Preparing Statement: select
> s.SearchID, s.FieldID, s.UserID, s.SearchType,
> s.timestamp, convert(datetime, convert(varchar,
> s.Timestamp, 104), 104) as thedate, s.Thesaurus,
> s.UserSaved, s.SuccessfulSearch, s.FieldValue,
> f.FieldName, f.methodName from
> EU_Users_Saved_Searches s inner join
> wf_Prospectus_Fields f on s.FieldId = f.ID inner
> join ( select top 10 s.SearchID , max(s.Timestamp)
> as timestamp from EU_Users_Saved_Searches s where
> s.UserID = ? and s.SearchType = ?
> group by s.SearchID order by max(s.Timestamp) desc
> ) as sub1 on s.SearchID = sub1.SearchID order by
> s.Timestamp desc >
> 2008-01-24 10:45:49,292 DEBUG
> [java.sql.PreparedStatement] - <{pstm-100028}
> Executing Statement: select s.SearchID, s.FieldID,
> s.UserID, s.SearchType, s.timestamp, convert(datetime,
> convert(varchar, s.Timestamp, 104), 104) as thedate,
> s.Thesaurus, s.UserSaved, s.SuccessfulSearch,
> s.FieldValue, f.FieldName, f.methodName from
> EU_Users_Saved_Searches s inner join
> wf_Prospectus_Fields f on s.FieldId = f.ID inner
> join ( select top 10 s.SearchID , max(s.Timestamp)
> as timestamp from EU_Users_Saved_Searches s where
> s.UserID = ? and s.SearchType = ?
> group by s.SearchID order by max(s.Timestamp) desc
> ) as sub1 on s.SearchID = sub1.SearchID order by
> s.Timestamp desc >
> 2008-01-24 10:45:49,292 DEBUG
> [java.sql.PreparedStatement] - <{pstm-100028}
> Parameters: [4163, 1]>
> 2008-01-24 10:45:49,292 DEBUG
> [java.sql.PreparedStatement] - <{pstm-100028} Types:
> [java.lang.Integer, java.lang.Integer]>
> 2008-01-24 10:46:09,870 INFO
> [org.springframework.beans.factory.xml.XmlBeanDefinitionReader]
> - <Loading XML bean definitions from class path
> resource
> [org/springframework/jdbc/support/sql-error-codes.xml]>
> 2008-01-24 10:46:09,917 INFO
> [org.springframework.jdbc.support.SQLErrorCodesFactory]
> - <SQLErrorCodes loaded: [DB2, Derby, H2, HSQL,
> Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase]>
> org.springframework.jdbc.UncategorizedSQLException:
> SqlMapClient operation; uncategorized SQLException for
> SQL []; SQL state [S1T00]; error code [0];
> --- The error occurred while applying a parameter map.
>
> --- Check the
> EU_Users_Saved_Searches.getSearchesByUser-InlineParameterMap.
>
> --- Check the statement (query failed).
> --- Cause: java.sql.SQLException: [TDS Driver]Read
> timed out; nested exception is
> com.ibatis.common.jdbc.exception.NestedSQLException:
>
> --- The error occurred while applying a parameter map.
>
> --- Check the
> EU_Users_Saved_Searches.getSearchesByUser-InlineParameterMap.
>
> --- Check the statement (query failed).
> --- Cause: java.sql.SQLException: [TDS Driver]Read
> timed out
> Caused by:
> com.ibatis.common.jdbc.exception.NestedSQLException:
>
> --- The error occurred while applying a parameter map.
>
> --- Check the
> EU_Users_Saved_Searches.getSearchesByUser-InlineParameterMap.
>
> --- Check the statement (query failed).
> --- Cause: java.sql.SQLException: [TDS Driver]Read
> timed out
> at
> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:185)
> at
> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForList(GeneralStatement.java:123)
> at
> com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:615)
> at
> com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:589)
> at
> com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(SqlMapSessionImpl.java:118)
> at
> org.springframework.orm.ibatis.SqlMapClientTemplate$3.doInSqlMapClient(SqlMapClientTemplate.java:268)
> at
> org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:193)
> at
> org.springframework.orm.ibatis.SqlMapClientTemplate.executeWithListResult(SqlMapClientTemplate.java:219)
> at
> org.springframework.orm.ibatis.SqlMapClientTemplate.queryForList(SqlMapClientTemplate.java:266)
> at
> com.globalfilings.domain.dao.SavedSearchDAOImpl.getUserSearches(SavedSearchDAOImpl.java:128)
> at
> com.globalfilings.services.UserServices.getSavedSearchesByUser(UserServices.java:195)
> 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:304)
> at
> org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:172)
> at
> org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:139)
> at
> org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:107)
> at
> org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:161)
> at
> org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
> at $Proxy6.getSavedSearchesByUser(Unknown Source)
> at
> com.globalfilings.controller.useraccount.UsersAccountSearchesFormController.onSubmit(UsersAccountSearchesFormController.java:52)
> at
> org.springframework.web.servlet.mvc.SimpleFormController.processFormSubmission(SimpleFormController.java:267)
> at
> org.springframework.web.servlet.mvc.AbstractFormController.handleRequestInternal(AbstractFormController.java:250)
> at
> org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:153)
> at
> org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:48)
> at
> org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:857)
> at
> org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:792)
> at
> org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:461)
> at
> org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:426)
> at
> javax.servlet.http.HttpServlet.service(HttpServlet.java:709)
> at
> javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
> at
> org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
> at
> org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
> at
> org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:265)
> at
> org.acegisecurity.intercept.web.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:97)
> at
> org.acegisecurity.intercept.web.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:72)
> at
> org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
> at
> org.acegisecurity.ui.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:110)
> at
> org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
> at
> org.acegisecurity.ui.rememberme.RememberMeProcessingFilter.doFilter(RememberMeProcessingFilter.java:142)
> at
> org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
> at
> com.globalfilings.security.UserCookieWriteFilter.doFilter(UserCookieWriteFilter.java:75)
> at
> org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
> at
> com.globalfilings.security.IntranetUserCookieFilter.doFilter(IntranetUserCookieFilter.java:75)
> at
> org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
> at
> org.acegisecurity.ui.AbstractProcessingFilter.doFilter(AbstractProcessingFilter.java:229)
> at
> org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
> at
> com.globalfilings.security.AbstractUserProcessingFilter.doFilter(AbstractUserProcessingFilter.java:65)
> at
> org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
> at
> com.globalfilings.security.AbstractUserProcessingFilter.doFilter(AbstractUserProcessingFilter.java:65)
> at
> org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
> at
> com.globalfilings.security.AbstractUserProcessingFilter.doFilter(AbstractUserProcessingFilter.java:65)
> at
> org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
> at
> org.acegisecurity.ui.logout.LogoutFilter.doFilter(LogoutFilter.java:106)
> at
> org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
> at
> org.acegisecurity.context.HttpSessionContextIntegrationFilter.doFilter(HttpSessionContextIntegrationFilter.java:198)
> at
> org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
> at
> com.globalfilings.security.ExpiredSubscriptionFilter.doFilter(ExpiredSubscriptionFilter.java:66)
> at
> org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
> at
> org.acegisecurity.concurrent.ConcurrentSessionFilter.doFilter(ConcurrentSessionFilter.java:95)
> at
> org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
> at
> org.acegisecurity.util.FilterChainProxy.doFilter(FilterChainProxy.java:149)
> at
> org.acegisecurity.util.FilterToBeanProxy.doFilter(FilterToBeanProxy.java:98)
> at
> org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
> at
> org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
> at
> org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:265)
> at
> org.acegisecurity.intercept.web.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:107)
> at
> org.acegisecurity.intercept.web.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:72)
> at
> org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
> at
> org.acegisecurity.ui.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:110)
> at
> org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
> at
> org.acegisecurity.ui.rememberme.RememberMeProcessingFilter.doFilter(RememberMeProcessingFilter.java:142)
> at
> org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
> at
> com.globalfilings.security.UserCookieWriteFilter.doFilter(UserCookieWriteFilter.java:75)
> at
> org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
> at
> com.globalfilings.security.IntranetUserCookieFilter.doFilter(IntranetUserCookieFilter.java:75)
> at
> org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
> at
> org.acegisecurity.ui.AbstractProcessingFilter.doFilter(AbstractProcessingFilter.java:229)
> at
> org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
> at
> com.globalfilings.security.AbstractUserProcessingFilter.doFilter(AbstractUserProcessingFilter.java:65)
> at
> org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
> at
> com.globalfilings.security.AbstractUserProcessingFilter.doFilter(AbstractUserProcessingFilter.java:65)
> at
> org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
> at
> com.globalfilings.security.AbstractUserProcessingFilter.doFilter(AbstractUserProcessingFilter.java:65)
> at
> org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
> at
> org.acegisecurity.ui.logout.LogoutFilter.doFilter(LogoutFilter.java:106)
> at
> org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
> at
> org.acegisecurity.context.HttpSessionContextIntegrationFilter.doFilter(HttpSessionContextIntegrationFilter.java:286)
> at
> org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
> at
> com.globalfilings.security.ExpiredSubscriptionFilter.doFilter(ExpiredSubscriptionFilter.java:66)
> at
> org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
> at
> org.acegisecurity.concurrent.ConcurrentSessionFilter.doFilter(ConcurrentSessionFilter.java:95)
> at
> org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
> at
> org.acegisecurity.util.FilterChainProxy.doFilter(FilterChainProxy.java:149)
> at
> org.acegisecurity.util.FilterToBeanProxy.doFilter(FilterToBeanProxy.java:98)
> at
> org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
> at
> org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
> at
> org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
> at
> org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
> at
> org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
> at
> org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
> at
> org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:526)
> at
> org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
> at
> org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
> at
> org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:856)
> at
> org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:744)
> at
> org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
> at
> org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
> at
> org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
> at java.lang.Thread.run(Thread.java:595)
> Caused by: java.sql.SQLException: [TDS Driver]Read
> timed out
> at com.inet.tds.TdsDriver.a(Unknown Source)
> at com.inet.tds.a.a(Unknown Source)
> at com.inet.tds.a.a(Unknown Source)
> at com.inet.tds.a.a(Unknown Source)
> at com.inet.tds.b.try(Unknown Source)
> at com.inet.tds.b.execute(Unknown Source)
> at
> org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:168)
> 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
> com.ibatis.common.jdbc.logging.PreparedStatementLogProxy.invoke(PreparedStatementLogProxy.java:62)
> at $Proxy11.execute(Unknown Source)
> at
> com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQuery(SqlExecutor.java:186)
> at
> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.sqlExecuteQuery(GeneralStatement.java:205)
> at
> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:173)
> ... 111 more
> 2008-01-24 10:46:11,620 ERROR
> [com.globalfilings.resolver.NotSoSimpleMappingExceptionResolver]
> - <Something has gone badly awry>
>
> When attempting to execute this query:
>
> <resultMap id="SavedSearchResultWithFields"
> class="com.globalfilings.domain.SavedSearch"
> groupBy="searchId">
> <result column="SearchID" property="searchId"
> jdbcType="VARCHAR" />
> <result column="UserID" property="userId"
> jdbcType="INTEGER" />
> <result column="SearchType" property="searchType"
> jdbcType="INTEGER" />
> <result column="timestamp" property="timestamp"
> jdbcType="TIMESTAMP" />
> <result column="Thesaurus"
> typeHandler="booleanHandler" property="thesaurus"
> jdbcType="BIT" />
> <result column="UserSaved"
> typeHandler="booleanHandler" property="userSaved"
> jdbcType="BIT" />
> <result column="SuccessfulSearch"
> typeHandler="booleanHandler"
> property="successfulSearch" jdbcType="BIT" />
> <result property="searchFields"
> resultMap="EU_Users_Saved_Searches.SearchFieldResult"
> />
> </resultMap>
> <resultMap id="SearchFieldResult"
> class="com.globalfilings.domain.SearchField"
> groupBy="id">
> <result column="FieldID" property="id"
> jdbcType="INTEGER" />
> <result column="FieldName" property="fieldName"
> jdbcType="VARCHAR" />
> <result column="methodName" property="methodName"
> jdbcType="VARCHAR" />
> <result column="FieldValue" property="value"
> jdbcType="CLOB" />
> </resultMap>
> <select id="getSearchesByUser"
> resultMap="SavedSearchResultWithFields"
> parameterClass="com.globalfilings.domain.search.SavedSearchesSearch"
> >
> select s.SearchID, s.FieldID, s.UserID,
> s.SearchType, s.timestamp, convert(datetime,
> convert(varchar, s.Timestamp, 104), 104) as thedate,
> s.Thesaurus, s.UserSaved, s.SuccessfulSearch,
> s.FieldValue, f.FieldName, f.methodName
> from EU_Users_Saved_Searches s
> inner join wf_Prospectus_Fields f on s.FieldId =
> f.ID
> inner join (
> select top 10 s.SearchID , max(s.Timestamp) as
> timestamp from EU_Users_Saved_Searches s
> where s.UserID = #userId:INTEGER#
> and s.SearchType = #searchType:INTEGER#
> <isGreaterThan property="userSaved"
> compareValue="0">
> and s.UserSaved = #userSaved:SHORT#
> </isGreaterThan>
> group by s.SearchID
> order by max(s.Timestamp) desc
> ) as sub1
> on s.SearchID = sub1.SearchID
> order by s.Timestamp desc
> </select>
>
> I think it times out after 10 seconds but the weird
> thing is - if I run the exact same query which is
> actually this:
>
> select s.SearchID, s.FieldID, s.UserID, s.SearchType,
> s.timestamp, convert(datetime, convert(varchar,
> s.Timestamp, 104), 104) as thedate,
> s.Thesaurus, s.UserSaved,
> s.SuccessfulSearch, s.FieldValue, f.FieldName,
> f.methodName
> from EU_Users_Saved_Searches s
> inner join wf_Prospectus_Fields f on s.FieldId = f.ID
>
> inner join (
> select top 10 s.SearchID , max(s.Timestamp) as
> timestamp
> from EU_Users_Saved_Searches s
> where s.UserID = 4163 and s.SearchType = 1
> group by s.SearchID order by max(s.Timestamp)
> desc
> ) as sub1
> on s.SearchID = sub1.SearchID
> order by s.Timestamp desc
>
> in the SQL server query analyser from the same remote
> box that our dev app runs on connected to the same SQL
> server the query return in less than 4 seconds. I've
> tried tweaking the driver configuration xml (maxWait,
> loginTimeout, queryTimeout) and whatever I do it still
> explodes at this point.
>
> I'm stumped - does anyone have any clues to how I
> change the timeout or indeed why jdbc is taking so
> damn long!
>
> Thanks all
>
> Charlie
>
>
> ___________________________________________________________
> Support the World Aids Awareness campaign this month with Yahoo! For Good
> http://uk.promotions.yahoo.com/forgood/
>
