2015-06-15 13:09 GMT+02:00 Mark Thomas <ma...@apache.org>: > On 15/06/2015 11:39, Jose María Zaragoza wrote: >> 2015-06-15 11:39 GMT+02:00 Mark Thomas <ma...@apache.org>: >>> On 15/06/2015 09:42, Jose María Zaragoza wrote: >>>> Hello: >>>> >>>> >>>> I'm using Tomcat 7.0.59 and PostgreSQL JDBC driver 9.4-1201-jdbc4 >>>> >>>> The context.xml settings are >>>> >>>> >>>> <Resource auth="Container" type="javax.sql.DataSource" >>>> factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" >>>> driverClassName="org.postgresql.Driver" >>>> initialSize="0" maxActive="100" maxIdle="20" >>>> maxWait="10000" mindIdle="0" >>>> name="datasource/bbdd" password="fake" >>>> removeAbandoned="true" >>>> >>>> url="jdbc:postgresql://localhost:9999,localhost:5432,localhost:5433/bbdd?LoginTimeout=3&loadBalanceHosts=false" >>>> username="foo" validationQuery="SELECT 1" >>>> testOnBorrow="true" timeBetweenEvictionRunsMillis="0"/> >>>> >>>> >>>> I've configured JDBC driver in failover mode ( as you can see in URL >>>> definition ) >>>> >>>> When I test this configuration ( I stop/start databases but , at >>>> least, there is one running ) , sometimes I'm getting this error >>>> message: >>>> >>>> >>>> org.apache.ibatis.exceptions.PersistenceException: >>>> ### Error querying database. Cause: >>>> org.postgresql.util.PSQLException: This connection has been closed. >>>> >>>> How is it possible if testOnBorrow="true" and validationQuery="SELECT 1" ? >>> >>> Application borrows connection. >>> You shut down database. >>> Application uses connection. >>> >> >> Not really. > > Look at the logs again. The same connection object is being used before > and after you stop the database.
Right, because Tomcat DBCP gives the same connection. And it shouldn't because that connection should be validated before. > > It looks more like the application is hanging onto the connection for an > extended period of time (which defeats the point of using a connection > pool in the first place). I'm sure that it doesn't. The application doesn't keep any connection . Ask to Tomcat DBCP for a connection when it needs to execute any query Doesn't make sense to keep a pool of pooled connections > >> Paste logs >> >> 1) Performs query >> >> 2015/06/15 12:20:22.379 INFO [getData] - Getting record from database id:0 >> 2015/06/15 12:20:22.379 DEBUG [JdbcTransaction] [openConnection] - >> Opening JDBC Connection >> 2015/06/15 12:20:22.613 DEBUG [JdbcTransaction] [setDesiredAutoCommit] >> - Setting autocommit to false on JDBC Connection >> [ProxyConnection[PooledConnection[org.postgresql.jdbc4.Jdbc4Connection@a7429f]]] >> 2015/06/15 12:20:22.613 DEBUG [queryData] [debug] - ooo Using >> Connection >> [ProxyConnection[PooledConnection[org.postgresql.jdbc4.Jdbc4Connection@a7429f]]] >> 2015/06/15 12:20:22.613 DEBUG [queryData] [debug] - ==> Preparing: >> SELECT * FROM data WHERE id = ? ORDER BY autoid DESC LIMIT 1 >> 2015/06/15 12:20:22.613 DEBUG [queryData] [debug] - ==> Parameters: 0(String) >> 2015/06/15 12:20:22.847 DEBUG [queryData] [debug] - <== Total: 1 >> 2015/06/15 12:20:22.847DEBUG [JdbcTransaction] [resetAutoCommit] - >> Resetting autocommit to true on JDBC Connection >> [ProxyConnection[PooledConnection[org.postgresql.jdbc4.Jdbc4Connection@a7429f]]] >> 2015/06/15 12:20:23.065 DEBUG [JdbcTransaction] [close] - Closing JDBC >> Connection >> [ProxyConnection[PooledConnection[org.postgresql.jdbc4.Jdbc4Connection@a7429f]]] >> >> >> 2) Stop database >> >> 3) Performs query again >> >> >> 2015/06/15 12:20:28.801 INFO [getDatosIVRCRM] - Getting record from >> database id:0 >> 2015/06/15 12:20:28.801 DEBUG [JdbcTransaction] [openConnection] - >> Opening JDBC Connection >> 2015/06/15 12:20:28.801 DEBUG [JdbcTransaction] >> [setDesiredAutoCommit] - Setting autocommit to false on JDBC >> Connection >> [ProxyConnection[PooledConnection[org.postgresql.jdbc4.Jdbc4Connection@a7429f]]] >> 2015/06/15 12:20:28.802 DEBUG [queryData] [debug] - ooo Using >> Connection >> [ProxyConnection[PooledConnection[org.postgresql.jdbc4.Jdbc4Connection@a7429f]]] >> 2015/06/15 12:20:28.802 DEBUG [queryDatosIVRCRM] [debug] - ==> >> Preparing: SELECT * FROM data WHERE id = ? ORDER BY autoid DESC LIMIT >> 1 >> 2015/06/15 12:20:28.802 DEBUG [queryData] [debug] - ==> Parameters: 0(String) >> 2015/06/15 12:20:28.802 [getData] - [EXCEPTION] >> org.apache.ibatis.exceptions.PersistenceException: >> ### Error querying database. Cause: >> org.postgresql.util.PSQLException: This connection has been closed. >> >> >> Both queries are executed by different thread ( http thread ) >> >> One important thing: >> >> if I wait 20 seconds between calls, *the second one works fine* . I >> dont know why > > Which suggests that the application hands the connection back during > that 20 second period. > >> Looks like the validationQuery check doesn't detect that the >> connection is broken . > > I see zero evidence to support that assumption. > > Mark > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org > For additional commands, e-mail: users-h...@tomcat.apache.org > --------------------------------------------------------------------- To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org