RE: Tomcat7 - ajax and connection pool : connection closed despite the use of validationQuery and testOnBorrow
OK, thank you, Jose! Good to know if I ever go the MySQL route. -Original Message- From: Jose María Zaragoza [mailto:demablo...@gmail.com] Sent: Friday, April 05, 2013 5:07 PM To: Tomcat Users List Subject: Re: Tomcat7 - ajax and connection pool : connection closed despite the use of validationQuery and testOnBorrow And that's right . You can define any SQL , but you prefer the easiest ( and fastest ) Indeed , you could use SELECT 1 FROM DUAL in MySQL, but it's not required by using FROM DUAL From MySQL doc DUAL is purely for the convenience of people who require that all SELECT statements should have FROM and possibly other clauses. MySQL may ignore the clauses. MySQL does not require FROM DUAL if no tables are referenced. I think that Oracle requires FROM word in queries ( I think ) 2013/4/5 Propes, Barry L barry.l.pro...@citi.com Ok, thanks for clarification. I thought it had to be from some faux table or object. -Original Message- From: Jose María Zaragoza [mailto:demablo...@gmail.com] Sent: Friday, April 05, 2013 3:44 PM To: Tomcat Users List Subject: Re: Tomcat7 - ajax and connection pool : connection closed despite the use of validationQuery and testOnBorrow 'select 1 from dual' works in Oracle DB In MySQL, select 1 2013/4/5 Propes, Barry L barry.l.pro...@citi.com I could be wrong, but is that validationQuery attribute correct? SELECT 1 ? - I have validationQuery=select 1 from dual in mine, and my doc states it has to be a valid SQL statement returning at least one row. That may not factor in at all, just noticed it. Looked maybe out of kilter. -Original Message- From: Jean-Claude Haw-King-Chon [mailto:jchaw-king-c...@medifirst.fr] Sent: Friday, April 05, 2013 10:38 AM To: Tomcat Users List Subject: Re: Tomcat7 - ajax and connection pool : connection closed despite the use of validationQuery and testOnBorrow .. My configuration : Resource name=jdbc/607 auth=Container type=javax.sql.DataSource username=x password=x driverClassName=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/X removeAbandoned=true removeAbandonedTimeout=300 testOnBorrow=true validationQuery=SELECT 1 logAbandoned=true / Tomcat 7.0.29 Linux Fedora Core 12 - 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 - 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
Tomcat7 - ajax and connection pool : connection closed despite the use of validationQuery and testOnBorrow
Hi, I use the connection pool of tomcat7 and set a connection in a ThreadLocal. The context is a web application : the threadLocal is configured when the http request is initialized. The jdbc connection is closed and removed from the threadLocal when the resquest is destroyed. If I'm not mistaken, each http request is treated by one thread by tomcat. So, I have one jdbc connection for each request ( = thread). My problem appears when I use Ajax. For 2 successive ajax request, I have the error Connection closed : it's a random problem (the stacktrace is not the same for the 2 attemps and it's not depending of the thread execution order). I know this issue : http://tomcat.apache.org/tomcat-7.0-doc/jndi-datasource-examples-howto.html#Random_Connection_Closed_Exceptions These can occur when one request gets a db connection from the connection pool and closes it twice. When using a connection pool, closing the connection just returns it to the pool for reuse by another request, it doesn't close the connection. And Tomcat uses multiple threads to handle concurrent requests But, I'm sure, I always close the connection only once. I use too the validationQuery and testOnBorrow parameters. Is it possible that the connection pool returns the same connection for 2 different threads? So I don't understand why my connection is closed during the treatment. Thank for your help - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: Tomcat7 - ajax and connection pool : connection closed despite the use of validationQuery and testOnBorrow
Am 2013-04-05 11:37, schrieb Jean-Claude Haw-King-Chon: Hi, I use the connection pool of tomcat7 and set a connection in a ThreadLocal. The context is a web application : the threadLocal is configured when the http request is initialized. The jdbc connection is closed and removed from the threadLocal when the resquest is destroyed. Why do you do that at all? Why don't you simply use such an idiom in your thead/controller: ... Connection conn = ConnetionUtils.getReadWriteConnection(DataSource dataSource); // has to be implemented of course .. Use this connetion in the entire thread, pass it along, when you are done. Close it. ThreadLocals are useful in other cases but not in this one. Michael - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: Tomcat7 - ajax and connection pool : connection closed despite the use of validationQuery and testOnBorrow
I wish to avoid passing the connection through the layers of the application (from controller to DAO). The controller initialize the connection and must pass it to the Dao layer. I don't want to pass the datasource or connection to the business layer as a parameter. Futher, the data source can be different for each user : it's not possible to use a static attribute to store the connection or the datasource (by example, in a connection factory). That's why I think that threadlocal is the solution for passing the connection between 2 non adjacent layers : is it a bad practice? Jean-Claude HAW-KING-CHON *MédiFirst ** **Immeuble LE SESAME** *8 rue Germain Soufflot 78180 Montigny le Bretonneux *Hotline Support: 01 79 85 39 40* Le 05/04/2013 12:40, Michael-O a écrit : Am 2013-04-05 11:37, schrieb Jean-Claude Haw-King-Chon: Hi, I use the connection pool of tomcat7 and set a connection in a ThreadLocal. The context is a web application : the threadLocal is configured when the http request is initialized. The jdbc connection is closed and removed from the threadLocal when the resquest is destroyed. Why do you do that at all? Why don't you simply use such an idiom in your thead/controller: ... Connection conn = ConnetionUtils.getReadWriteConnection(DataSource dataSource); // has to be implemented of course .. Use this connetion in the entire thread, pass it along, when you are done. Close it. ThreadLocals are useful in other cases but not in this one. Michael - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: Tomcat7 - ajax and connection pool : connection closed despite the use of validationQuery and testOnBorrow
2013/4/5 Jean-Claude Haw-King-Chon jchaw-king-c...@medifirst.fr:
Hi,
I use the connection pool of tomcat7 and set a connection in a ThreadLocal.
The context is a web application : the threadLocal is configured when the
http request is initialized. The jdbc connection is closed and removed from
the threadLocal when the resquest is destroyed.
1. What exactly do you mean by initialized and destroyed?
If I'm not mistaken, each http request is treated by one thread by tomcat.
So, I have one jdbc connection for each request ( = thread).
2. No. It depends on how you use it and at what points in time you look at it.
If you look at it in a call chain, e.g. using try{ } finally block in
a Filter, then yes, all calls inside of try belong to the same
thread.
If you return processing to Tomcat and Tomcat makes a different call
into your code (e.g. using Async or Comet APIs), then that call can be
using a different thread from a thread pool.
(...)
My problem appears when I use Ajax. For 2 successive ajax request, I have
the error Connection closed : it's a random problem (the stacktrace is not
the same for the 2 attemps and it's not depending of the thread execution
order).
I know this issue :
http://tomcat.apache.org/tomcat-7.0-doc/jndi-datasource-examples-howto.html#Random_Connection_Closed_Exceptions
These can occur when one request gets a db connection from the connection
pool and closes it twice. When using a connection pool, closing the
connection just returns it to the pool for reuse by another request, it
doesn't close the connection. And Tomcat uses multiple threads to handle
concurrent requests
But, I'm sure, I always close the connection only once.
I use too the validationQuery and testOnBorrow parameters.
Is it possible that the connection pool returns the same connection for 2
different threads?
You can check its hash code.
So I don't understand why my connection is closed during the treatment.
Best regards,
Konstantin Kolinko
-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org
Re: Tomcat7 - ajax and connection pool : connection closed despite the use of validationQuery and testOnBorrow
Fist of all, do NOT top post. Use the power of your wondeful mail client. Am 2013-04-05 13:05, schrieb Jean-Claude Haw-King-Chon: I wish to avoid passing the connection through the layers of the application (from controller to DAO). The controller initialize the connection and must pass it to the Dao layer. I don't want to pass the datasource or connection to the business layer as a parameter. Futher, the data source can be different for each user : it's not possible to use a static attribute to store the connection or the datasource (by example, in a connection factory). That's why I think that threadlocal is the solution for passing the connection between 2 non adjacent layers : is it a bad practice? ThreadLocals have their use cases but must be used with great caution. Are you certain that no one else sets the ThreadLocal and you do not suffer from race conditions due to missing synchronization? Michael - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: Tomcat7 - ajax and connection pool : connection closed despite the use of validationQuery and testOnBorrow
Le 05/04/2013 13:09, Konstantin Kolinko a écrit :
2013/4/5 Jean-Claude Haw-King-Chon jchaw-king-c...@medifirst.fr:
Hi,
I use the connection pool of tomcat7 and set a connection in a ThreadLocal.
The context is a web application : the threadLocal is configured when the
http request is initialized. The jdbc connection is closed and removed from
the threadLocal when the resquest is destroyed.
1. What exactly do you mean by initialized and destroyed?
If I'm not mistaken, each http request is treated by one thread by tomcat.
So, I have one jdbc connection for each request ( = thread).
2. No. It depends on how you use it and at what points in time you look at it.
If you look at it in a call chain, e.g. using try{ } finally block in
a Filter, then yes, all calls inside of try belong to the same
thread.
If you return processing to Tomcat and Tomcat makes a different call
into your code (e.g. using Async or Comet APIs), then that call can be
using a different thread from a thread pool.
(...)
My problem appears when I use Ajax. For 2 successive ajax request, I have
the error Connection closed : it's a random problem (the stacktrace is not
the same for the 2 attemps and it's not depending of the thread execution
order).
I know this issue :
http://tomcat.apache.org/tomcat-7.0-doc/jndi-datasource-examples-howto.html#Random_Connection_Closed_Exceptions
These can occur when one request gets a db connection from the connection
pool and closes it twice. When using a connection pool, closing the
connection just returns it to the pool for reuse by another request, it
doesn't close the connection. And Tomcat uses multiple threads to handle
concurrent requests
But, I'm sure, I always close the connection only once.
I use too the validationQuery and testOnBorrow parameters.
Is it possible that the connection pool returns the same connection for 2
different threads?
You can check its hash code.
So I don't understand why my connection is closed during the treatment.
Best regards,
Konstantin Kolinko
-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org
1. What exactly do you mean by initialized and destroyed?
Sorry, to be more precise, 2 approches has been tested :
1) The connection is initialized in the servlet by a method called by
doGet or doPost.
But the connection is closed (and removed from the threadLocal) by a
ServletRequestListener in the override of the requestDestroyed method.
2) The connection is initialized in the servlet by a method called by
doGet or doPost. The process is encapsuled in a try/catch/finally block.
The connection is closed (and removed from the threadLocal) in the
finally block.
These 2 approches didn't work (random closed connection).
2. No. It depends on how you use it and at what points in time you look at it.
So for the 2 approches, I'm in a call chain.
Is it possible that the connection pool returns the same connection for 2
different threads?
You can check its hash code.
Thank you for this tip. The thread name and the connection hashCode are
now logged : I'm trying to understand the mechanism.
The first results show that the problem appears with connections
precedently closed...
My configuration :
Resource
name=jdbc/607
auth=Container
type=javax.sql.DataSource
username=x
password=x
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/X
removeAbandoned=true
removeAbandonedTimeout=300
testOnBorrow=true
validationQuery=SELECT 1
logAbandoned=true
/
Tomcat 7.0.29
Linux Fedora Core 12
-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org
Re: Tomcat7 - ajax and connection pool : connection closed despite the use of validationQuery and testOnBorrow
2013/4/5 Jean-Claude Haw-King-Chon jchaw-king-c...@medifirst.fr:
Le 05/04/2013 13:09, Konstantin Kolinko a écrit :
2013/4/5 Jean-Claude Haw-King-Chon jchaw-king-c...@medifirst.fr:
Hi,
I use the connection pool of tomcat7 and set a connection in a
ThreadLocal.
The context is a web application : the threadLocal is configured when the
http request is initialized. The jdbc connection is closed and removed
from
the threadLocal when the resquest is destroyed.
1. What exactly do you mean by initialized and destroyed?
If I'm not mistaken, each http request is treated by one thread by
tomcat.
So, I have one jdbc connection for each request ( = thread).
2. No. It depends on how you use it and at what points in time you look
at it.
If you look at it in a call chain, e.g. using try{ } finally block in
a Filter, then yes, all calls inside of try belong to the same
thread.
If you return processing to Tomcat and Tomcat makes a different call
into your code (e.g. using Async or Comet APIs), then that call can be
using a different thread from a thread pool.
(...)
My problem appears when I use Ajax. For 2 successive ajax request, I have
the error Connection closed : it's a random problem (the stacktrace is
not
the same for the 2 attemps and it's not depending of the thread execution
order).
I know this issue :
http://tomcat.apache.org/tomcat-7.0-doc/jndi-datasource-examples-howto.html#Random_Connection_Closed_Exceptions
These can occur when one request gets a db connection from the
connection
pool and closes it twice. When using a connection pool, closing the
connection just returns it to the pool for reuse by another request, it
doesn't close the connection. And Tomcat uses multiple threads to handle
concurrent requests
But, I'm sure, I always close the connection only once.
I use too the validationQuery and testOnBorrow parameters.
Is it possible that the connection pool returns the same connection for 2
different threads?
You can check its hash code.
So I don't understand why my connection is closed during the treatment.
Best regards,
Konstantin Kolinko
-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org
1. What exactly do you mean by initialized and destroyed?
Sorry, to be more precise, 2 approches has been tested :
1) The connection is initialized in the servlet by a method called by
doGet or doPost.
But the connection is closed (and removed from the threadLocal) by a
ServletRequestListener in the override of the requestDestroyed method.
2) The connection is initialized in the servlet by a method called by
doGet or doPost. The process is encapsuled in a try/catch/finally block.
The connection is closed (and removed from the threadLocal) in the finally
block.
These 2 approches didn't work (random closed connection).
You should go with 2).
2. No. It depends on how you use it and at what points in time you look at
it.
So for the 2 approches, I'm in a call chain.
Is it possible that the connection pool returns the same connection for 2
different threads?
You can check its hash code.
Thank you for this tip. The thread name and the connection hashCode are now
logged : I'm trying to understand the mechanism.
The first results show that the problem appears with connections precedently
closed...
My configuration :
Resource
name=jdbc/607
auth=Container
type=javax.sql.DataSource
username=x
password=x
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/X
removeAbandoned=true
removeAbandonedTimeout=300
testOnBorrow=true
validationQuery=SELECT 1
logAbandoned=true
/
Tomcat 7.0.29
Linux Fedora Core 12
Do you have queries that run or are used for more than 5 minutes in a
single request?
If so, then your removeAbandonedTimeout is too short and
removeAbandoned will close them.
http://commons.apache.org/proper/commons-dbcp/configuration.html
BTW, your pool size is 8.
Best regards,
Konstantin Kolinko
-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org
Re: Tomcat7 - ajax and connection pool : connection closed despite the use of validationQuery and testOnBorrow
Hi Konstantin: About this link http://commons.apache.org/proper/commons-dbcp/configuration.html If you have enabled removeAbandoned then it is possible that a connection is reclaimed by the pool because it is considered to be abandoned. This mechanism is triggered when (getNumIdle() 2) and (getNumActive() getMaxActive() - 3) I know that it's about DBCP , not JDBC Pool in Tomcat 7, but Do you know if it's the same mechanism ? In this case, this mechanism only is useful when pool is about to be exhausted, right ? Regards Do you have queries that run or are used for more than 5 minutes in a single request? If so, then your removeAbandonedTimeout is too short and removeAbandoned will close them. http://commons.apache.org/proper/commons-dbcp/configuration.html BTW, your pool size is 8. Best regards, Konstantin Kolinko - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
RE: Tomcat7 - ajax and connection pool : connection closed despite the use of validationQuery and testOnBorrow
I could be wrong, but is that validationQuery attribute correct? SELECT 1 ? - I have validationQuery=select 1 from dual in mine, and my doc states it has to be a valid SQL statement returning at least one row. That may not factor in at all, just noticed it. Looked maybe out of kilter. -Original Message- From: Jean-Claude Haw-King-Chon [mailto:jchaw-king-c...@medifirst.fr] Sent: Friday, April 05, 2013 10:38 AM To: Tomcat Users List Subject: Re: Tomcat7 - ajax and connection pool : connection closed despite the use of validationQuery and testOnBorrow .. My configuration : Resource name=jdbc/607 auth=Container type=javax.sql.DataSource username=x password=x driverClassName=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/X removeAbandoned=true removeAbandonedTimeout=300 testOnBorrow=true validationQuery=SELECT 1 logAbandoned=true / Tomcat 7.0.29 Linux Fedora Core 12 - 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
Re: Tomcat7 - ajax and connection pool : connection closed despite the use of validationQuery and testOnBorrow
'select 1 from dual' works in Oracle DB In MySQL, select 1 2013/4/5 Propes, Barry L barry.l.pro...@citi.com I could be wrong, but is that validationQuery attribute correct? SELECT 1 ? - I have validationQuery=select 1 from dual in mine, and my doc states it has to be a valid SQL statement returning at least one row. That may not factor in at all, just noticed it. Looked maybe out of kilter. -Original Message- From: Jean-Claude Haw-King-Chon [mailto:jchaw-king-c...@medifirst.fr] Sent: Friday, April 05, 2013 10:38 AM To: Tomcat Users List Subject: Re: Tomcat7 - ajax and connection pool : connection closed despite the use of validationQuery and testOnBorrow .. My configuration : Resource name=jdbc/607 auth=Container type=javax.sql.DataSource username=x password=x driverClassName=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/X removeAbandoned=true removeAbandonedTimeout=300 testOnBorrow=true validationQuery=SELECT 1 logAbandoned=true / Tomcat 7.0.29 Linux Fedora Core 12 - 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
RE: Tomcat7 - ajax and connection pool : connection closed despite the use of validationQuery and testOnBorrow
Ok, thanks for clarification. I thought it had to be from some faux table or object. -Original Message- From: Jose María Zaragoza [mailto:demablo...@gmail.com] Sent: Friday, April 05, 2013 3:44 PM To: Tomcat Users List Subject: Re: Tomcat7 - ajax and connection pool : connection closed despite the use of validationQuery and testOnBorrow 'select 1 from dual' works in Oracle DB In MySQL, select 1 2013/4/5 Propes, Barry L barry.l.pro...@citi.com I could be wrong, but is that validationQuery attribute correct? SELECT 1 ? - I have validationQuery=select 1 from dual in mine, and my doc states it has to be a valid SQL statement returning at least one row. That may not factor in at all, just noticed it. Looked maybe out of kilter. -Original Message- From: Jean-Claude Haw-King-Chon [mailto:jchaw-king-c...@medifirst.fr] Sent: Friday, April 05, 2013 10:38 AM To: Tomcat Users List Subject: Re: Tomcat7 - ajax and connection pool : connection closed despite the use of validationQuery and testOnBorrow .. My configuration : Resource name=jdbc/607 auth=Container type=javax.sql.DataSource username=x password=x driverClassName=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/X removeAbandoned=true removeAbandonedTimeout=300 testOnBorrow=true validationQuery=SELECT 1 logAbandoned=true / Tomcat 7.0.29 Linux Fedora Core 12 - 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 - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: Tomcat7 - ajax and connection pool : connection closed despite the use of validationQuery and testOnBorrow
And that's right . You can define any SQL , but you prefer the easiest ( and fastest ) Indeed , you could use SELECT 1 FROM DUAL in MySQL, but it's not required by using FROM DUAL From MySQL doc DUAL is purely for the convenience of people who require that all SELECT statements should have FROM and possibly other clauses. MySQL may ignore the clauses. MySQL does not require FROM DUAL if no tables are referenced. I think that Oracle requires FROM word in queries ( I think ) 2013/4/5 Propes, Barry L barry.l.pro...@citi.com Ok, thanks for clarification. I thought it had to be from some faux table or object. -Original Message- From: Jose María Zaragoza [mailto:demablo...@gmail.com] Sent: Friday, April 05, 2013 3:44 PM To: Tomcat Users List Subject: Re: Tomcat7 - ajax and connection pool : connection closed despite the use of validationQuery and testOnBorrow 'select 1 from dual' works in Oracle DB In MySQL, select 1 2013/4/5 Propes, Barry L barry.l.pro...@citi.com I could be wrong, but is that validationQuery attribute correct? SELECT 1 ? - I have validationQuery=select 1 from dual in mine, and my doc states it has to be a valid SQL statement returning at least one row. That may not factor in at all, just noticed it. Looked maybe out of kilter. -Original Message- From: Jean-Claude Haw-King-Chon [mailto:jchaw-king-c...@medifirst.fr] Sent: Friday, April 05, 2013 10:38 AM To: Tomcat Users List Subject: Re: Tomcat7 - ajax and connection pool : connection closed despite the use of validationQuery and testOnBorrow .. My configuration : Resource name=jdbc/607 auth=Container type=javax.sql.DataSource username=x password=x driverClassName=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/X removeAbandoned=true removeAbandonedTimeout=300 testOnBorrow=true validationQuery=SELECT 1 logAbandoned=true / Tomcat 7.0.29 Linux Fedora Core 12 - 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 - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
