why datasource get an already closed connection?

[vicattomcat]

Hi buddies,

I want to ask a question about connection pool, and my environment is 
apache-tomcat-5.5.27, mysql-5.0.67(mysql-connection-j-5.1.8) and default 
apache DBCP. Here is a short description of the problem I met.
I configured a datasource in $catalina.base/conf/server.xml, and get a 
connection in my java code.
   <Context docBase="xxx" path="/xxx"
       reloadable="true" source="org.eclipse.jst.j2ee.server:voguebi">
       <Resource auth="Container" driverClassName="com.mysql.jdbc.Driver"
       maxActive="100" maxIdle="30" maxWait="10000"
       name="jdbc/xxx" password="xxx"
       type="javax.sql.DataSource"
       
url="jdbc:mysql://localhost:3306/xxx?useUnicode=true&amp;characterEncoding=utf8" 

       username="xxx" />
  </Context>
I can do all the operations using that connection.
while, when I logout the application, and wait for a long time, about a 
whole day, and then an error will occur when I login again. The 
SQLException message is something like this:

   2009-09-03 14:04:32,046 ERROR [xxx.xxx.xxxDao].[login] 
Communications link failure
   The last packet successfully received from the server was 1,398,531 
milliseconds ago.  The last packet sent successfully to the server was 0 
milliseconds ago.
   2009-09-03 14:04:52,062 ERROR 
[xxx.xxx.xxx.common.DatabaseUtils].[closeQuietly] Already closed.

OR

   2009-09-03 16:05:37,703 ERROR [xxx.xxx.xxxDao].[login] No operations 
allowed after connection closed.Connection was implicitly closed due to 
underlying exception/error:
   ** BEGIN NESTED EXCEPTION **
   com.mysql.jdbc.exceptions.jdbc4.CommunicationsException
   MESSAGE: Communications link failure
   The last packet successfully received from the server was 910,485 
milliseconds ago.  The last packet sent successfully to the server was 0 
milliseconds ago.
   STACKTRACE:
   com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: 
Communications link failure
   The last packet successfully received from the server was 910,485 
milliseconds ago.  The last packet sent successfully to the server was 0 
milliseconds ago.
   ......

so, I think the error is because I am using an already closed 
connection. and I have to wait a really long time to re-create this 
exception.
After doing some googles, I found that there is an wait_timeout 
parameter in mysql:
   By default, the server closes the connection after eight hours if 
nothing has happened. You can change the time limit by setting the 
wait_timeout variable when you start mysqld.

so, I think it because DBCP get the connection with the following way:

   The pool can be configured to behave as a LIFO queue with respect to 
idle objects - always returning the most recently used object from the 
pool, or as a FIFO queue, where borrowObject always returns the oldest 
object in the idle object pool.
   lifo determines whether or not the pool returns idle objects in 
last-in-first-out order. The default setting for this parameter is true.

so, if 8 hours later, the mysql server closes the connection used the 
most recently in the pool ,then when I login again, I get an already 
closed connection, when I use it to do some operations, error occurs.

Now I have some ways to solve it:
First of all, I check the connection I get from the pool, if it has 
already closed, I set it to null, and get another one from the pool.
Secondly, I add validationQuery="SELECT 1", which seems working well.
Thirdly, I change the mysql wait_timeout to the max value, which can be 
365 days.

Who can give me any advice to solve this problem in another way or tell 
me which is the best way among the ways I talked about?

Thanks so much for your patient and kind heart.


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org