[dbcp]why datasource get an already closed connection?

[vicatcommons <vi...@googlemail.com>]

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: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org