Connection reset

brian_wainwright · **Posted:** Fri Mar 05, 2004 10:53 am

hey all,

Given the following hibernate.cfg.xml:

Code:

    <session-factory name="scape:/hibernate/SessionFactory">

      <!-- <property name="connection.datasource">java:comp/env/jdbc/scapeDB</property>-->

        <property name="show_sql">true</property>
        <property name="dialect">net.sf.hibernate.dialect.MySQLDialect</property>
        <property name="connection.username">scape</property>
        <property name="connection.password">scape</property>
        <property name="connection.url">jdbc:mysql://localhost:3306/scape?autoReconnect=true</property>
        <property name="connection.driver_class">org.gjt.mm.mysql.Driver</property>
        <property name="transaction.factory_class">net.sf.hibernate.transaction.JDBCTransactionFactory</property>
        <property name="dbcp.minIdle">1</property>
        <property name="cache.use_query_cache">true</property>
        <property name="use_outer_join">false</property>
        <property name="hibernate.cglib.use_reflection_optimizer">false</property>


        <!-- Mapping files -->
        <mapping resource="object.hbm.xml"/>


    </session-factory>

And the following hibernate.properties (yeah, that's all there is):

Code:

hibernate.cglib.use_reflection_optimizer=false

What additional configuration do I need to do to prevent the folowing error:

Code:

java.sql.SQLException: Communication link failure: java.net.SocketException, underlying cause: Connection reset

From what I understand, this is a JDBC error specifc to MySQL. Essentially after 8 hours, MySQL kills any idle connections. This is a default, however I had thought that setting the "autoReconnect" parameter to "true" in the JDBC url would solve this. It doesn't. Perhaps this is more of a JDBC question than a Hibernate question, but any infor would be great. Thanks!

--BW

anthony · **Posted:** Fri Mar 05, 2004 10:59 am

<property name="dbcp.maxActive">100</property>
<property name="dbcp.whenExhaustedAction">1</property>
<property name="dbcp.maxWait">120000</property>
<property name="dbcp.maxIdle">1</property>
<property name="dbcp.minIdle">0</property>
<property name="dbcp.ps.maxActive">10</property>
<property name="dbcp.ps.whenExhaustedAction">1</property>
<property name="dbcp.ps.maxWait">120000</property>
<property name="dbcp.ps.maxIdle">100</property>
<property name="dbcp.validationQuery">select 1 from dual</property>
<property name="dbcp.testOnBorrow">true</property>
<property name="dbcp.testOnReturn">false</property>

anthony · **Posted:** Fri Mar 05, 2004 11:01 am

<property name="dbcp.validationQuery">select 1 from dual</property>
<property name="dbcp.testOnBorrow">true</property>
<property name="dbcp.testOnReturn">false</property>

will test if the connexion is still "usable"
select 1 from dual is ok for Oracle, don't know if it works with your db

brian_wainwright · **Posted:** Fri Mar 05, 2004 11:05 am

Thanks delpouve that's extremely helpful.

One more thing - would setting the autoReconnectForPools parameter = true help as well?

--BW

brian_wainwright · **Posted:** Fri Mar 05, 2004 11:16 am

For mySQL an appropriate validation query would be "select 1"

--BW

anthony · **Posted:** Fri Mar 05, 2004 11:18 am

autoReconnectForPools parameter = true is a mySQL parameter?
if it is, i don't know...

brian_wainwright · **Posted:** Fri Mar 05, 2004 11:44 am

yeah. it is a MySQL parameter. Sorry.

I guess my final question regarding this situation is the following: If I was creating the JDBC Connection myself or providing it through my own connection providor I could catch the SQLException test the SQLState and then do whatever I need to do to restablish a connection to the DB. I got this from the MySQL Connector/J docs (hmmm... documentation...what's that? ;-) ) (http://www.mysql.com/documentation/connector-j/index.html#id2803835 Troubleshooting section 4.4:

Quote:

MySQL closes connections after 8 hours of inactivity. You either need to use a connection pool that handles stale connections or use the "autoReconnect" parameter (see "Developing Applications with MySQL Connector/J").

Also, you should be catching SQLExceptions in your application and dealing with them, rather than propagating them all the way until your application exits, this is just good programming practice. MySQL Connector/J will set the SQLState (see java.sql.SQLException.getSQLState() in your APIDOCS) to "08S01" when it encounters network-connectivity issues during the processing of a query. Your application code should then attempt to re-connect to MySQL at this point.

The following (simplistic) example shows what code that can handle these exceptions might look like:

And then it gives some code. So now the question, do any of the other ConnectionProvider implementations check for the state of the connection read: "handle stale connections" (DBCPConnectionProvider apparently does not - right?) and if not how can account for this MySQL specifc behavior beside writing my own ConnectionProvider implementation which I don't have time to do?

Thanks![/url][/quote]

anthony · **Posted:** Fri Mar 05, 2004 11:47 am

Quote:

(DBCPConnectionProvider apparently does not - right?)

i can't give you more help, the only thing i know is that we had broken pipe exception due to Oracle disconnections and that the parameters i gave you solved the problem

[/quote]

brian_wainwright · **Posted:** Fri Mar 05, 2004 11:49 am

the above post assumes that autoReconnectForPool isn't what I need to use.... but it may be...

brian_wainwright · **Posted:** Fri Mar 05, 2004 11:51 am

Thanks man. I really appreciate the help!