[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 7004
  • Last Modified:

How to configure a datasource that reconnects automaticaly after connection lost?

My problem is that if the by any reason the connection from my application-servers (Tomcat) to the database (Postgres) is lost and I fix the issue, I have to restart all apps which use this database. I'm looking for a configuration so that the app-server notices that the connetion is available again and establishes this connection by itself without a tomcat restart.

At the moment the config is the following (see code).
We're using DBCP for Connection pooling. We want to keep connection pooling, but it may be another implemantion if you know a better one.
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="org.postgresql.Driver"/>
        <property name="url" value="jdbc:postgresql://localhost/mydb"/>
        <property name="username" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>
        <property name="maxActive" value="${jdbc.maxActive}"/>
        <property name="maxIdle" value="${jdbc.maxIdle}"/>
        <property name="maxWait" value="1000"/>
        <property name="defaultAutoCommit" value="true"/>
    </bean>

Open in new window

0
mahome
Asked:
mahome
  • 8
  • 6
  • 5
  • +1
4 Solutions
 
CEHJCommented:
How/why is it losing the connection in the first place?
0
 
mahomeAuthor Commented:
Several reasons:
  • network problems app-server -> DB-Server
  • Problem in DB-Server so that kill was necessary
  • restart of DB-Server
0
 
CEHJCommented:
Sounds like you'd be better off configuring failover
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
mahomeAuthor Commented:
For the future you are right, but in the moment we are just having one DB-Server. In the future the aim is as you said having replication with failover. So till that I'm looking for a fast way to reconnect without restarting the app-server. That also can be via JMX if that is possible.
0
 
CEHJCommented:
Sounds a difficult proposition. What would the app be doing while the db box is offline to avoid exceptions - queuing queries?
0
 
mahomeAuthor Commented:
Exceptions avoidance is not necessary as our webapps are not usable without connection to the database (except the help page). I just want to avoid the restarts of the 8 apps which rely on this database to reduce off-time.

I just found such a config for MySQL: http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-configuration-properties.html -> autoReconnect, autoReconnectForPool, but I need an equivalent for Postgres.
0
 
objectsCommented:
try setting the testOnBorrow parameter

0
 
CEHJCommented:
That looks like a pretty powerful datasource but my  it might mainly implement its autoReconnect functionality via the failover that's mentioned
0
 
gheistCommented:
http://c-jdbc.ow2.org/
If you had two servers it will be better solution to network failures, but anyway this one handles reconnections and pooling corectly
0
 
CEHJCommented:
>>http://c-jdbc.ow2.org/

Again, that looks like reconnection is implemented through failover principles ...
0
 
gheistCommented:
You are right - but it will reconnect without destroying database connection in application.
0
 
CEHJCommented:
>>...but it will reconnect

How will it do that when there's only one server (which is down)?
0
 
gheistCommented:
It implements keep-alive which prevents most of modern network faults from kicking in.
It reconnects when keep-alive fails.

And it works btw.
0
 
objectsCommented:
And handling this is fairly standard stuff, any decent production app will be setup to gracefully handle dead connections.

0
 
mahomeAuthor Commented:
Thanks for your input I will test the both solutions.
0
 
gheistCommented:
Report back - there are more to come.
0
 
mahomeAuthor Commented:
1st result: testOnBorrow seems to work.
Please have a look at my test code, if the test is correct. I have a while loop between I have the possibility to stop/start the DB-Server. With the following two lines it worked:
dataSource.setTestOnBorrow(true);
dataSource.setValidationQuery("select 1");

@gheist
>>Report back - there are more to come.
Even if this solutions seems to work I would also be interested in the other solutions. Just a hint or name, to know what possibilites are there.

BasicDataSource dataSource = new BasicDataSource();
dataSource.setDriverClassName("org.postgresql.Driver");
dataSource.setUrl("jdbc:postgresql://localhost/myDB");
dataSource.setUsername("***");
dataSource.setPassword("***");
dataSource.setMaxActive(1);
dataSource.setMaxWait(1000);
 
// without the following 2 lines it keeps the failed connection
dataSource.setTestOnBorrow(true);
dataSource.setValidationQuery("select 1");
 
System.out.println(dataSource.getInitialSize());
System.out.println(dataSource.getMinIdle());
System.out.println(dataSource.getMaxIdle());
System.out.println(dataSource.getMaxActive());
 
 
BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
while (true) {
	br.readLine(); // wait-position to start/stop DB-Server
	System.out.println("idle: " + dataSource.getNumIdle());
	System.out.println("active: " + dataSource.getNumActive());
	
	try {
		Connection connection = dataSource.getConnection();
		System.out.println(connection);
		Statement statement = connection.createStatement();
		boolean execute = statement.execute("select 1");
		System.out.println(execute);
		connection.close();
	} catch (SQLException e) {
		e.printStackTrace();
	}
}

Open in new window

0
 
mahomeAuthor Commented:
The upper solution still works, but has one disadvantage: The validationQuery. For each getConnection there has to be an extra roundtrip to the DB. It would be better if the connection would be invalidated on Exception rather than on next borrow. That would be possible but sadly DBCP don't implement PoolableConnection.handleException(), where there would be the place to do that.

C-JDBC is for my problem oversized and I don't want to setup a cluster controller just for one DB. But I'll keep it in mind for future.

Has anyone experience with one of those: http://java-source.net/open-source/connection-pools
The only one I've heard of yet is C3P0

@gheist
>>Report back - there are more to come.
Please tell.
0
 
gheistCommented:
pgpool, other java pools etc etc...
0
 
objectsCommented:
c3po was my next suggestion :)

0
 
mahomeAuthor Commented:
I finally used C3P0, reasons:
FYI: here is my config:

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
	<property name="driverClass" value="${jdbc.driverClassName}"></property>
	<property name="jdbcUrl" value="${jdbc.url}"/>
	<property name="user" value="${jdbc.username}"/>
	<property name="password" value="${jdbc.password}"/>
	<property name="initialPoolSize" value="5"></property>
	<property name="minPoolSize" value="5"></property>
	<property name="maxPoolSize" value="${jdbc.maxActive}"/>
	<property name="checkoutTimeout" value="1000"/> <!--  Wartezeit auf freie Pool-Connection -->
	<property name="maxStatements" value="50"></property>
	<property name="automaticTestTable" value="C3P0_TEST_TABLE"></property>
	<property name="testConnectionOnCheckin" value="true"></property>
	<property name="idleConnectionTestPeriod" value="60"></property> <!--  in Sekunden -->
</bean>

Open in new window

0
 
mahomeAuthor Commented:
Thanks for your input.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 8
  • 6
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now