<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:p="urn:schemas-microsoft-com:office:powerpoint" xmlns:a="urn:schemas-microsoft-com:office:access" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema" xmlns:b="urn:schemas-microsoft-com:office:publisher" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:c="urn:schemas-microsoft-com:office:component:spreadsheet" xmlns:odc="urn:schemas-microsoft-com:office:odc" xmlns:oa="urn:schemas-microsoft-com:office:activation" xmlns:html="http://www.w3.org/TR/REC-html40" xmlns:q="http://schemas.xmlsoap.org/soap/envelope/" xmlns:rtc="http://microsoft.com/officenet/conferencing" xmlns:D="DAV:" xmlns:Repl="http://schemas.microsoft.com/repl/" xmlns:mt="http://schemas.microsoft.com/sharepoint/soap/meetings/" xmlns:x2="http://schemas.microsoft.com/office/excel/2003/xml" xmlns:ppda="http://www.passport.com/NameSpace.xsd" xmlns:ois="http://schemas.microsoft.com/sharepoint/soap/ois/" xmlns:dir="http://schemas.microsoft.com/sharepoint/soap/directory/" xmlns:ds="http://www.w3.org/2000/09/xmldsig#" xmlns:dsp="http://schemas.microsoft.com/sharepoint/dsp" xmlns:udc="http://schemas.microsoft.com/data/udc" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sub="http://schemas.microsoft.com/sharepoint/soap/2002/1/alerts/" xmlns:ec="http://www.w3.org/2001/04/xmlenc#" xmlns:sp="http://schemas.microsoft.com/sharepoint/" xmlns:sps="http://schemas.microsoft.com/sharepoint/soap/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:udcs="http://schemas.microsoft.com/data/udc/soap" xmlns:udcxf="http://schemas.microsoft.com/data/udc/xmlfile" xmlns:udcp2p="http://schemas.microsoft.com/data/udc/parttopart" xmlns:wf="http://schemas.microsoft.com/sharepoint/soap/workflow/" xmlns:dsss="http://schemas.microsoft.com/office/2006/digsig-setup" xmlns:dssi="http://schemas.microsoft.com/office/2006/digsig" xmlns:mdssi="http://schemas.openxmlformats.org/package/2006/digital-signature" xmlns:mver="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns:mrels="http://schemas.openxmlformats.org/package/2006/relationships" xmlns:spwp="http://microsoft.com/sharepoint/webpartpages" xmlns:ex12t="http://schemas.microsoft.com/exchange/services/2006/types" xmlns:ex12m="http://schemas.microsoft.com/exchange/services/2006/messages" xmlns:pptsl="http://schemas.microsoft.com/sharepoint/soap/SlideLibrary/" xmlns:spsl="http://microsoft.com/webservices/SharePointPortalServer/PublishedLinksService" xmlns:Z="urn:schemas-microsoft-com:" xmlns:st="" xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type content="text/html; charset=iso-8859-1"><meta name=Generator content="Microsoft Word 12 (filtered medium)"><style><!--
/* Font Definitions */
@font-face
{font-family:Wingdings;
panose-1:5 0 0 0 0 0 0 0 0 0;}
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
p
{mso-style-priority:99;
mso-margin-top-alt:auto;
margin-right:0cm;
mso-margin-bottom-alt:auto;
margin-left:0cm;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
code
{mso-style-priority:99;
font-family:"Courier New";}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:"Calibri","sans-serif";
color:windowtext;}
span.hps
{mso-style-name:hps;}
.MsoChpDefault
{mso-style-type:export-only;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.WordSection1
{page:WordSection1;}
/* List Definitions */
@list l0
{mso-list-id:364060724;
mso-list-type:hybrid;
mso-list-template-ids:758658204 67698689 67698691 67698693 67698689 67698691 67698693 67698689 67698691 67698693;}
@list l0:level1
{mso-level-number-format:bullet;
mso-level-text:\F0B7;
mso-level-tab-stop:none;
mso-level-number-position:left;
margin-left:39.0pt;
text-indent:-18.0pt;
font-family:Symbol;}
@list l1
{mso-list-id:604965100;
mso-list-template-ids:-520845180;}
@list l1:level1
{mso-level-number-format:bullet;
mso-level-text:\F0B7;
mso-level-tab-stop:36.0pt;
mso-level-number-position:left;
text-indent:-18.0pt;
mso-ansi-font-size:10.0pt;
font-family:Symbol;}
@list l2
{mso-list-id:1316301652;
mso-list-template-ids:-710929998;}
@list l2:level1
{mso-level-number-format:bullet;
mso-level-text:\F0B7;
mso-level-tab-stop:36.0pt;
mso-level-number-position:left;
text-indent:-18.0pt;
mso-ansi-font-size:10.0pt;
font-family:Symbol;}
ol
{margin-bottom:0cm;}
ul
{margin-bottom:0cm;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></head><body lang=EN-US link=blue vlink=purple><div class=WordSection1><p><span style='font-family:"Arial","sans-serif"'>Hi all,<o:p></o:p></span></p><p style='min-height: 8pt'><span style='font-family:"Arial","sans-serif"'> We are having problems in production enviroments with datasource connections. We have a JBOSS 4.2.2.GA with a datasource configured to create connections against an Oracle Database 10g Enterprise Edition Release 10.2.0.1.0. It seems that some connections returned by the pool are closed because we get the following exception when some thread try to use that connections:<o:p></o:p></span></p><p style='min-height: 8pt'><span lang=ES-TRAD style='font-family:"Arial","sans-serif"'> </span><span lang=ES-TRAD style='font-family:"Courier New"'>2011-05-15 01:32:01,455 ERROR [STDERR] java.sql.SQLException: Conexi?n cerrada<o:p></o:p></span></p><p><span lang=ES-TRAD style='font-family:"Courier New"'>2011-05-15 01:32:01,455 ERROR [STDERR] at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)<o:p></o:p></span></p><p><span lang=ES-TRAD style='font-family:"Courier New"'>2011-05-15 01:32:01,455 ERROR [STDERR] at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)<o:p></o:p></span></p><p><span lang=ES-TRAD style='font-family:"Courier New"'>2011-05-15 01:32:01,455 ERROR [STDERR] at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)<o:p></o:p></span></p><p><span style='font-family:"Courier New"'>2011-05-15 01:32:01,455 ERROR [STDERR] at oracle.jdbc.driver.PhysicalConnection.prepareStatement(PhysicalConnection.java:868)<o:p></o:p></span></p><p><span style='font-family:"Courier New"'>2011-05-15 01:32:01,455 ERROR [STDERR] at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.doPrepareStatement(BaseWrapperManagedConnection.java:349)<o:p></o:p></span></p><p><span style='font-family:"Courier New"'>2011-05-15 01:32:01,456 ERROR [STDERR] at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.prepareStatement(BaseWrapperManagedConnection.java:344)<o:p></o:p></span></p><p><span style='font-family:"Courier New"'>2011-05-15 01:32:01,456 ERROR [STDERR] at org.jboss.resource.adapter.jdbc.WrappedConnection.prepareStatement(WrappedConnection.java:201)<o:p></o:p></span></p><p style='min-height: 8pt'><span style='font-family:"Arial","sans-serif"'> Other connections returned by de DB connection pool more ore less at the same time seems to work properly. For some reason (firewalls, proxyx may be) this connections are not in use.<o:p></o:p></span></p><p style='min-height: 8pt'><span style='font-family:"Arial","sans-serif"'> We think that including a configuration in the datasource which forces the pool to test the connections before being borrowed would solve this problem, because the pool would returned util connections prepared to be used. We have found two </span><span class=hps><span lang=EN style='font-family:"Arial","sans-serif"'>possible solutions after reading 5.3 Configuring JDBC DataSources</span></span><span lang=EN style='font-family:"Arial","sans-serif"'> </span><span class=hps><span lang=EN style='font-family:"Arial","sans-serif"'><a href="http://docs.jboss.org/jbossas/docs/Server_Configuration_Guide/4/html/Connectors_on_JBoss-Configuring_JDBC_DataSources.html"><span style='color:windowtext'>http://docs.jboss.org/jbossas/docs/Server_Configuration_Guide/4/html/Connectors_on_JBoss-Configuring_JDBC_DataSources.html :</span></a></span></span><span style='font-family:"Arial","sans-serif"'><o:p></o:p></span></p><p><span style='font-family:"Arial","sans-serif"'><br>We have some questions about how these configurations works:<o:p></o:p></span></p><p style='margin-left:39.0pt;text-indent:-18.0pt;mso-list:l0 level1 lfo3;min-height: 8pt'><![if !supportLists]><span style='font-family:Symbol'><span style='mso-list:Ignore'>·<span style='font:7.0pt "Times New Roman"'> </span></span></span><![endif]><strong><span style='font-family:"Arial","sans-serif"'>check-valid-connection-sql</span></strong><span style='font-family:"Arial","sans-serif"'><o:p></o:p></span></p><p><span style='font-family:"Arial","sans-serif"'> Before the pool returned one connection, it is checked, executing the sql sentence specified, such as select * from dual<o:p></o:p></span></p><p><span style='font-family:"Arial","sans-serif"'> If the SQL sentence is executed correctly the connection is returned because the test was OK. Am I right?<o:p></o:p></span></p><p><span style='font-family:"Arial","sans-serif"'> If it was not posible to executed the statement, what is the </span><span class=hps><span lang=EN style='font-family:"Arial","sans-serif"'>behavior? Would the datasource try with another connection an consider that the previous connection is not in use and would be removed from pool? Does exists in JBOSS datasource configuration any tag which specifies a timeout to decide that a connection is closed in case that there are any response to the SQL test statement?</span></span><span style='font-family:"Arial","sans-serif"'><o:p></o:p></span></p><p><span style='font-family:"Arial","sans-serif"'> Including a check-valid-connections, does ensure that every connection returned by de pool will be in used in terms of connectivity?<o:p></o:p></span></p><p><span style='font-family:"Arial","sans-serif"'> If all the connections of the pool were invalid, Will the JBOSS datasource test all the connections and when find that all are invalid would create a new pool with new connections?<o:p></o:p></span></p><p><span style='font-family:"Arial","sans-serif"'> As I understand in this kind of solution a "user" of pool connections would get an exception after pool has test all available connections and all were invalid, Am I right?<o:p></o:p></span></p><p><span style='font-family:"Arial","sans-serif"'> What would be the behaviour of pool in terms of throwing exceptions when request a DB connection?<o:p></o:p></span></p><p style='min-height: 8pt'><span style='font-family:"Arial","sans-serif"'> <o:p></o:p></span></p><ul type=disc><li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;mso-list:l2 level1 lfo2'><strong><span style='font-size:12.0pt;font-family:"Arial","sans-serif"'>valid-connection-checker-class-name</span></strong><span style='font-size:12.0pt;font-family:"Arial","sans-serif"'><o:p></o:p></span></li></ul><p><span style='font-family:"Arial","sans-serif"'> What are the differences between this solution and the one above beyond the procedure to decide if a connection is valid? I mean, if I implement the org.jboss.resource.adapter.jdbc.ValidConnectionChecker interface and the method <code><span style='font-family:"Arial","sans-serif"'>SQLException isValidConnection(Connection e) doesn't do anything more than executed "select * from dual" statement we would be talking about the same solution?</span></code><o:p></o:p></span></p><p style='min-height: 8pt'><span style='font-family:"Arial","sans-serif"'> valid-connection-checker-class-name provides more possibilities to test a connection, right? but the rest of behaviour of datasource is equal in the two solutions, the unique difference is the method to test if a connection is OK, Am right?<o:p></o:p></span></p><p style='min-height: 8pt'><span style='font-family:"Arial","sans-serif"'> <o:p></o:p></span></p><p><span style='font-family:"Arial","sans-serif"'>Many thanks, best regards,<o:p></o:p></span></p><p style='min-height: 8pt'><span style='font-family:"Arial","sans-serif"'><o:p> </o:p></span></p><p><span style='font-family:"Arial","sans-serif"'>Juan Moratilla Peón<o:p></o:p></span></p><p style='min-height: 8pt'><span style='font-family:"Arial","sans-serif"'> <o:p></o:p></span></p><p><span style='font-family:"Arial","sans-serif"'>HP<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:12.0pt;font-family:"Arial","sans-serif"'><o:p> </o:p></span></p></div></body></html>