views:

33

answers:

2

Hi,

My application is based on the hibernate to fetched data from a mysql server. This mysql server is replicated to another mysql server instance. Today, I got a downtime as the primary database server was gone down without any notice. To avoid any future accidental problem, I am planing to add a functionality that will enable system to connect to secondary database if it finds the primary down.

Is there exists a way by which, I can leverage hibernate library to enable this functionality?

+1  A: 

I have created following class after getting an idea form this thread It seems to be working well. I don't know if this is a good approach at all.

package com.vsd.server.hibernate;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

import org.apache.commons.lang.StringUtils;
import org.hibernate.HibernateException;
import org.hibernate.connection.C3P0ConnectionProvider;

public class FailoverConnectionProvider extends C3P0ConnectionProvider {

    String password;
    String username;
    String connectionString;
    String failover_connstring;

    @Override
    public Connection getConnection() throws SQLException {

        Connection conn = null;

        try {
            conn = DriverManager.getConnection(connectionString, username, password);
        } catch (Exception ex) {
            conn = DriverManager.getConnection(failover_connstring, username, password);
        }

        if(conn == null){
            throw new IllegalStateException("Database connection was not initialized");
        }

        return conn;

    }

    @Override
    public void configure(Properties properties) throws HibernateException {
        failover_connstring = properties.getProperty("hibernate.connection.failover.url");

        if (StringUtils.isBlank(connectionString)
                && StringUtils.isBlank(failover_connstring)
                && StringUtils.isBlank(username)
                && StringUtils.isBlank(password)) {
            throw new IllegalStateException("Unable to initialize connection provider for hibernate");
        }
    }
}
vijay.shad
This could be another way. But your current implementation is very basic (no connection pooling) and is IMO not suitable for production use.
Pascal Thivent
+4  A: 

Today, I got a downtime as the primary database server was gone down without any notice.

Well, that's the first thing you should fix (with proper monitoring).

(...) Is there exists a way by which, I can leverage hibernate library to enable this functionality?

To my knowledge, Hibernate doesn't really provide any facility for this. Personally, I would investigate the fail-over support of MySQL and its JDBC driver. I can't provide a very concrete answer because I haven't implemented this with MySQL but here are some pointers:

And, as mentioned at the bottom of the latest link above (and also in this comment):

You may also want to investigate the Load Balancing JDBC Pool (lbpol) tool, which provides a wrapper around the standard JDBC driver and enables you to use DB connection pools that includes checks for system failures and uneven load distribution. For more information, see Load Balancing JDBC Pool (lbpool).

Pascal Thivent
@Pascal Thivent : What do you think of the program I have added to the application?
vijay.shad
@Pascal Thivent : Can you suggest some monitoring process, for proper monitoring
vijay.shad
@Vijay Here are some: [Zenoss](http://community.zenoss.org/index.jspa), [Centreon](http://www.centreon.com/), [Opsview](http://www.opsview.com/downloads/download-opsview-community)
Pascal Thivent