Handle Read-Only Mode After Cluster Failover

[rreich]

A AWS Aurora MySQL Cluster Failover might leave connections open to the old writer instance which is now a reader instance. (This scenario also applies to other databases and cluster solutions.)

The problem is that these connections are not closed / evicted from the pool. While it is possible to configure a connectionTestQuery like select 1 from xxx where 1=2 for update as suggested in https://github.com/brettwooldridge/HikariCP/issues/1802#issuecomment-876064227, this is not enough. A heavily used connection might not even get checked for quite some time without setting the system property com.zaxxer.hikari.aliveBypassWindowMs to 0.

Anyway, these workarounds are not optimal as they create some overhead and might have an impact on performance.

It would be much better if there was a way to handle the Exceptions when they occur and close and evict the connection right away.

For the AWS Aurora MySQL Cluster the exceptions look like this: class: java.sql.SQLException message: "The MySQL server is running with the --read-only option so it cannot execute this statement" errorCode: 1290 SQLState: "HY000"

See https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html#error_er_option_prevents_statement

Some possible approaches might be:

• In ProxyConnection#checkException(SQLException), include this errorCode and SQLState or maybe the combination of both in the check. If there are any scenarios where this behavior is not wanted, maybe guard this with an extra configuration flag like mysqlClusterMode?
• Create an extension point to mark connections throwing such a SQLException as to be evicted. This could probably be an extension of the existing SQLExceptionOverride feature. I created a separate issue for that, as I think it is a reasonable feature on its own that might also be useful in other situations: https://github.com/brettwooldridge/HikariCP/issues/2184

There already are some related issues, but I found them to be lacking some detail: https://github.com/brettwooldridge/HikariCP/issues/1971 https://github.com/brettwooldridge/HikariCP/issues/1802

[john9x]

+1 I need weird crutches to achieve this now

class MysqlReadOnlyInterceptor implements ExceptionInterceptor {
    @Override
    public Exception interceptException(Exception ex) {
        if (ex instanceof SQLException sqlEx) {
            if (sqlEx.getErrorCode() == MysqlErrorNumbers.ER_OPTION_PREVENTS_STATEMENT
                    && sqlEx.getMessage().contains("read-only")) {

                dataSources.stream()
                        .map(HikariDataSource::getHikariPoolMXBean)
                        .filter(Objects::nonNull)
                        .forEach(pool -> {
                            log.warn("Evict connections due to read only {}", pool);
                            pool.softEvictConnections();
                        });
            }
        }
        return ex;
    }
}