title: JDBC Connection Pool test query "SELECT 1" does not catch AWS RDS Writer/Reader failover
body: <p>We are running an AWS RDS Aurora/MySQL database in a cluster with a writer and a reader instance where the writer is replicated to the reader. </p>
  
  <p>The application accessing the database is a standard java application using a HikariCP Connection Pool. The pool is configured to use a <code>"SELECT 1"</code> test query on checkout. </p>
  
  <p>What we noticed is that once in a while RDS fails over the writer to the reader. The failover can also be replicated manually by clicking "Instance Actions/Failover" in the AWS console. </p>
  
  <p>The connection pool is not able to detect the failover and the fact that it is now connected to a reader database, as the <code>"SELECT 1"</code> test queries still succeed. However any subsequent database updates fail with <code>"java.sql.SQLException: The MySQL server is running with the --read-only option so it cannot execute this statement"</code> errors. </p>
  
  <p>It appears that instead of a <code>"SELECT 1"</code> test query, the Connection Pool can detect that it is now connected to the reader by using a <code>"SELECT count(1) FROM test_table WHERE 1 = 2 FOR UPDATE"</code> test query. </p>
  
  <ol>
  <li>Has anybody experienced the same issue? </li>
  <li>Are there any downsides on using <code>"FOR UPDATE"</code> in the test query? </li>
  <li>Are there any alternate or better approaches of handling an AWS RDS cluster writer/reader failover? </li>
  </ol>
  
  <p>Your help is much appreciated</p>
  
  <p>Bernie</p>