I've setup a datasource as <Resource auth="Container" url="jdbc:oracle:thin:@dgalimov7:1521:DAN2" driverClassName="oracle.jdbc.OracleDriver" factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory" name="jdbc/IpbDB" username="dgalimov_home_new" password="dgalimov_home_new" type="javax.sql.DataSource" timeBetweenEvictionRunsMillis="10000" maxActive="25" maxIdle="3" minIdle="0" maxWait="30000" testOnBorrow="true" testOnReturn="true" validationQuery="SELECT 1 FROM DUAL" /> However, if I change factory from org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory to org.apache.tomcat.jdbc.pool.DataSourceFactory, after a while I'm getting errors like ORA-01000: maximum open cursors exceeded (although I've set 1000 cursors limit). I'm using this query to get the cursors count: select max(a.value) as highest_open_cur, p.value as max_open_cur from v$sesstat a, v$statname b, v$parameter p where a.statistic# = b.statistic# and b.name = 'opened cursors current' and p.name= 'open_cursors' group by p.value; I've tried adding defaultAutoCommit="true" when using DataSourceFactory, but it didn't help.
Correct product
Have you enabled "abandoned" logging? I believe tomcat-pool is less kind than DBCP when you have resource leaks in your application.
No, could you please clarify how can I do that?
Not sure this would be a bug. It is your responsibility to close statements in your application. However, you can configure the pool to do it for you https://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html#org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer in your config that would look like jdbcInterceptors="org.apache.tomcat.jdbc.pool.interceptor.ConnectionState; org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer" <Resource auth="Container" url="jdbc:oracle:thin:@dgalimov7:1521:DAN2" driverClassName="oracle.jdbc.OracleDriver" factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" name="jdbc/IpbDB" username="dgalimov_home_new" password="dgalimov_home_new" type="javax.sql.DataSource" timeBetweenEvictionRunsMillis="10000" maxActive="25" maxIdle="3" minIdle="0" maxWait="30000" testOnBorrow="true" testOnReturn="true" validationQuery="SELECT 1 FROM DUAL" jdbcInterceptors="org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer" />
Yes, it helped. Thanks.
If the StatementFinalizer fixed your problem, then you should take some time to look into your application and correct those errors. Head over to the users' mailing list if you'd like some help with that.
Thank you :-) Ideally I would like to have something like stacktrace of statement opening, and stacktrace of connection close, so when connection is closed, all of the opened and not closed prepared statments, connections, result sets e.t.c will be printed with creation trace, so I will be able to check this. Is it possible to do with currently available jdbc interceptors (or maybe I need to setup something like proxy jdbc drivers) ? Unfortunately the project is very big and we're using a lot of libraries, so it's hard to get the exact place without logging.
(In reply to Danila Galimov from comment #7) > Thank you :-) > > Ideally I would like to have something like stacktrace of statement opening, > and stacktrace of connection close, so when connection is closed, all of the > opened and not closed prepared statments, connections, result sets e.t.c > will be printed with creation trace, so I will be able to check this. > > Is it possible to do with currently available jdbc interceptors (or maybe I > need to setup something like proxy jdbc drivers) ? > > Unfortunately the project is very big and we're using a lot of libraries, so > it's hard to get the exact place without logging. Writing your own is very simple, take a look at the code for the StatementFinalizer http://svn.apache.org/viewvc/tomcat/tc7.0.x/trunk/modules/jdbc-pool/src/main/java/org/apache/tomcat/jdbc/pool/interceptor/StatementFinalizer.java?view=markup And you can easily add in options to track stack traces.
Created attachment 31467 [details] StatementFinalizer patch
I've created an patch for StatementFinalizer class. It allows to print the creation stack, if configured in a way like jdbcInterceptors="org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer(logCreationStack=true)". Otherwise, it works almost the same way as original version. I think it may be useful to detect unclosed statements in the code. Please integrate it.
(In reply to Danila Galimov from comment #10) > I've created an patch for StatementFinalizer class. It allows to print the > creation stack, if configured in a way like > jdbcInterceptors="org.apache.tomcat.jdbc.pool.interceptor. > StatementFinalizer(logCreationStack=true)". Otherwise, it works almost the > same way as original version. > I think it may be useful to detect unclosed statements in the code. Please > integrate it. How is this different than setting logAbandoned="true"?
When logAbandoned is set to true, when connection is closed, but any statements are left unclosed, you'll get this message in the catalina.log and/or console (depends on the logger configuration): WARNING: Statement created, but was not closed java.lang.Throwable at org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer$StatementEntry.<init>(StatementFinalizer.java:98) at org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer.createStatement(StatementFinalizer.java:46) at org.apache.tomcat.jdbc.pool.interceptor.AbstractCreateStatementInterceptor.invoke(AbstractCreateStatementInterceptor.java:69) at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:109) at org.apache.tomcat.jdbc.pool.interceptor.ConnectionState.invoke(ConnectionState.java:153) at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:109) at org.apache.tomcat.jdbc.pool.TrapException.invoke(TrapException.java:41) at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:109) at org.apache.tomcat.jdbc.pool.DisposableConnectionFacade.invoke(DisposableConnectionFacade.java:80) at com.sun.proxy.$Proxy14.prepareCall(Unknown Source) at com.exigen.cm.database.ConnectionProviderImpl.findDialect(ConnectionProviderImpl.java:258) at com.exigen.cm.database.ConnectionProviderImpl.configure(ConnectionProviderImpl.java:191) at com.exigen.cm.impl.RepositoryConfiguration.checkFillConfiguration(RepositoryConfiguration.java:375) at com.exigen.cm.impl.RepositoryConfiguration.getRepositoryConfiguration(RepositoryConfiguration.java:238) at com.exigen.cm.impl.RepositoryProviderImpl.getRepository(RepositoryProviderImpl.java:42) at com.exigen.cm.impl.spring.LocalExigenJcrRepositoryBean.getObject(LocalExigenJcrRepositoryBean.java:74) at org.springframework.beans.factory.support.FactoryBeanRegistrySupport.doGetObjectFromFactoryBean(FactoryBeanRegistrySupport.java:142) at org.springframework.beans.factory.support.FactoryBeanRegistrySupport.getObjectFromFactoryBean(FactoryBeanRegistrySupport.java:102) at org.springframework.beans.factory.support.AbstractBeanFactory.getObjectForBeanInstance(AbstractBeanFactory.java:1442) at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:305) at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:193) at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveReference(BeanDefinitionValueResolver.java:322) at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveValueIfNecessary(BeanDefinitionValueResolver.java:106) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.applyPropertyValues(AbstractAutowireCapableBeanFactory.java:1360) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1118) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:517) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:456) at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:294) at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:223) at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:291) at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:193) at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveReference(BeanDefinitionValueResolver.java:322) at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveValueIfNecessary(BeanDefinitionValueResolver.java:106) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.applyPropertyValues(AbstractAutowireCapableBeanFactory.java:1360) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1118) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:517) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:456) at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:294) at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:223) at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:291) at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:193) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.autowireByName(AbstractAutowireCapableBeanFactory.java:1136) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1086) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:517) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:456) at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:294) at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:223) at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:291) at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:197) at org.springframework.beans.factory.support.DefaultListableBeanFactory.getBeansOfType(DefaultListableBeanFactory.java:400) at org.springframework.beans.factory.support.DefaultListableBeanFactory.getBeansOfType(DefaultListableBeanFactory.java:390) at org.springframework.context.support.AbstractApplicationContext.getBeansOfType(AbstractApplicationContext.java:1158) at com.exigen.ipb.bam.DefaultActivityDefinitionsRegistry.init(DefaultActivityDefinitionsRegistry.java:47) 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:597) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeCustomInitMethod(AbstractAutowireCapableBeanFactory.java:1581) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1522) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1452) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:519) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:456) at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:294) at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:223) at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:291) at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:193) at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveReference(BeanDefinitionValueResolver.java:322) at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveValueIfNecessary(BeanDefinitionValueResolver.java:106) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.applyPropertyValues(AbstractAutowireCapableBeanFactory.java:1360) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1118) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:517) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:456) at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:294) at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:223) at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:291) at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:193) at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveReference(BeanDefinitionValueResolver.java:322) at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveValueIfNecessary(BeanDefinitionValueResolver.java:106) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.applyPropertyValues(AbstractAutowireCapableBeanFactory.java:1360) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1118) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:517) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:456) at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:294) at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:223) at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:291) at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:193) at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveReference(BeanDefinitionValueResolver.java:322) at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveValueIfNecessary(BeanDefinitionValueResolver.java:106) at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveManagedList(BeanDefinitionValueResolver.java:352) at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveValueIfNecessary(BeanDefinitionValueResolver.java:153) at org.springframework.beans.factory.support.ConstructorResolver.resolveConstructorArguments(ConstructorResolver.java:629) at org.springframework.beans.factory.support.ConstructorResolver.autowireConstructor(ConstructorResolver.java:148) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.autowireConstructor(AbstractAutowireCapableBeanFactory.java:1035) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:939) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:485) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:456) at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:294) at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:223) at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:291) at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:193) at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveReference(BeanDefinitionValueResolver.java:322) at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveValueIfNecessary(BeanDefinitionValueResolver.java:106) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.applyPropertyValues(AbstractAutowireCapableBeanFactory.java:1360) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1118) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:517) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:456) at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:294) at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:223) at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:291) at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:193) at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:569) at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:913) at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:464) at org.springframework.web.context.ContextLoader.configureAndRefreshWebApplicationContext(ContextLoader.java:385) at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:284) at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:111) at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4973) at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5467) at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150) at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:901) at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:877) at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:632) at org.apache.catalina.startup.HostConfig.deployDirectory(HostConfig.java:1229) at org.apache.catalina.startup.HostConfig$DeployDirectory.run(HostConfig.java:1875) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:439) at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303) at java.util.concurrent.FutureTask.run(FutureTask.java:138) at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:895) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:918) at java.lang.Thread.run(Thread.java:662)
(In reply to Danila Galimov from comment #12) > When logAbandoned is set to true, when connection is closed, but any > statements are left unclosed, you'll get this message in the catalina.log > and/or console (depends on the logger configuration): > > WARNING: Statement created, but was not closed That's the general ideal, right? How does your patch do anything differently?
The other changes are: 1) Removed catch of ClassCastException, since it shouldn't occur (And I was not able to get it actually in my tests) 2) Statement is now closed only if Statement.isClosed() returns false. I think there is no sense of closing already closed statement. In my tests all of the cursors were closed with this settings
Reopening since the patch is a new fix
Is it ok to apply it?
Fixed in r161644
The revision links to Apache Directory
(In reply to Michael Osipov from comment #18) > The revision links to Apache Directory r1616644
Filip, You changed the logic a bit. In my original patch, only the statements, which are not closed, are logged. if (!st.isClosed()) { .... if (logCreationStack) { log.warn("Statement created, but was not closed ", se.getAllocationStack()); } } However, in your core, http://svn.apache.org/viewvc?view=revision&revision=1616644, you always log the creation stack on closure, even if it was already closed. My intention was to log only unclosed statements.
Fixed in r1617042 Danila, Thank You So Much for the review Filip