Example program that uses a database connection checkout test to avoid using a read-only connection after Aurora failover.

AuroraFailoverTest.java

// Copyright (c) Keith D Gregory, all rights reserved
package com.kdgregory.sandbox.jdbc;

import java.util.List;
import java.util.Map;
import java.util.UUID;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

/**
 *  Configures a HikariCP connection pool to silently retry connections
 *  if they're discovered to be read-only (which may happen during
 *  Aurora failover).
 */
public class AuroraFailoverTest
{
    private static Logger logger = LoggerFactory.getLogger(AuroraFailoverTest.class);

    public static void main(String[] argv)
    throws Exception
    {
        final String hostname = "REDACTED";
        final String defaultDB = "example";

        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:mysql://" + hostname + ":3306/" + defaultDB);
        config.setUsername("REDACTED");
        config.setPassword("REDACTED");
        config.setConnectionTestQuery("select throwIfReadOnly()");

        logger.info("creating DataSource");
        try (HikariDataSource datasource = new HikariDataSource(config))
        {
            for (int ii = 0 ; ii < 3600 ; ii++)
            {
                Thread.sleep(1000);
                try
                {
                    logger.info("executing insert");
                    QueryUtil.executeUpdate(datasource, "insert into foo (value) values(?)", UUID.randomUUID().toString());
                }
                catch (Exception ex)
                {
                    logger.warn("exception on insert: {}", ex);
                }
            }
        }
    }
}

AuroraFailoverTest2.java

// Copyright (c) Keith D Gregory, all rights reserved
package com.kdgregory.sandbox.jdbc;

import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.sql.Statement;
import java.util.UUID;

import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

/**
 *  Example of wrapping the HikariCP connection pool with a DataSource that ensures
 *  we have a read-write connection.
 */
public class AuroraFailoverTest2
{
    private static Logger logger = LoggerFactory.getLogger(AuroraFailoverTest2.class);

    public static void main(String[] argv)
    throws Exception
    {
        final String hostname = "REDACTED";
        final String defaultDB = "REDACTED";

        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:mysql://" + hostname + ":3306/" + defaultDB);
        config.setUsername("REDACTED");
        config.setPassword("REDACTED");

        logger.info("creating DataSource");
        try (HikariDataSource hikariDS = new HikariDataSource(config))
        {
            DataSource datasource = new WrappedDataSource(hikariDS);
            for (int ii = 0 ; ii < 3600 ; ii++)
            {
                Thread.sleep(1000);
                try
                {
                    logger.info("executing insert");
                    QueryUtil.executeUpdate(datasource, "insert into foo (value) values(?)", UUID.randomUUID().toString());
                }
                catch (Exception ex)
                {
                    logger.warn("exception on insert: {}", ex);
                }
            }
        }
    }
    
    
    private static class WrappedDataSource
    implements DataSource
    {
        Logger logger = LoggerFactory.getLogger(getClass());
        
        private HikariDataSource delegate;
        
        public WrappedDataSource(HikariDataSource delegate)
        {
            this.delegate = delegate;
        }

        @Override
        public Connection getConnection() throws SQLException
        {
            while (true)
            {
                Connection cxt = delegate.getConnection();
                try (Statement stmt = cxt.createStatement())
                {
                    try (ResultSet rslt = stmt.executeQuery("select @@innodb_read_only"))
                    {
                        if (rslt.next() && ! rslt.getBoolean(1))
                        {
                            logger.debug("returning connection {}", cxt);
                            return cxt;
                        }
                    }
                }
                logger.warn("received a read-only connection, possible fail-over happening");
                delegate.evictConnection(cxt);
                try
                {
                    Thread.sleep(1000);
                }
                catch (InterruptedException ignored)
                {
                    // if we're interrupted we just retry
                }
            }
        }

        @Override
        public Connection getConnection(String username, String password) throws SQLException
        {
            return delegate.getConnection(username, password);
        }

        @Override
        public PrintWriter getLogWriter() throws SQLException
        {
            return delegate.getLogWriter();
        }

        @Override
        public void setLogWriter(PrintWriter out) throws SQLException
        {
            delegate.setLogWriter(out);
        }

        @Override
        public void setLoginTimeout(int seconds) throws SQLException
        {
            delegate.setLoginTimeout(seconds);
        }

        @Override
        public int getLoginTimeout() throws SQLException
        {
            return delegate.getLoginTimeout();
        }

        @Override
        public java.util.logging.Logger getParentLogger() throws SQLFeatureNotSupportedException
        {
            return delegate.getParentLogger();
        }

        @Override
        public <T> T unwrap(Class<T> iface) throws SQLException
        {
            return delegate.unwrap(iface);
        }

        @Override
        public boolean isWrapperFor(Class<?> iface) throws SQLException
        {
            return delegate.isWrapperFor(iface);
        }
    }
}

QueryUtil.java

// Copyright (c) Keith D Gregory, all rights reserved
package com.kdgregory.sandbox.jdbc;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;


/**
 *  Utilities for executing JDBC queries. Requires Java 7.
 */
public class QueryUtil
{
    /**
     *  Executes the passed SQL statement and returns the results.
     */
    public static List<Map<String,Object>> executeQuery(DataSource ds, String sql)
    throws SQLException
    {
        try (Connection cxt = ds.getConnection())
        {
            try (Statement stmt = cxt.createStatement())
            {
                try (ResultSet rslt = stmt.executeQuery(sql))
                {
                    return processResultSet(rslt);
                }
            }
        }
    }


    /**
     *  Creates and executes a prepared statement, returning the numbr of rows updated.
     */
    public static int executeUpdate(DataSource ds, String sql, Object... values)
    throws SQLException
    {
        try (Connection cxt = ds.getConnection())
        {
            try (PreparedStatement stmt = cxt.prepareStatement(sql))
            {
                for (int ii = 0 ; ii < values.length ; ii++)
                {
                    stmt.setObject(ii + 1, values[ii]);
                }
                return stmt.executeUpdate();
            }
        }
    }


    /**
     *  Iterates a <code>ResultSet</code> and converts it to a list-of-maps.
     */
    public static List<Map<String,Object>> processResultSet(ResultSet rslt)
    throws SQLException
    {
        List<Map<String,Object>> data = new ArrayList<Map<String,Object>>();
        ResultSetMetaData meta = rslt.getMetaData();

        while (rslt.next())
        {
            Map<String,Object> row = new HashMap<String,Object>();
            for (int ii = 1 ; ii <= meta.getColumnCount() ; ii++)
            {
                row.put(meta.getColumnName(ii), rslt.getObject(ii));
            }
            data.add(row);
        }

        return data;
    }
}

throwIfReadOnly.sql

DELIMITER EOF

CREATE FUNCTION throwIfReadOnly() RETURNS INTEGER
BEGIN
    IF @@innodb_read_only THEN
        SIGNAL SQLSTATE 'ERR0R' SET MESSAGE_TEXT = 'database is read_only';
    END IF;
    RETURN 0;
END;
EOF

DELIMITER ;