Search code examples
mysqljdbcc3p0

Checking if the JDBC connection is writable; using preferredTestQuery or ConnectionTester

Current setup:

Service host (Java) connects to (JDBC) master DB (MySQL) and has a slave (read-only) for reliability

Scenario:

In case of flip, I need to upgrade slave to new master. Present master becomes read-only and new master is promoted to read-write. I am looking to automatically refresh the C3P0 connection pool to the new master using a test query (executing writable query to database).

Idea:

Exploring a way to auto refresh JDBC connection pool so that they connect to new master when there is a flip (present master would be RO and slave would be promoted to RW and master cname would be updated)

Present config

        <property name="driverClass" value="${DriverClass}" />
        <property name="jdbcUrl" value="${ReadWriteDatabaseURL}${AccountDatabaseName}${JDBCProperties}" />
        <property name="user" value="${ReadWriteDatabaseCredentials}.principal" />
        <property name="password" value="${ReadWriteDatabaseCredentials}.credential" />
        <property name="testConnectionOnCheckout" value="true"/>
        <property name="testConnectionOnCheckin" value="false" />
        <property name="preferredTestQuery" value="update existing_table set value = now() where id = 1;"/>
        <property name="maxIdleTime" value="44000"/>
        <property name="idleConnectionTestPeriod" value="30"/>
        <property name="maxStatements" value="50"/>
        <property name="minPoolSize" value="3"/>
        <property name="maxPoolSize" value="3"/>
        <property name="acquireIncrement" value="2"/>
        <property name="checkoutTimeout" value="15000"/>
        <property name="acquireRetryDelay" value="1000"/>

Having a preferredTestQuery as select 1 from existing_table where 1 = 0 absolutely works without any issue

Approach 1:

In C3P0 config or hibernate config - Use preferredTestQuery as a writable query (something like insert/update an existing table) and have the query executed on every connection checkout (testConnectionCheckout = true). Not sure if this is supposed to be a simple query to test if db is up, because

  • Insert/update queries doesn't work. May be we need to begin, commit transaction ?
    • Error: org.hibernate.engine.jdbc.spi.SqlExceptionHelper: An attempt by a client to checkout a Connection has timed out.
  • Multiple queries doesn't work. Something like create table if not exists; insert into .. ; drop table ..;
    • Error: org.hibernate.engine.jdbc.spi.SqlExceptionHelper: An attempt by a client to checkout a Connection has timed out.

Approach 2: https://www.mchange.com/projects/c3p0/#configuring_connection_testing refers to Advanced users may define any kind of Connection testing they wish, by implementing a ConnectionTester and supplying the fully qualified name of the class as connectionTesterClassName. Neither I am sure how can this be done nor if it works for writable calls

Stack trace for above error:

Caused by: org.hibernate.exception.GenericJDBCException: Could not open connection
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:54) ~[hibernate-core-4.2.2.Final.jar:4.2.2.Final]
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125) ~[hibernate-core-4.2.2.Final.jar:4.2.2.Final]
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110) ~[hibernate-core-4.2.2.Final.jar:4.2.2.Final]
    at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.obtainConnection(LogicalConnectionImpl.java:221) ~[hibernate-core-4.2.2.Final.jar:4.2.2.Final]
    at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.getConnection(LogicalConnectionImpl.java:157) ~[hibernate-core-4.2.2.Final.jar:4.2.2.Final]
    at org.hibernate.internal.SessionImpl.connection(SessionImpl.java:550) ~[hibernate-core-4.2.2.Final.jar:4.2.2.Final]
    at org.springframework.orm.hibernate4.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:450) ~[spring-orm-4.3.20.RELEASE.jar:4.3.20.RELEASE]
    ... 42 more
