CODEWITHSUNDEEP
mariadbc3p0amazon-aurora
Adi Baron
Adi Baron

Reputation: 576

Aurora failover leaves connections open as read-only state

We are using MySQL in Aurora cluster We have 2 instances - master and slave. We are working with spring transactions on top of a c3po connection pool. We are using mariadb jdbc driver (version 2.2.3).

Our url looks like this - jdbc:mysql:aurora:myclaster-cluster.cluster-xxxxxx.us-east-1.rds.amazonaws.com:3306/db?rewriteBatchedStatements=true

When testing failover; every few failovers we get into a state of using a read only connection -

Caused by: org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [INSERT INTO a (a1, a2, a3, a4) VALUES (?, ?, ?, ?) on duplicate key update ]; SQL state [HY000]; error code [1290]; (conn=7) The MySQL server is running with the --read-only option so it cannot execute this statement; nested exception is java.sql.SQLException: (conn=7) The MySQL server is running with the --read-only option so it cannot execute this statement
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:84)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:645)
    at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:866)
    at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:927)
    at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:937)
    at com.persistence.impl.MyDao.insert(MyDao.java:52)
    at org.springframework.scheduling.quartz.QuartzJobBean.execute(QuartzJobBean.java:75)
    at org.quartz.core.JobRunShell.run(JobRunShell.java:213)
    ... 1 common frames omitted
Caused by: java.sql.SQLException: (conn=7) The MySQL server is running with the --read-only option so it cannot execute this statement
    at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.get(ExceptionMapper.java:198)
    at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.getException(ExceptionMapper.java:110)
    at org.mariadb.jdbc.MariaDbStatement.executeExceptionEpilogue(MariaDbStatement.java:228)
    at org.mariadb.jdbc.MariaDbPreparedStatementClient.executeInternal(MariaDbPreparedStatementClient.java:216)
    at org.mariadb.jdbc.MariaDbPreparedStatementClient.execute(MariaDbPreparedStatementClient.java:150)
    at org.mariadb.jdbc.MariaDbPreparedStatementClient.executeUpdate(MariaDbPreparedStatementClient.java:183)
    at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:410)
    at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:873)
    at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:866)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:629)
    ... 9 common frames omitted

How can we force the driver to return connections only to the master instance? Is there a way to force aurora to close all open connection upon failover?

Thanks

Upvotes: 3

Views: 3723

Answers (1)

Adi Baron
Adi Baron

Reputation: 576

We solved the problem by implementing an ExceptionInterceptor, in which we closed the connection, forcing the pool to create a new one.

This workround is relevant for mysql-connector-java 5.1.47

@Override
public SQLException interceptException(SQLException sqlEx, Connection conn) {
    if (sqlEx.getErrorCode() == READ_ONLY_ERROR_CODE) {
        log.warn("Got read only exception closing the connection {} ", sqlEx.getMessage());
        closeConnection(conn);
    }
    return sqlEx;
}

Upvotes: 1

Related Questions