ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Tony Qian" <daqiqi...@aol.com>
Subject RE: autoReconnect=true doesn't solve closed connection problem
Date Fri, 12 May 2006 15:49:18 GMT
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <title></title>
</head>
<body>
<font face="Arial,sans-serif"><font size="2">Poitras,<br>
<br>
Thanks for response. If we enable pingQuery and have following setting:<br>
<br>
&nbsp;&nbsp; &lt;property name="Pool.PingConnectionsOlderThan" value="2000"/&gt;<br>
&nbsp;&nbsp; &lt;property name="Pool.PingConnectionsNotUsedFor" value="2000"/&gt;<br>
</font></font><font face="Arial,sans-serif" size="2"><span  class="369121617-11052006">
<p><font face="Arial,sans-serif" size="2"><span  class="369121617-11052006"></span></font>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. <br>
</p>
</span></font><font size="2"><font face="Arial,sans-serif">I believe
that autoReconnect will have less impact than pingQuery, even though
autoReconnect has its other impact.<br>
<br>
If we have to use pingQuery, my question is what are the optimum
settings for </font></font><font face="Arial,sans-serif" size="2"><font
 size="2">Pool.PingConnectionsOlderThan and </font></font><font  face="Arial,sans-serif"
size="2"><font size="2">PingConnectionsNotUsedFor
so that it has minimum impact on performance( Let's say I know that</font></font>
<font size="2">connection will be closed if connection idles for more
than&nbsp; 8 hours).<br>
<font face="Arial,sans-serif"><br>
my believe is to set </font></font><font face="Arial,sans-serif"  size="2"><font
size="2">"Pool.PingConnectionsOlderThan" value="0" and </font></font><font
 face="Arial,sans-serif" size="2"><font size="2">PingConnectionsNotUsedFor"
