Skip to content

Instantly share code, notes, and snippets.

@kdgregory
Last active September 11, 2019 00:20
Show Gist options
  • Save kdgregory/fb06e29feedb23b54be208327d82668f to your computer and use it in GitHub Desktop.
Save kdgregory/fb06e29feedb23b54be208327d82668f to your computer and use it in GitHub Desktop.
Example program that uses a database connection checkout test to avoid using a read-only connection after Aurora failover.
// 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);
}
}
}
}
}
// 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);
}
}
}
// 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;
}
}
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 ;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment