title: Checking if the JDBC connection is writable; using preferredTestQuery or ConnectionTester
body: <p><strong>Current setup:</strong></p>
  <p>Service host (Java) connects to (JDBC) master DB (MySQL) and has a slave (read-only) for reliability</p>
  <p><strong>Scenario:</strong></p>
  <p>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).</p>
  <p><strong>Idea:</strong></p>
  <p>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)</p>
  <p><strong>Present config</strong></p>
  <pre><code>        &lt;property name=&quot;driverClass&quot; value=&quot;${DriverClass}&quot; /&gt;
          &lt;property name=&quot;jdbcUrl&quot; value=&quot;${ReadWriteDatabaseURL}${AccountDatabaseName}${JDBCProperties}&quot; /&gt;
          &lt;property name=&quot;user&quot; value=&quot;${ReadWriteDatabaseCredentials}.principal&quot; /&gt;
          &lt;property name=&quot;password&quot; value=&quot;${ReadWriteDatabaseCredentials}.credential&quot; /&gt;
          &lt;property name=&quot;testConnectionOnCheckout&quot; value=&quot;true&quot;/&gt;
          &lt;property name=&quot;testConnectionOnCheckin&quot; value=&quot;false&quot; /&gt;
          &lt;property name=&quot;preferredTestQuery&quot; value=&quot;update existing_table set value = now() where id = 1;&quot;/&gt;
          &lt;property name=&quot;maxIdleTime&quot; value=&quot;44000&quot;/&gt;
          &lt;property name=&quot;idleConnectionTestPeriod&quot; value=&quot;30&quot;/&gt;
          &lt;property name=&quot;maxStatements&quot; value=&quot;50&quot;/&gt;
          &lt;property name=&quot;minPoolSize&quot; value=&quot;3&quot;/&gt;
          &lt;property name=&quot;maxPoolSize&quot; value=&quot;3&quot;/&gt;
          &lt;property name=&quot;acquireIncrement&quot; value=&quot;2&quot;/&gt;
          &lt;property name=&quot;checkoutTimeout&quot; value=&quot;15000&quot;/&gt;
          &lt;property name=&quot;acquireRetryDelay&quot; value=&quot;1000&quot;/&gt;
  </code></pre>
  <p>Having a <strong>preferredTestQuery</strong> as <code>select 1 from existing_table where 1 = 0</code> absolutely works without any issue</p>
  <p><em>Approach 1:</em></p>
  <p>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</p>
  <ul>
  <li>Insert/update queries doesn't work. May be we need to <a href="https://stackoverflow.com/questions/26636754/java-sql-sqlexception-an-attempt-by-a-client-to-checkout-a-connection-has-timed">begin, commit transaction</a> ?
  <ul>
  <li>Error: <code>org.hibernate.engine.jdbc.spi.SqlExceptionHelper: An attempt by a client to checkout a Connection has timed out.</code></li>
  </ul>
  </li>
  <li>Multiple queries doesn't work. Something like <code>create table if not exists; insert into .. ; drop table ..;</code>
  <ul>
  <li>Error: <code>org.hibernate.engine.jdbc.spi.SqlExceptionHelper: An attempt by a client to checkout a Connection has timed out.</code></li>
  </ul>
  </li>
  </ul>
  <p><em>Approach 2:</em>
  <a href="https://www.mchange.com/projects/c3p0/#configuring_connection_testing" rel="nofollow noreferrer">https://www.mchange.com/projects/c3p0/#configuring_connection_testing</a> refers to <em>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.</em> Neither I am sure how can this be done nor if it works for writable calls</p>
  <p>Stack trace for above error:</p>
  <pre><code>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
  </code></pre>
  <p>Thanks</p>