Aurora故障切换使连接处于只读状态
Aurora故障切换使连接处于只读状态
提问于 2018-04-10 16:29:52
我们在极光集群中使用MySQL，我们有两个实例--主实例和从实例。我们正在使用c3po连接池之上的spring事务。我们使用的是mariadb jdbc驱动程序(2.2.3版)。
我们的url看起来像这样- jdbc:mysql:aurora:myclaster-cluster.cluster-xxxxxx.us-east-1.rds.amazonaws.com:3306/db?rewriteBatchedStatements=true
在测试故障转移时；每隔几次故障转移，我们就会进入使用只读连接的状态-
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如何强制驱动程序将connections only返回到主实例？有没有办法强制aurora在故障转移时关闭所有打开的连接？
谢谢
回答 1
Stack Overflow用户
回答已采纳
发布于 2018-06-04 03:30:08
我们通过实现一个ExceptionInterceptor解决了这个问题，在这个a中我们关闭了连接，强制池创建一个新的连接。
此解决方法与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;
}页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/49748908
复制相关文章
相似问题