RE: autoReconnect=true doesn't solve closed connection problem

[Tony Qian]

Poitras,

Thanks for response. If we enable pingQuery and have following setting:

   <property name="Pool.PingConnectionsOlderThan" value="2000"/>
   <property name="Pool.PingConnectionsNotUsedFor" value="2000"/>

my understanding is that iBATIS will use pingQurey first for every query if the connection is older than 2000 (millisec) or NotUsedFor 2000. It will have some impact on performance.

I believe that autoReconnect will have less impact than pingQuery, even though autoReconnect has its other impact.

If we have to use pingQuery, my question is what are the optimum settings for Pool.PingConnectionsOlderThan and PingConnectionsNotUsedFor so that it has minimum impact on performance( Let's say I know that connection will be closed if connection idles for more than  8 hours).

my believe is to set "Pool.PingConnectionsOlderThan" value="0" and PingConnectionsNotUsedFor" value="28800000" ( 28800000 is 8 hours). if connection idles more than 8 hours, i know it is closed by MySql server. I need to use pingQuery to remove it from connection pool. Otherwise, don't do pingQuery.

Thanks,
Tony

Poitras Christian wrote on 5/11/2006, 1:19 PM:

iBatis automatically handle reconnection to databse on connection failure.

 

To use this possibiliy, user can define a certain number of variables in your sqlmapconfig.

 

Here is an example.

<transactionManager type="JDBC">

<dataSource type="SIMPLE">

<property value="${driver}" name="JDBC.Driver"/>

<property value="${url}" name="JDBC.ConnectionURL"/>

<property value="${username}" name="JDBC.Username"/>

<property value="${password}" name="JDBC.Password"/>

<property value="${pingquery}" name="Pool.PingQuery"/>

<property value="${pingenable}" name="Pool.PingEnabled"/>

<property value="${pingoldertime}" name="Pool.PingConnectionsOlderThan"/>

</dataSource>

</transactionManager>

 

 

Make sure the property files contains all the properties specified. I use.

driver=com.mysql.jdbc.Driver

url="" color="#2a00ff">jdbc:mysql://localhost:3306/data

username=root

password=

pingquery=SELECT 1

pingenable=true

pingoldertime=2000

 

---

From: Tony Qian [mailto:[EMAIL PROTECTED]]
Sent: Thursday, 11 May 2006 12:22
To: user-java@ibatis.apache.org
Subject: autoReconnect=true doesn't solve closed connection problem

All,

I set following in SglMapConfig.xml file:

 <property name="JDBC.ConnectionURL" value="jdbc:mysql://hostName:3306/dbName?autoReconnect=true"/>

It doesn't solve Communication Failure problem due to closed connection. Setting autoReconnection=true works when connection pool is managed by Tomcat (DBCP). (btw, PingQuery solves the problem)

database is MySql 5.019, JDBC diver is mysql-connector-java-3.1.11.

Did I do something wrong? any suggestion?

thanks,
Tony