Tomcat connection pool issue- Cannot call a method on closed connection

Question

My application is using Spring 2.5.x and deployed on Tomcat server. Some times, I get below error when my db connection is idle:

[TeraJDBC 14.00.00.13] [Error 1095] [SQLState HY000] Cannot call a method on closed connection

Here is the datasource configuration

<bean id="dataSource"   class="org.apache.tomcat.jdbc.pool.DataSource"
          destroy-method="close">
    <property name="driverClassName" value="com.teradata.jdbc.TeraDriver"/>
    <property name="url" >
        <util:constant static-field="_DB_HOST"/>
    </property>
    <property name="username">
        <util:constant static-field="_DB_USER"/>
    </property>
    <property name="password">
        <util:constant static-field="_DB_PWD"/>
    </property>
    <property name="initialSize" value="1" />
    <property name="maxActive" value="50" />
</bean>

Is there any configuration I'm missing here?

Can you show how you get a connection and close a connection in code too? — stdunbar, Feb 24 '21 at 14:56
@stdunbar I am using the spring jdbcTemplate to call the stored procedure , I assume spring takes cares of closing the connections. — user1591156, Feb 24 '21 at 15:14
It's possible that the pool is returning a closed connection (for some reason). If you don't have an [additional check](https://stackoverflow.com/q/41998490/2541560) that the connection is valid, you may encounter the error you're getting. — Kayaman, Feb 24 '21 at 15:28
Does this answer your question? [Cannot call a method on closed connection, facing this issue after ideal time of connection tera data pool](https://stackoverflow.com/questions/60503059/cannot-call-a-method-on-closed-connection-facing-this-issue-after-ideal-time-of) — Piotr P. Karwasz, Feb 24 '21 at 18:02

score 0 · Accepted Answer · answered Feb 24 '21 at 20:29

While all connections used by Spring's JdbcTemplate are closed at the end of each transaction, Tomcat's JDBC Connection Pool never actually returns the real Connection's obtained by the driver. DataSource#getConnection always returns a proxy, such that Connection#close returns the connection to the pool instead of physically closing it.

Therefore, as explained in this answer, the connections are probably closed by the server. Therefore you need to configure the pool to validate the connections, as in the answer cited by Kayaman.

I suspect that your problem is not caused by connection issues, but server policy, so I would set up:

<property name="validationQuery" value="SELECT 66353343" /> 
<property name="testWhileIdle" value="true" />
<property name="timeBetweenEvictionRunsMillis" value="60000" />

in order to check every 60 seconds if the physical connections are up.

checking every 60 seconds will not increase additional load to database and is it good practice for prod environment? — user1591156, Feb 25 '21 at 16:06
A couple of milliseconds of work every 60 seconds generate almost no load at all and it will keep you connections alive. You can also test when borrowing from the pool, but that increases the load slightly more. — Piotr P. Karwasz, Feb 25 '21 at 16:16

Tomcat connection pool issue- Cannot call a method on closed connection

1 Answers1