jdbc connection failover

[andersonbd1]

Hi,
I'm wondering if Spring has any support for switching to another database if one fails (determined by certain parameters). Currently we use our own custom implementation to get datasources so that we can ensure that the datasource is up and running. The only solution I can come up with in Spring is putting an AOP filter somewhere which performs this checking and if necessary changes the instance of the spring bean which is the datasource to another datasource. This new instance would then have to be "reinjected" into all objects that contain a reference to it. However, I don't know if I can even do this. Also, it seems like a common problem, so I figure there must be a better solution.

Maybe this is more a question for dbcp?

Any guidance is appreciated.
Thanks,
Ben Anderson

[Marten Deinum]

Use a TargetSource around you datasource. Let that decide which DataSource to use. For an example see the HotSwappableTargetSource you also might want to take a look at the AbstractRoutingDataSource

[andersonbd1]

that looks easy enough - thanks!

[belugabob]

You can use a database driver that handles the failover for you.

We use c3p0 as our driver of choice as it provides this facility, as well as connection pooling.

An extract from the tomcat 'server.xml' file is as follows...

        <Resource
                name="jdbc/trm"
                type="com.mchange.v2.c3p0.ComboPooledDataSource"
                driverClass="com.microsoft.sqlserver.jdbc.SQLServerDriver"
                password="password"
                user="username"
                auth="Container"
                description="DB Connection pool for TRM application"
                minPoolSize="2"
                maxPoolSize="4"
                acquireIncrement="1"
                factory="org.apache.naming.factory.BeanFactory"
                jdbcUrl="jdbc:sqlserver://mainserver:1433;failoverPartner=backupserver;databaseName=nameofyourdatabase;applicationName=appname"
                preferredTestQuery="SELECT 'Connection' = 'true'"
                testConnectionOnCheckout="true"
                />

The beauty of this approach is that the whole configuration of the pooling/failover is external to your application and your therefore don't have to reinvent it for each new appl that you develop.

[seanscullion]

You can use a database driver that handles the failover for you.

We use c3p0 as our driver of choice as it provides this facility, as well as connection pooling.

An extract from the tomcat 'server.xml' file is as follows...

        <Resource
                jdbcUrl="jdbc:sqlserver://mainserver:1433;failoverPartner=backupserver;databaseName=nameofyourdatabase;applicationName=appname"
                />

That failoverPartner is wonderful if it's true - it serves our purposes exactly. I can't see any option for configuring mysql + c3p0 + java + tomcat with a failoverPartner... Do you know if such a configuration is possible?

Thanks!

Sean

[jfrank]

Old thread, but I stumbled upon it today while researching failover strategies. The notion of the failover being built into the connection pooler sounded pretty good, so I wanted to try it out. I had a little trouble finding any documentation for how to configure the jdbcUrl property for a MySQL setup, but I finally got it to work. Here is how my URL looks:

jdbc:mysql://localhost:3306,backupdb.example.com:3306/dbname

This assumes that you are using localhost for the primary and a machine with DNS name backupdb.example.com for the secondary, with a database name of dbname.