How to prevent application (using hibernate + c3po) from hanging if Mysql port is blocked?

Question

I've been trying to make my Java application db fail proof, so that they are no unexpected lags if the DB is not available. I've successfully handled the easy case when the database is totally down.

I am stuck on a case when the mysql is port is blocked for my machine and I try to access it. In this case, the application hangs forever, no timeout configuration mentioned in c3po documentation seems to handle this case predictably. On debugging, I can see that it hangs at the ReadAheadInputStream class in the mysql connector. The documentation for this class shows that it only blocks to satisfy a request of read.

I've used the following properties till now.

 <property name="hibernate.c3p0.validate" value="true"/>
 <property name="hibernate.c3p0.checkoutTimeout" value="5"/>
 <property name="hibernate.c3p0.preferredTestQuery" value="SELECT 1"/>

Apart from these, the other properties for configuring the connection pool are as mentioned in the documentation

Versions:

 <dependency>
        <groupId>c3p0</groupId>
        <artifactId>c3p0</artifactId>
        <version>0.9.1.2</version>
    </dependency>
    <dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>hibernate-c3p0</artifactId>
        <version>4.2.12.Final</version>
    </dependency>

Could anyone please guide me on this? Is there any other timeout param apart from those mentioned in the c3po doc that also needs to be configured?

Answer 1

You probably need to set network timeouts for MySQL, by default there are none. Use connectTimeout for the initial connection and socketTimeout for subsequent network operations. For example:

jdbc:mysql://dbhost:3306/dbname?connectTimeout=1000&socketTimeout=200000

Both are in millliseconds. See https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-configuration-properties.html.