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/
