You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@tomcat.apache.org by Jose María Zaragoza <de...@gmail.com> on 2015/06/15 10:42:43 UTC
validationQuery doesn't work (DBCP 7.0.59)
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" ?
How I can debug if validationQuery is working as I want ?
Thanks and regards
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: validationQuery doesn't work (DBCP 7.0.59)
Posted by Dhaval Jaiswal <dh...@via.com>.
You can change the DB log parameter "log_statement" setting to ALL for the
time being and ensure the query execution.
On Mon, Jun 15, 2015 at 2:12 PM, Jose María Zaragoza <de...@gmail.com>
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" ?
> How I can debug if validationQuery is working as I want ?
>
>
> Thanks and regards
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
>
>
Re: validationQuery doesn't work (DBCP 7.0.59)
Posted by Mark Thomas <ma...@apache.org>.
On 15/06/2015 15:35, Daniel Mikusa wrote:
> On Mon, Jun 15, 2015 at 4:42 AM, Jose María Zaragoza <de...@gmail.com>
> 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" ?
>>
>
> Try setting validationInterval to a lower value. It defaults to 30s, which
> means it will only run the query once per 30s.
>
> http://tomcat.apache.org/tomcat-8.0-doc/jdbc-pool.html#Tomcat_JDBC_Enhanced_Attributes
I was about to ask what on earth you were talking about since DBCP has
no concept of validation interval but you are correct. Despite the use
of DBCP in the subject, the OP isn't using DBCP at all.
Ignore my previous comments on this thread. They were only valid for
DBCP, not Tomcat's jdbc-pool implementation (which is what the OP is
actually using).
Mark
>
>
>> How I can debug if validationQuery is working as I want ?
>>
>
> 1.) Start Tomcat, make sure the app is working.
> 2.) Stop the DB.
> 3.) Your app should stop working, since it can't get a connection. Also,
> any pooled connections will now be bad.
> 4.) Start the DB.
> 5.) Your app should be working again. If the pool is configured properly
> as your app requests connections, it should remove the bad connections and
> replace them with new ones.
>
> Dan
>
>
>
>>
>>
>> Thanks and regards
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
>> For additional commands, e-mail: users-help@tomcat.apache.org
>>
>>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: validationQuery doesn't work (DBCP 7.0.59)
Posted by Jose María Zaragoza <de...@gmail.com>.
2015-06-15 16:35 GMT+02:00 Daniel Mikusa <dm...@pivotal.io>:
> On Mon, Jun 15, 2015 at 4:42 AM, Jose María Zaragoza <de...@gmail.com>
> 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" ?
>>
>
> Try setting validationInterval to a lower value. It defaults to 30s, which
> means it will only run the query once per 30s.
>
> http://tomcat.apache.org/tomcat-8.0-doc/jdbc-pool.html#Tomcat_JDBC_Enhanced_Attributes
You got it !
Thanks
Now I need to understand which is the best value ( in performance terms )
Regards
>
>
>> How I can debug if validationQuery is working as I want ?
>>
>
> 1.) Start Tomcat, make sure the app is working.
> 2.) Stop the DB.
> 3.) Your app should stop working, since it can't get a connection. Also,
> any pooled connections will now be bad.
> 4.) Start the DB.
> 5.) Your app should be working again. If the pool is configured properly
> as your app requests connections, it should remove the bad connections and
> replace them with new ones.
>
> Dan
>
>
>
>>
>>
>> Thanks and regards
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
>> For additional commands, e-mail: users-help@tomcat.apache.org
>>
>>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: validationQuery doesn't work (DBCP 7.0.59)
Posted by Daniel Mikusa <dm...@pivotal.io>.
On Mon, Jun 15, 2015 at 4:42 AM, Jose María Zaragoza <de...@gmail.com>
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" ?
>
Try setting validationInterval to a lower value. It defaults to 30s, which
means it will only run the query once per 30s.
http://tomcat.apache.org/tomcat-8.0-doc/jdbc-pool.html#Tomcat_JDBC_Enhanced_Attributes
> How I can debug if validationQuery is working as I want ?
>
1.) Start Tomcat, make sure the app is working.
2.) Stop the DB.
3.) Your app should stop working, since it can't get a connection. Also,
any pooled connections will now be bad.
4.) Start the DB.
5.) Your app should be working again. If the pool is configured properly
as your app requests connections, it should remove the bad connections and
replace them with new ones.
Dan
>
>
> Thanks and regards
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
>
>
Re: validationQuery doesn't work (DBCP 7.0.59)
Posted by Christopher Schultz <ch...@christopherschultz.net>.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA256
Jose,
On 6/23/15 6:46 AM, Jose María Zaragoza wrote:
> 2015-06-15 17:59 GMT+02:00 Christopher Schultz
> <ch...@christopherschultz.net>: Jose,
>
> It looks like your issue is mostly solved...
>
> On 6/15/15 4:42 AM, Jose María Zaragoza wrote:
>>>> 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" [...]
>>>> timeBetweenEvictionRunsMillis="0"/>
>
> Are you sure you want to do this? The docs don't say anything
> about the value of "0", though one might reasonably expect that
> this disables eviction runs.
>
> The default for "testWhileIdle" is "false", which means that the
> default value of timeBetweenEvictionRunsMillis is not relevant.
>
>
>> sure ?
Not sure. I just read the official documentation.
>> I was following the guide in this link:
>> http://www.codingpedia.org/ama/tomcat-jdbc-connection-pool-configurat
ion-for-production-and-development/
>
>> "timeBetweenEvictionRunsMillis > 0 AND removeAbandoned=true AND
>> removeAbandonedTimeout > 0 means the pool sweeper is enabled."
>
>> I want to be sure that eviction thread of inactive thread is not
>> executed . is that paragrah false ?
Not sure.
Try reading this:
http://tomcat.apache.org/tomcat-8.0-doc/jdbc-pool.html#Tomcat_JDBC_Enhan
ced_Attributes
- -chris
-----BEGIN PGP SIGNATURE-----
Comment: GPGTools - http://gpgtools.org
iQIcBAEBCAAGBQJViXivAAoJEBzwKT+lPKRYslAP/12wPIJ5AFAUd5tumu27FcDL
XbT8lw4yx/8Ol3gmr81jVBFN9jcybUj45iOY2xdlnDr6fRN5Arat0sESxmPlTdQi
gp2x6Ilh4tAmoU+43fLwMYz1f/LhJ1Qk7Sd98VNfs9KA+NvuZPRrzMwotkvj9Pdg
EcQ89hLxEa9OTQv5EUrpXvwQ9MqxaVZ7XIpFa440OLHwRhHYzIIYBAcouDh+FlzP
xrtjM/4+oVGS7EoqUj2C31qLIHN2YsFrJ8Yn/YqLPSL+SFw2ZBR46oR7aB7TJmH7
DLhl6X5c2NKs3rAs0+lQSClOZzBmunWDvZU31oW0aY67CHy+7lAGqJdh8uTljkWN
5AAIC3qSE7QsCSkERmSg3arVqjoDOrO4BDBldSP4mSWgZKPYitllPH7N9vSXX7jo
PNBTo4UZEnaPO6+KuUeS/E8XGUj4n2V84cJznaFCr8sdWoKbKhHFphBup5iSiurw
/+aT+J8yaj4rLg9p4KVeYYWJcfwEgxYAXdUDzcHBFIT1n7gKUBp9jg4cwyMWb7Gb
juoIahCpbxZW0gYOLJJworifOsEERNQEg7nDLyw7mW4y3JNDi76s+2/twgbI9fny
e7/2JfRK9v6CWbp0pXvb0RqRhCskWyPC9YlzqRYPsiaXx9plT+4me1hR8ZwU6uUR
Xrf4YPYDq/Ponw2jJXNW
=cYw1
-----END PGP SIGNATURE-----
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: validationQuery doesn't work (DBCP 7.0.59)
Posted by Jose María Zaragoza <de...@gmail.com>.
2015-06-15 17:59 GMT+02:00 Christopher Schultz <ch...@christopherschultz.net>:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA256
>
> Jose,
>
> It looks like your issue is mostly solved...
>
> On 6/15/15 4:42 AM, Jose María Zaragoza wrote:
>> 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" [...]
>> timeBetweenEvictionRunsMillis="0"/>
>
> Are you sure you want to do this? The docs don't say anything about
> the value of "0", though one might reasonably expect that this
> disables eviction runs.
>
> The default for "testWhileIdle" is "false", which means that the
> default value of timeBetweenEvictionRunsMillis is not relevant.
sure ?
I was following the guide in this link:
http://www.codingpedia.org/ama/tomcat-jdbc-connection-pool-configuration-for-production-and-development/
"timeBetweenEvictionRunsMillis > 0 AND removeAbandoned=true AND
removeAbandonedTimeout > 0 means the pool sweeper is enabled."
I want to be sure that eviction thread of inactive thread is not executed .
is that paragrah false ?
Regards
>
> I would remove this unnecessary configuration, or you may confuse
> people (or yourself) into thinking that it has an actual meaning.
>
> - -chris
> -----BEGIN PGP SIGNATURE-----
> Comment: GPGTools - http://gpgtools.org
>
> iQIcBAEBCAAGBQJVfvZnAAoJEBzwKT+lPKRYCG8QALcZ6KqFWYxB3YZSr49R+pGx
> a3PZwLWO/FabNjkqRy1t2N3/mMJBDFRmwUOw1II+jPpgDl8E6X3cuyyYBBFl1YnU
> 08CJIbKeNclUq6coU52/huvESTCaqa/q+YxknJRq9JFla5yDZlSNQFbQ1QiY/Ei0
> YtcZrzgzRIzeYTVrTlYkb1kuZouKU2KL2DCVS9NoY+s/R5ZSjAs+O4Cpg6V5BcuO
> oEhrwijaQ8+yVWAzGt9+s9j0ohf40ryUcbFB86uY3O4TmebMMZEBMvmTPJ/dpVqt
> GJYCDnr3nLfe6FTr7IK8YinFaWRAJ8U/h0JPs1OthJn1ZiLvLy2N5JeK+YAtx0oI
> l2dxkKr5COFU+DzslUjhiF6/AQJElA/ldqZYJKWeDUJ2oQaEyicPRkdvgknDIjcu
> vNJidFEWZ/4LA48D0OnQ9HLAWYUmXXeiTYXtxWekxpv5RSTL9xZh4rGylWvnbZXi
> WpZxl66RCmk1Lfe5VEIt8jWtRydOOYR4CnBkFoMZj4vSuOysnspnaHMAblEsJuUW
> u02MVC7ZxzG+FepQAMoriQKaEdih1cPNRj3y2Dq2cWZW82RpwS8CoLzgZi9FlSkg
> rPXWpsQ5OJNVmnhdAPaaEHKwIGxObScJZnBVFZgPzU7DRbh7q0SMQexIgnSB5blj
> kEnwEfkaLRF295pU+APM
> =OTjA
> -----END PGP SIGNATURE-----
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: validationQuery doesn't work (DBCP 7.0.59)
Posted by Christopher Schultz <ch...@christopherschultz.net>.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA256
Jose,
It looks like your issue is mostly solved...
On 6/15/15 4:42 AM, Jose María Zaragoza wrote:
> 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" [...]
> timeBetweenEvictionRunsMillis="0"/>
Are you sure you want to do this? The docs don't say anything about
the value of "0", though one might reasonably expect that this
disables eviction runs.
The default for "testWhileIdle" is "false", which means that the
default value of timeBetweenEvictionRunsMillis is not relevant.
I would remove this unnecessary configuration, or you may confuse
people (or yourself) into thinking that it has an actual meaning.
- -chris
-----BEGIN PGP SIGNATURE-----
Comment: GPGTools - http://gpgtools.org
iQIcBAEBCAAGBQJVfvZnAAoJEBzwKT+lPKRYCG8QALcZ6KqFWYxB3YZSr49R+pGx
a3PZwLWO/FabNjkqRy1t2N3/mMJBDFRmwUOw1II+jPpgDl8E6X3cuyyYBBFl1YnU
08CJIbKeNclUq6coU52/huvESTCaqa/q+YxknJRq9JFla5yDZlSNQFbQ1QiY/Ei0
YtcZrzgzRIzeYTVrTlYkb1kuZouKU2KL2DCVS9NoY+s/R5ZSjAs+O4Cpg6V5BcuO
oEhrwijaQ8+yVWAzGt9+s9j0ohf40ryUcbFB86uY3O4TmebMMZEBMvmTPJ/dpVqt
GJYCDnr3nLfe6FTr7IK8YinFaWRAJ8U/h0JPs1OthJn1ZiLvLy2N5JeK+YAtx0oI
l2dxkKr5COFU+DzslUjhiF6/AQJElA/ldqZYJKWeDUJ2oQaEyicPRkdvgknDIjcu
vNJidFEWZ/4LA48D0OnQ9HLAWYUmXXeiTYXtxWekxpv5RSTL9xZh4rGylWvnbZXi
WpZxl66RCmk1Lfe5VEIt8jWtRydOOYR4CnBkFoMZj4vSuOysnspnaHMAblEsJuUW
u02MVC7ZxzG+FepQAMoriQKaEdih1cPNRj3y2Dq2cWZW82RpwS8CoLzgZi9FlSkg
rPXWpsQ5OJNVmnhdAPaaEHKwIGxObScJZnBVFZgPzU7DRbh7q0SMQexIgnSB5blj
kEnwEfkaLRF295pU+APM
=OTjA
-----END PGP SIGNATURE-----
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: validationQuery doesn't work (DBCP 7.0.59)
Posted by Jose María Zaragoza <de...@gmail.com>.
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-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: validationQuery doesn't work (DBCP 7.0.59)
Posted by 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.
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).
> 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-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: validationQuery doesn't work (DBCP 7.0.59)
Posted by Jose María Zaragoza <de...@gmail.com>.
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.
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
Looks like the validationQuery check doesn't detect that the
connection is broken . I don't know what response is waiting to mark
as OK that pooled connection
Regards
>> How I can debug if validationQuery is working as I want ?
>
> Check your database logs.
>
> Mark
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: validationQuery doesn't work (DBCP 7.0.59)
Posted by 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.
> How I can debug if validationQuery is working as I want ?
Check your database logs.
Mark
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org