0

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?

lazyloader
  • 157
  • 2
  • 10
  • Fixing the hangs on the database (or network) side, as @user2612030 suggests, is definitely the best way to go if you can. If you can't, there is one other timeout that might help you workaround the problem, [maxAdministrativeTaskTime](http://www.mchange.com/projects/c3p0/#maxAdministrativeTaskTime). – Steve Waldman Mar 30 '18 at 06:17

1 Answers1

0

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.

ewramner
  • 5,810
  • 2
  • 17
  • 33
  • Hey thanks for your reply. That definitely looks like a good solution. I didn't think in that direction. However, I tried it, and now my connection attempt throws timeout exception for normal cases as well. I've changes the hibernate jdbc url just like you said – lazyloader Mar 30 '18 at 06:52
  • Try increasing the timeouts. I've seen connect timeouts of 5s, though usually 1s is more than enough. If you still run into problems, remove the socket timeout. It can be problematic if the connection is idle and it will be when pooled. On the other hand it is vital for detecting connections broken by firewalls, so it is a balance. – ewramner Mar 30 '18 at 07:08
  • I didn't escape the query param separators properly in the connection URL, so it was trying to connect to a wrong location. Thanks for the help. Could you please give me some info about these params. I tried only connectTimeout and it didn't seem to work. Only using socketTimeout threw an exception (which is what I need). As per the documentation the connect timeout should be triggered upon acquiring the initial connection but it didn't. Did I understand it right? – lazyloader Mar 30 '18 at 09:18
  • The connect timeout is for the initial TCP connection. If you are using SSL the handshake is not included, neither is any processing in MySQL. If the database is listening, setting up a TCP connection is usually quite fast so that will only fail if MySQL is down or under very heavy load. The socket timeout applies for network operations with an established connection, for example for reading a response. A short timeout may kill a working connection waiting for a query, but a long timeout may leave the application hanging waiting for a response. It is a balance. – ewramner Mar 30 '18 at 09:51
  • I am intentionally keeping blocking the 3306 port for mysql , but connect timeout isn't reflected here. I understand what you say about socket timeout , so if I have 5 connections in the pool, and no requests come from the application, will all these connections throw socket timeout. I have just started working on this, so I apologize if the questions sound confusing or dumb. – lazyloader Mar 30 '18 at 10:17
  • If you have 5 idle connections and have blocked the port there should be errors eventually when C3P0 tries to validate them. If that occurs in the background using a validation thread or only on checkout is configurable in C3P0. If you want to use a background thread see http://www.mchange.com/projects/c3p0/index.html#idleConnectionTestPeriod. – ewramner Mar 30 '18 at 13:41
  • Okay, I get it now. Thanks a lot :) ! – lazyloader Apr 02 '18 at 07:36