Use "select 1 from dual" instead.
On Tue, Oct 6, 2009 at 10:57 AM, Jasmin Mehta <jasmin_me...@nexweb.org>wrote:
> I have changed my spring.xml accordingly:
>
> <bean id="dataSource"
> class="org.apache.commons.dbcp.BasicDataSource"
> destroy-method="close">
> <property name="driverClassName" value="${jdbc.driverClassName}"/>
> <property name="url" value="${jdbc.url}"/>
> <property name="username" value="${jdbc.username}"/>
> <property name="password" value="${jdbc.password}"/>
> <property name="validationQuery" value="SELECT 1"/>
> <property name="testWhileIdle" value="true"/>
> <property name="timeBetweenEvictionRunsMillis" value="300000"/>
> <property name="numTestsPerEvictionRun" value="6"/>
> <property name="minEvictableIdleTimeMillis" value="1800000"/>
> </bean>
>
> But now I am getting runtime exception:
>
> [06 Oct 2009 11:32:13] DEBUG [UserSqlMapDAO] Opened SqlMapSession
> [com.ibatis.sqlmap.engine.impl.sqlmapsessioni...@282f55] for iBATIS
> operation
> [06 Oct 2009 11:32:13] DEBUG [DataSourceUtils] Fetching JDBC Connection
> from DataSource
> [06 Oct 2009 11:32:13] ERROR [WelcomeAction]
> org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get
> JDBC Connection; nested exception is
> org.apache.commons.dbcp.SQLNestedException: Cannot create
> PoolableConnectionFactory (*ORA-00923: FROM keyword not found where
> expected*
> )
> at
> org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:204)
> at
> org.springframework.orm.ibatis.SqlMapClientTemplate.queryForObject(SqlMapClientTemplate.java:271)
> at
> org.nexweb.qol.gcc.ibatis.dao.sqlmaps.UserSqlMapDAO.getUserByUserNameAndRealmName(UserSqlMapDAO.java:26)
> at
> org.nexweb.qol.gcc.ibatis.services.UserService.getUserByUserNameAndRealmName(UserService.java:30)
> at
> org.nexweb.qol.gcc.actions.WelcomeAction.execute(WelcomeAction.java:76)
> at
> org.apache.struts.chain.commands.servlet.ExecuteAction.execute(ExecuteAction.java:58)
> at
> org.apache.struts.chain.commands.AbstractExecuteAction.execute(AbstractExecuteAction.java:67)
> at
> org.apache.struts.chain.commands.ActionCommandBase.execute(ActionCommandBase.java:51)
> at
> org.apache.commons.chain.impl.ChainBase.execute(ChainBase.java:190)
> at
> org.apache.commons.chain.generic.LookupCommand.execute(LookupCommand.java:304)
> at
> org.apache.commons.chain.impl.ChainBase.execute(ChainBase.java:190)
> at
> org.apache.struts.chain.ComposableRequestProcessor.process(ComposableRequestProcessor.java:283)
> at
> org.apache.struts.action.ActionServlet.process(ActionServlet.java:1913)
> at
> org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:449)
> at javax.servlet.http.HttpServlet.service(HttpServlet.java:743)
> at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
> at
> com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:713)
> at
> com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:370)
> at
> com.evermind.server.http.ServletRequestDispatcher.unprivileged_forward(ServletRequestDispatcher.java:259)
> at
> com.evermind.server.http.ServletRequestDispatcher.access$100(ServletRequestDispatcher.java:51)
> at
> com.evermind.server.http.ServletRequestDispatcher$2.oc4jRun(ServletRequestDispatcher.java:193)
> at
> oracle.oc4j.security.OC4JSecurity.doPrivileged(OC4JSecurity.java:283)
> at
> com.evermind.server.http.ServletRequestDispatcher.forward(ServletRequestDispatcher.java:198)
> at
> com.evermind.server.http.EvermindPageContext.forward(EvermindPageContext.java:392)
> at _jsp._forward._jspService(_forward.java:48)
> at
> com.orionserver.http.OrionHttpJspPage.service(OrionHttpJspPage.java:59)
> at
> oracle.jsp.runtimev2.JspPageTable.compileAndServe(JspPageTable.java:724)
> at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:414)
> at
> oracle.jsp.runtimev2.JspServlet.internalService(JspServlet.java:594)
> at oracle.jsp.runtimev2.JspServlet.service(JspServlet.java:518)
> at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
> at
> com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:713)
> at
> com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:370)
> at
> com.evermind.server.http.HttpRequestHandler.doProcessRequest(HttpRequestHandler.java:871)
> at
> com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:453)
> at
> com.evermind.server.http.HttpRequestHandler.serveOneRequest(HttpRequestHandler.java:221)
> at
> com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:122)
> at
> com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:111)
> at
> oracle.oc4j.network.ServerSocketReadHandler$SafeRunnable.run(ServerSocketReadHandler.java:260)
> at
> com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:303)
> at java.lang.Thread.run(Thread.java:595)
> Caused by: org.apache.commons.dbcp.SQLNestedException: Cannot create
> PoolableConnectionFactory (ORA-00923: FROM keyword not found where expected
> )
> at
> org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1225)
> at
> org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:880)
> at
> org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:113)
> at
> org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy.getConnection(TransactionAwareDataSourceProxy.java:109)
> at
> org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:190)
> ... 40 more
> Caused by: java.sql.SQLException: ORA-00923: FROM keyword not found where
> expected
>
> at
> oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:74)
> at
> oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:131)
> at
> oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:204)
> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
> at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034)
> at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:183)
> at
> oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:780)
> at
> oracle.jdbc.driver.T4CStatement.executeMaybeDescribe(T4CStatement.java:855)
> at
> oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1186)
> at
> oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1377)
> at
> oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:387)
> at
> org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
> at
> org.apache.commons.dbcp.PoolableConnectionFactory.validateConnection(PoolableConnectionFactory.java:332)
> at
> org.apache.commons.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:1249)
> at
> org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1221)
> ... 44 more
>
>
>
>
>
> From: "meindert" <meind...@eduflex.com> To: <user-java@ibatis.apache.org>
> Date: 10/06/2009 10:23 AM Subject: RE: Database Connection reset on Oracle
> 10g AS using Oracle 11g database
> ------------------------------
>
>
>
>
> I’m using;
>
> <bean id=*"propertyConfigurer"*
>
> class=*
> "org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"*>
>
> <property name=*"location"* value=*"WEB-INF/config.properties"
> * />
>
> </bean>
>
> <bean id=*"dataSource"* class=*
> "org.apache.commons.dbcp.BasicDataSource"* destroy-method=*"close"*>
>
> <property name=*"driverClassName"* value=*"${database_driver}"
> * />
>
> <property name=*"url"* value=*"${database_url}"* />
>
> <property name=*"username"* value=*"${database_user}"* />
>
> <property name=*"password"* value=*"${database_password}"* />
>
> <property name=*"defaultAutoCommit"* value=*"true"*/>
>
> <property name=*"defaultTransactionIsolation"* value=*"2"*/>
>
> <property name=*"maxActive"* value=*"10"*/>
>
> <property name=*"removeAbandoned"* value=*"true"*/>
>
> <property name=*"removeAbandonedTimeout"* value=*"30"*/>
>
> <property name=*"logAbandoned"* value=*"false"*/>
>
> <property name=*"validationQuery"* value=*"select 1"*/>
>
> </bean>
>
> Hope it helps
>
>
>
> *From:* Jasmin Mehta [mailto:jasmin_me...@nexweb.org<jasmin_me...@nexweb.org>]
>
> *Sent:* 06 October 2009 04:05 PM
> *To:* user-java@ibatis.apache.org
> *Cc:* lmead...@apache.org
> *Subject:* Database Connection reset on Oracle 10g AS using Oracle 11g
> database
>
>
>
> Larry,
>
> I saw an answer for one of the thread
>
> *http://www.mail-archive.com/user-java@ibatis.apache.org/msg03522.html*<http://www.mail-archive.com/user-java@ibatis.apache.org/msg03522.html>
>
> I have something similar, How do I modify spring.xml file to use
> BasicDataSource feature to ping the query?
>
>
> I have J2EE application running on OC4J application server. Where the
> database connection is dropping / resetting after some idle time. The idle
> time varies from 20 mins to 90 mins. The persistence layer is created using
> Spring and iBatis with Struts as controller.
>
> I am not able to find that why the code / oracle database is trying to
> close the connection before it makes new one.
>
> Here is the exception log:
> [05 Oct 2009 11:55:38] DEBUG [SQLErrorCodesFactory] Looking up default
> SQLErrorCodes for DataSource
> [org.springframework.jdbc.datasource.transactionawaredatasourcepr...@13478c8
> ]
> [05 Oct 2009 11:55:38] DEBUG [DataSourceUtils] Fetching JDBC Connection
> from DataSource
> [05 Oct 2009 11:55:38] DEBUG [DataSourceUtils] Fetching JDBC Connection
> from DataSource
> [05 Oct 2009 11:55:38] DEBUG [DataSourceUtils] Returning JDBC Connection to
> DataSource
> [05 Oct 2009 11:55:38] DEBUG [DataSourceUtils] Returning JDBC Connection to
> DataSource
> [05 Oct 2009 11:55:38] DEBUG [SQLErrorCodesFactory] Database product name
> cached for DataSource
> [org.springframework.jdbc.datasource.transactionawaredatasourcepr...@13478c8]:
> name is 'Oracle'
> [05 Oct 2009 11:55:38] DEBUG [SQLErrorCodesFactory] SQL error codes for
> 'Oracle' found
> [05 Oct 2009 11:55:38] DEBUG [SQLErrorCodeSQLExceptionTranslator] *Translating
> SQLException with SQL state '08006', error code '17002', message [ *
> --- The error occurred in org/nexweb/qol/gcc/ibatis/dao/xml/User.xml.
> --- The error occurred while applying a parameter map.
> --- Check the getUserByUserNameAndRealmName-InlineParameterMap.
> --- Check the statement (query failed).
> --- Cause: java.sql.SQLException: *Io exception: Connection reset by peer:
> socket write error]*; SQL was [] for task [SqlMapClient operation]
> [05 Oct 2009 11:55:38] DEBUG [DataSourceUtils] Returning JDBC Connection to
> DataSource
> [05 Oct 2009 11:55:38] DEBUG [UserSqlMapDAO] *Could not close JDBC
> Connection*
> *java.sql.SQLException: Already closed.*
> at
> org.apache.commons.dbcp.PoolableConnection.close(PoolableConnection.java:84)
> at
> org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.close(PoolingDataSource.java:181)
> at
> org.springframework.jdbc.datasource.DataSourceUtils.doReleaseConnection(DataSourceUtils.java:313)
> at
> org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy$TransactionAwareInvocationHandler.invoke(TransactionAwareDataSourceProxy.java:166)
> at $Proxy0.close(Unknown Source)
> at
> org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:218)
> at
> org.springframework.orm.ibatis.SqlMapClientTemplate.queryForObject(SqlMapClientTemplate.java:271)
> at
> org.nexweb.qol.gcc.ibatis.dao.sqlmaps.UserSqlMapDAO.getUserByUserNameAndRealmName(UserSqlMapDAO.java:26)
> at
> org.nexweb.qol.gcc.ibatis.services.UserService.getUserByUserNameAndRealmName(UserService.java:30)
> at
> org.nexweb.qol.gcc.actions.WelcomeAction.execute(WelcomeAction.java:71)
> at
> org.apache.struts.chain.commands.servlet.ExecuteAction.execute(ExecuteAction.java:58)
> at
> org.apache.struts.chain.commands.AbstractExecuteAction.execute(AbstractExecuteAction.java:67)
> at
> org.apache.struts.chain.commands.ActionCommandBase.execute(ActionCommandBase.java:51)
> at
> org.apache.commons.chain.impl.ChainBase.execute(ChainBase.java:190)
> at
> org.apache.commons.chain.generic.LookupCommand.execute(LookupCommand.java:304)
> at
> org.apache.commons.chain.impl.ChainBase.execute(ChainBase.java:190)
> at
> org.apache.struts.chain.ComposableRequestProcessor.process(ComposableRequestProcessor.java:283)
> at
> org.apache.struts.action.ActionServlet.process(ActionServlet.java:1913)
> at
> org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:449)
> at javax.servlet.http.HttpServlet.service(HttpServlet.java:743)
> at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
> at
> com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:713)
> at
> com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:370)
> at
> com.evermind.server.http.ServletRequestDispatcher.unprivileged_forward(ServletRequestDispatcher.java:259)
> at
> com.evermind.server.http.ServletRequestDispatcher.access$100(ServletRequestDispatcher.java:51)
> at
> com.evermind.server.http.ServletRequestDispatcher$2.oc4jRun(ServletRequestDispatcher.java:193)
> at
> oracle.oc4j.security.OC4JSecurity.doPrivileged(OC4JSecurity.java:283)
> at
> com.evermind.server.http.ServletRequestDispatcher.forward(ServletRequestDispatcher.java:198)
> at
> com.evermind.server.http.EvermindPageContext.forward(EvermindPageContext.java:392)
> at _jsp._forward._jspService(_forward.java:48)
> at
> com.orionserver.http.OrionHttpJspPage.service(OrionHttpJspPage.java:59)
> at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:462)
> at
> oracle.jsp.runtimev2.JspServlet.internalService(JspServlet.java:594)
> at oracle.jsp.runtimev2.JspServlet.service(JspServlet.java:518)
> at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
> at
> com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:713)
> at
> com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:370)
> at
> com.evermind.server.http.HttpRequestHandler.doProcessRequest(HttpRequestHandler.java:871)
> at
> com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:453)
> at
> com.evermind.server.http.HttpRequestHandler.serveOneRequest(HttpRequestHandler.java:221)
> at
> com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:122)
> at
> com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:111)
> at
> oracle.oc4j.network.ServerSocketReadHandler$SafeRunnable.run(ServerSocketReadHandler.java:260)
> at
> com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:303)
> at java.lang.Thread.run(Thread.java:595)
>
> Here is the Spring.xml code related to datasource :
>
>
>
> <?xml version="1.0" encoding="UTF-8"?>
> <!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN 2.0//EN"
> "*
> http://www.springframework.org/dtd/spring-beans-2.0.dtd*<http://www.springframework.org/dtd/spring-beans-2.0.dtd>
> ">
> <beans>
> <bean id="propertyConfigurer"
>
>
> class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
> <property name="location" value="classpath:jdbc.properties"/>
> </bean>
>
> <bean id="dataSource"
> class=" org.apache.commons.dbcp.BasicDataSource">
> <property name="driverClassName" value="${jdbc.driverClassName}"/>
> <property name="url" value="${jdbc.url}"/>
> <property name="username" value="${jdbc.username}"/>
> <property name="password" value="${jdbc.password}"/>
> </bean>
>
> <bean id="sqlMapClient"
> class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
> <property name="configLocation">
>
> <value>classpath:org/nexweb/qol/gcc/ibatis/config/SqlMapConfig.xml</value>
> </property>
>
> <property name="transactionConfigProperties">
> <props>
> <prop key="autoCommitAllowed">true</prop>
> </props>
> </property>
>
> <property name="useTransactionAwareDataSource">
> <value>true</value>
> </property>
> <property name="transactionConfigClass">
>
>
> <value>com.ibatis.sqlmap.engine.transaction.jdbc.JdbcTransactionConfig</value>
> </property>
> <property name="dataSource" ref="dataSource"/>
> </bean>
>
>
>
>
>
> ******************************************************************************
> This email and any files transmitted with it are intended solely for
> the use of the individual or agency to whom they are addressed.
> If you have received this email in error please notify the Navy
> Exchange Service Command e-mail administrator. This footnote
> also confirms that this email message has been scanned for the
> presence of computer viruses.
>
> Thank You!
> *
> ******************************************************************************
> *
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 8.5.420 / Virus Database: 270.14.3/2414 - Release Date: 10/05/09
> 18:23:00
>