Caused by: java.sql.SQLException: An attempt by a client to checkout a Connection has timed out.
    at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:118) ~[mchange-commons-java-0.2.10.jar:0.2.10]
    at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:77) ~[mchange-commons-java-0.2.10.jar:0.2.10]
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:690) ~[c3p0-0.9.5.1.jar:0.9.5.1]
    at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:140) ~[c3p0-0.9.5.1.jar:0.9.5.1]
    at org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource.getConnection(AbstractRoutingDataSource.java:164) ~[spring-jdbc-4.3.20.RELEASE.jar:4.3.20.RELEASE]
    at org.hibernate.service.jdbc.connections.internal.DatasourceConnectionProviderImpl.getConnection(DatasourceConnectionProviderImpl.java:141) ~[hibernate-core-4.2.2.Final.jar:4.2.2.Final]
    at org.hibernate.internal.AbstractSessionImpl$NonContextualJdbcConnectionAccess.obtainConnection(AbstractSessionImpl.java:292) ~[hibernate-core-4.2.2.Final.jar:4.2.2.Final]
    at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.obtainConnection(LogicalConnectionImpl.java:214) ~[hibernate-core-4.2.2.Final.jar:4.2.2.Final]
    at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.getConnection(LogicalConnectionImpl.java:157) ~[hibernate-core-4.2.2.Final.jar:4.2.2.Final]
    at org.hibernate.internal.SessionImpl.connection(SessionImpl.java:550) ~[hibernate-core-4.2.2.Final.jar:4.2.2.Final]
    at org.springframework.orm.hibernate4.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:450) ~[spring-orm-4.3.20.RELEASE.jar:4.3.20.RELEASE]
    ... 42 more
Caused by: com.mchange.v2.resourcepool.TimeoutException: A client timed out while waiting to acquire a resource from com.mchange.v2.resourcepool.BasicResourcePool@4c86da0c -- timeout at awaitAvailable()
    at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1467) ~[c3p0-0.9.5.1.jar:0.9.5.1]
    at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:644) ~[c3p0-0.9.5.1.jar:0.9.5.1]
    at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:554) ~[c3p0-0.9.5.1.jar:0.9.5.1]
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutAndMarkConnectionInUse(C3P0PooledConnectionPool.java:758) ~[c3p0-0.9.5.1.jar:0.9.5.1]
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:685) ~[c3p0-0.9.5.1.jar:0.9.5.1]
    at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:140) ~[c3p0-0.9.5.1.jar:0.9.5.1]
    at org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource.getConnection(AbstractRoutingDataSource.java:164) ~[spring-jdbc-4.3.20.RELEASE.jar:4.3.20.RELEASE]
    at org.hibernate.service.jdbc.connections.internal.DatasourceConnectionProviderImpl.getConnection(DatasourceConnectionProviderImpl.java:141) ~[hibernate-core-4.2.2.Final.jar:4.2.2.Final]
    at org.hibernate.internal.AbstractSessionImpl$NonContextualJdbcConnectionAccess.obtainConnection(AbstractSessionImpl.java:292) ~[hibernate-core-4.2.2.Final.jar:4.2.2.Final]
    at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.obtainConnection(LogicalConnectionImpl.java:214) ~[hibernate-core-4.2.2.Final.jar:4.2.2.Final]
    at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.getConnection(LogicalConnectionImpl.java:157) ~[hibernate-core-4.2.2.Final.jar:4.2.2.Final]
    at org.hibernate.internal.SessionImpl.connection(SessionImpl.java:550) ~[hibernate-core-4.2.2.Final.jar:4.2.2.Final]
    at org.springframework.orm.hibernate4.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:450) ~[spring-orm-4.3.20.RELEASE.jar:4.3.20.RELEASE]
    ... 42 more

Thanks

Solution

  • I can't comment on the larger failover issue, but you certainly can define a ConnectionTester that does basically anything you want. See the apidocs for the UnifiedConnectionTester interface for concise instructions.

    c3p0's built-in ConnectionTester uses Statement.executeQuery(...) so INSERTs etc may not work. Your custom ConnectionTester can do whatever it wants. You should be sure it does something sane if preferredTestQuery and rootCauseOutParamHolder are set null (in either method).

    If you wish you may ignore preferredTestQuery, or require that it be null (ie throw an Exception if preferredTestQuery is set).