value="28800000" </font></font><font face="Arial,sans-serif" size="2"><font
 size="2">( 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.</font></font><br>
<font size="2"><font face="Arial,sans-serif"></font><font  face="Arial,sans-serif"><br>
Thanks,<br>
Tony<br>
</font></font><font face="Arial,sans-serif"><font size="2"><br>
<span type="cite">Poitras Christian wrote on 5/11/2006, 1:19 PM:</span>
</font></font>
<p><font face="Arial,sans-serif" size="2"></font></p>
<blockquote type="cite"  style="border-left: thin solid blue; padding-left: 10px; margin-left:
0pt;"><font  face="Arial,sans-serif" size="2"></font>
  <div dir="ltr" align="left"><font face="Arial,sans-serif" size="2"><span
 class="369121617-11052006"><font face="Arial" color="#0000ff" size="2">iBatis
automatically handle reconnection to databse on connection failure.</font></span></font></div>
  <font face="Arial,sans-serif" size="2"></font>
  <div dir="ltr" align="left"><font face="Arial,sans-serif" size="2"><span
 class="369121617-11052006"></span>&nbsp;</font></div>
  <font face="Arial,sans-serif" size="2"></font>
  <div dir="ltr" align="left"><font face="Arial,sans-serif" size="2"><span
 class="369121617-11052006"><font face="Arial" color="#0000ff" size="2">To
use this possibiliy, user can define a certain number of variables in
your sqlmapconfig.</font></span></font></div>
  <font face="Arial,sans-serif" size="2"></font>
  <div dir="ltr" align="left"><font face="Arial,sans-serif" size="2"><span
 class="369121617-11052006"></span>&nbsp;</font></div>
  <font face="Arial,sans-serif" size="2"></font>
  <div dir="ltr" align="left"><font face="Arial,sans-serif" size="2"><span
 class="369121617-11052006"><font face="Arial" color="#0000ff" size="2">Here
is an example.</font></span></font></div>
  <font face="Arial,sans-serif" size="2"></font>
  <div dir="ltr" align="left"><font face="Arial,sans-serif" size="2"><span
 class="369121617-11052006"><font color="#008080" size="2"><font  size="2"></font></font>
  <p><font color="#008080" size="2"><font color="#008080" size="2">&lt;</font><font
 color="#3f7f7f" size="2">transactionManager </font><font  color="#7f007f" size="2">type</font><font
size="2">=</font><font  color="#2a00ff" size="2">"JDBC"</font><font
color="#008080" size="2">&gt;</font></font></p>
  <font color="#008080" size="2"><font size="2"></font></font>
  <p><font color="#008080" size="2"><font color="#008080" size="2">&lt;</font><font
 color="#3f7f7f" size="2">dataSource </font><font color="#7f007f"  size="2">type</font><font
size="2">=</font><font color="#2a00ff"  size="2">"SIMPLE"</font><font
color="#008080" size="2">&gt;</font></font></p>
  <font color="#008080" size="2"><font size="2"></font></font>
  <p><font color="#008080" size="2"><font color="#008080" size="2">&lt;</font><font
 color="#3f7f7f" size="2">property </font><font color="#7f007f"  size="2">value</font><font
size="2">=</font><font color="#2a00ff"  size="2">"${driver}" </font><font
color="#7f007f" size="2">name</font><font  size="2">=</font><font
color="#2a00ff" size="2">"JDBC.Driver"</font><font  color="#008080" size="2">/&gt;</font></font></p>
  <font color="#008080" size="2"><font size="2"></font></font>
  <p><font color="#008080" size="2"><font color="#008080" size="2">&lt;</font><font
 color="#3f7f7f" size="2">property </font><font color="#7f007f"  size="2">value</font><font
size="2">=</font><font color="#2a00ff"  size="2">"${url}" </font><font
color="#7f007f" size="2">name</font><font  size="2">=</font><font
color="#2a00ff" size="2">"JDBC.ConnectionURL"</font><font  color="#008080" size="2">/&gt;</font></font></p>
  <font color="#008080" size="2"><font size="2"></font></font>
  <p><font color="#008080" size="2"><font color="#008080" size="2">&lt;</font><font
 color="#3f7f7f" size="2">property </font><font color="#7f007f"  size="2">value</font><font
size="2">=</font><font color="#2a00ff"  size="2">"${username}" </font><font
color="#7f007f" size="2">name</font><font  size="2">=</font><font
color="#2a00ff" size="2">"JDBC.Username"</font><font  color="#008080" size="2">/&gt;</font></font></p>
  <font color="#008080" size="2"><font size="2"></font></font>
  <p><font color="#008080" size="2"><font color="#008080" size="2">&lt;</font><font
 color="#3f7f7f" size="2">property </font><font color="#7f007f"  size="2">value</font><font
size="2">=</font><font color="#2a00ff"  size="2">"${password}" </font><font
color="#7f007f" size="2">name</font><font  size="2">=</font><font
color="#2a00ff" size="2">"JDBC.Password"</font><font  color="#008080" size="2">/&gt;</font></font></p>
  <font color="#008080" size="2"><font size="2"></font></font>
  <p><font color="#008080" size="2"><font color="#008080" size="2">&lt;</font><font
 color="#3f7f7f" size="2">property </font><font color="#7f007f"  size="2">value</font><font
size="2">=</font><font color="#2a00ff"  size="2">"${pingquery}" </font><font
color="#7f007f" size="2">name</font><font  size="2">=</font><font
color="#2a00ff" size="2">"Pool.PingQuery"</font><font  color="#008080" size="2">/&gt;</font></font></p>
  <font color="#008080" size="2"><font size="2"></font></font>
  <p><font color="#008080" size="2"><font color="#008080" size="2">&lt;</font><font
 color="#3f7f7f" size="2">property </font><font color="#7f007f"  size="2">value</font><font
size="2">=</font><font color="#2a00ff"  size="2">"${pingenable}" </font><font
color="#7f007f" size="2">name</font><font  size="2">=</font><font
color="#2a00ff" size="2">"Pool.PingEnabled"</font><font  color="#008080" size="2">/&gt;</font></font></p>
  <font color="#008080" size="2"><font size="2"></font></font>
  <p><font color="#008080" size="2"><font color="#008080" size="2">&lt;</font><font
 color="#3f7f7f" size="2">property </font><font color="#7f007f"  size="2">value</font><font
size="2">=</font><font color="#2a00ff"  size="2">"${pingoldertime}" </font><font
color="#7f007f" size="2">name</font><font  size="2">=</font><font
color="#2a00ff" size="2">"Pool.PingConnectionsOlderThan"</font><font  color="#008080"
size="2">/&gt;</font></font></p>
  <font color="#008080" size="2"><font size="2"></font></font>
  <p><font color="#008080" size="2"><font color="#008080" size="2">&lt;/</font><font
 color="#3f7f7f" size="2">dataSource</font><font color="#008080"  size="2">&gt;</font></font></p>
  <font color="#008080" size="2"><font size="2"></font></font>
  <p><font color="#008080" size="2"><font color="#008080" size="2">&lt;/</font><font
 color="#3f7f7f" size="2">transactionManager</font><font color="#008080"  size="2">&gt;</font></font></p>
  </span><span class="369121617-11052006"></span></font></div>
  <font face="Arial,sans-serif" size="2"></font>
  <div><font face="Arial,sans-serif" size="2">&nbsp;</font></div>
  <font face="Arial,sans-serif" size="2"></font>
  <div><font face="Arial,sans-serif" size="2">&nbsp;</font></div>
  <font face="Arial,sans-serif" size="2"></font>
  <div><font color="#0000ff" size="2" face="Arial,sans-serif"><span  class="369121617-11052006">Make
sure the property files contains all
the properties specified.&nbsp;I use.<br>
  </span></font></div>
  <font face="Arial,sans-serif" size="2"></font>
  <div><font color="#0000ff" face="Arial,sans-serif" size="2"><span  class="369121617-11052006">
  <p><font size="2">driver=<font color="#2a00ff">com.mysql.jdbc.Driver</font></font></p>
  <p><font size="2">url=<font color="#2a00ff">jdbc</font>:<font
 color="#2a00ff">mysql://localhost:3306/<span class="369121617-11052006">data</span></font></font></p>
  <p><font size="2">username=<font color="#2a00ff">root</font></font></p>
  <p><font size="2">password=</font></p>
  <p><font size="2">pingquery=<font color="#2a00ff">SELECT</font>
<font  color="#2a00ff">1</font></font></p>
  <p><font size="2">pingenable=<font color="#2a00ff">true</font></font></p>
  <p><font size="2">pingoldertime=<font color="#2a00ff">2000</font></font></p>
  </span></font></div>
  <font face="Arial,sans-serif" size="2"></font>
  <div><font face="Arial,sans-serif" size="2">&nbsp;</font></div>
  <font face="Arial,sans-serif" size="2"></font>
  <div><font face="Arial,sans-serif" size="2"><br>
  </font></div>
  <font face="Arial,sans-serif" size="2"></font>
  <div class="OutlookMessageHeader" lang="en-us" dir="ltr" align="left"><font  face="Arial,sans-serif"
size="2"></font>
  <hr tabindex="-1"><font face="Arial,sans-serif" size="2"></font><font
 face="Tahoma" size="2"><b>From:</b> Tony Qian
[<a class="moz-txt-link-freetext" href="mailto:daqiqian2@aol.com">mailto:daqiqian2@aol.com</a>]
<br>
  <b>Sent:</b> Thursday, 11 May 2006 12:22<br>
  <b>To:</b> <a class="moz-txt-link-abbreviated" href="mailto:user-java@ibatis.apache.org">user-java@ibatis.apache.org</a><br>
  <b>Subject:</b> autoReconnect=true doesn't solve closed connection
problem<br>
  </font><font face="Arial,sans-serif" size="2"><br>
  </font></div>
  <font face="Arial,sans-serif" size="2"></font><font size="2"  face="Arial,sans-serif"><font
face="Arial,sans-serif">All,<br>
  <br>
I set following in SglMapConfig.xml file:<br>
  <br>
&nbsp;&lt;property name="JDBC.ConnectionURL"
value="jdbc:mysql://hostName:3306/dbName?autoReconnect=true"/&gt;<br>
  <br>
It doesn't solve Communication Failure problem due to closed
connection. </font></font><font face="Arial,sans-serif" size="2"><font
 face="Arial,sans-serif">Setting autoReconnection=true works when
connection pool is managed by Tomcat (DBCP). (btw, PingQuery solves the
problem)</font></font><font face="Arial,sans-serif" size="2"><br>
  </font><font size="2" face="Arial,sans-serif"><font  face="Arial,sans-serif"><br>
database is MySql 5.019, JDBC diver is mysql-connector-java-3.1.11.<br>
  <br>
Did I do something wrong? any suggestion?<br>
  <br>
thanks,<br>
Tony<br>
  <br>
  <br>
  </font></font></blockquote>
</body>
</html>


Mime
View raw message