mysql - MySQL 服务器正在使用 --super-read-only 选项运行，因此它无法执行此语句

Question

我在 kubernetes 集群 v1.16 中使用 MySQL 单主多从集群 5.7。我使用 helm chart 安装集群，如下所示：

./helm install report incubator/mysqlha -n middleware

所以我在我的应用程序中配置连接链接，如下所示：

jdbc:mysql://report-mysqlha.middleware.svc.cluster.local:3306/hub_pro?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&useSSL=false&verifyServerCertificate=false&allowMultiQueries=true

当我启动我的应用程序时，显示此错误日志：

org.springframework.jdbc.UncategorizedSQLException: 
### Error updating database.  Cause: java.sql.SQLException: The MySQL server is running with the --super-read-only option so it cannot execute this statement
### The error may exist in class path resource [mybatis/mapper/illidan/TenantOverviewMapper.xml]
### The error may involve com.sportswin.soa.illidan.hub.dao.TenantOverviewMapper.updateByPrimaryKeySelective-Inline
### The error occurred while setting parameters
### SQL: update h_tenant_overview      SET tenant_name = ?,                       start_access_time = ?,                       create_account = ?,                       user_spent = ?,                       tenant_revenue = ?,                       agent_take_ratio = ?,                       agent_revenue = ?,                       tenant_code = ?,                       reg_count = ?,                       tenant_id = ?,                       statistic_time = ?,                              updated_time = ?      where id = ?
### Cause: java.sql.SQLException: The MySQL server is running with the --super-read-only option so it cannot execute this statement
; uncategorized SQLException; SQL state [HY000]; error code [1290]; The MySQL server is running with the --super-read-only option so it cannot execute this statement; nested exception is java.sql.SQLException: The MySQL server is running with the --super-read-only option so it cannot execute this statement
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:89) ~[spring-jdbc-5.1.13.RELEASE.jar!/:5.1.13.RELEASE]
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-5.1.13.RELEASE.jar!/:5.1.13.RELEASE]
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-5.1.13.RELEASE.jar!/:5.1.13.RELEASE]
    at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:74) ~[mybatis-spring-2.0.2.jar!/:2.0.2]
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:440) ~[mybatis-spring-2.0.2.jar!/:2.0.2]
    at com.sun.proxy.$Proxy145.update(Unknown Source) ~[na:na]
    at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:287) ~[mybatis-spring-2.0.2.jar!/:2.0.2]
    at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:67) ~[mybatis-3.5.2.jar!/:3.5.2]
    at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:57) ~[mybatis-3.5.2.jar!/:3.5.2]
    at com.sun.proxy.$Proxy147.updateByPrimaryKeySelective(Unknown Source) ~[na:na]
    at com.sportswin.soa.illidan.service.impl.HomeService.insertOrUpdate(HomeService.java:84) ~[classes!/:na]
    at com.sportswin.soa.illidan.service.impl.HomeService$$FastClassBySpringCGLIB$$51f41045.invoke(<generated>) ~[classes!/:na]
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218) ~[spring-core-5.1.13.RELEASE.jar!/:5.1.13.RELEASE]
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:750) ~[spring-aop-5.1.13.RELEASE.jar!/:5.1.13.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-5.1.13.RELEASE.jar!/:5.1.13.RELEASE]
    at com.alibaba.druid.support.spring.stat.DruidStatInterceptor.invoke(DruidStatInterceptor.java:72) ~[druid-1.1.9.jar!/:1.1.9]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.1.13.RELEASE.jar!/:5.1.13.RELEASE]
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:689) ~[spring-aop-5.1.13.RELEASE.jar!/:5.1.13.RELEASE]
    at com.sportswin.soa.illidan.service.impl.HomeService$$EnhancerBySpringCGLIB$$777629ab.insertOrUpdate(<generated>) ~[classes!/:na]
    at com.sportswin.soa.illidan.service.impl.HomeService$$FastClassBySpringCGLIB$$51f41045.invoke(<generated>) ~[classes!/:na]
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218) ~[spring-core-5.1.13.RELEASE.jar!/:5.1.13.RELEASE]
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:750) ~[spring-aop-5.1.13.RELEASE.jar!/:5.1.13.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-5.1.13.RELEASE.jar!/:5.1.13.RELEASE]
    at com.alibaba.druid.support.spring.stat.DruidStatInterceptor.invoke(DruidStatInterceptor.java:72) ~[druid-1.1.9.jar!/:1.1.9]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.1.13.RELEASE.jar!/:5.1.13.RELEASE]
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:689) ~[spring-aop-5.1.13.RELEASE.jar!/:5.1.13.RELEASE]
    at com.sportswin.soa.illidan.service.impl.HomeService$$EnhancerBySpringCGLIB$$624d3217.insertOrUpdate(<generated>) ~[classes!/:na]
    at com.sportswin.soa.illidan.hub.controller.impl.analysis.TenantOverviewController.generateTenantOverviewInfo(TenantOverviewController.java:113) ~[classes!/:na]
    at com.sportswin.soa.illidan.hub.controller.impl.analysis.TenantOverviewController.statistic(TenantOverviewController.java:80) ~[classes!/:na]
    at com.sportswin.soa.illidan.hub.controller.impl.analysis.TenantOverviewController.g(TenantOverviewController.java:62) ~[classes!/:na]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_212]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_212]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_212]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_212]
    at org.springframework.scheduling.support.ScheduledMethodRunnable.run(ScheduledMethodRunnable.java:84) [spring-context-5.1.13.RELEASE.jar!/:5.1.13.RELEASE]
    at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54) [spring-context-5.1.13.RELEASE.jar!/:5.1.13.RELEASE]
    at org.springframework.scheduling.concurrent.ReschedulingRunnable.run(ReschedulingRunnable.java:93) [spring-context-5.1.13.RELEASE.jar!/:5.1.13.RELEASE]
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) [na:1.8.0_212]
    at java.util.concurrent.FutureTask.run(FutureTask.java:266) [na:1.8.0_212]
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180) [na:1.8.0_212]
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293) [na:1.8.0_212]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [na:1.8.0_212]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [na:1.8.0_212]
    at java.lang.Thread.run(Thread.java:748) [na:1.8.0_212]
Caused by: java.sql.SQLException: The MySQL server is running with the --super-read-only option so it cannot execute this statement
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129) ~[mysql-connector-java-8.0.19.jar!/:8.0.19]
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[mysql-connector-java-8.0.19.jar!/:8.0.19]
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-java-8.0.19.jar!/:8.0.19]
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953) ~[mysql-connector-java-8.0.19.jar!/:8.0.19]
    at com.mysql.cj.jdbc.ClientPreparedStatement.execute$original$z6ncbhXt(ClientPreparedStatement.java:370) ~[mysql-connector-java-8.0.19.jar!/:8.0.19]
    at com.mysql.cj.jdbc.ClientPreparedStatement.execute$original$z6ncbhXt$accessor$r1XOtRdH(ClientPreparedStatement.java) ~[mysql-connector-java-8.0.19.jar!/:8.0.19]
    at com.mysql.cj.jdbc.ClientPreparedStatement$auxiliary$6xe4F6qJ.call(Unknown Source) ~[mysql-connector-java-8.0.19.jar!/:8.0.19]
    at org.apache.skywalking.apm.agent.core.plugin.interceptor.enhance.InstMethodsInter.intercept(InstMethodsInter.java:93) ~[skywalking-agent.jar:6.5.0]
    at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java) ~[mysql-connector-java-8.0.19.jar!/:8.0.19]
    at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3051) ~[druid-1.1.9.jar!/:1.1.9]
    at com.alibaba.druid.filter.FilterAdapter.preparedStatement_execute(FilterAdapter.java:1080) ~[druid-1.1.9.jar!/:1.1.9]
    at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3049) ~[druid-1.1.9.jar!/:1.1.9]
    at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440) ~[druid-1.1.9.jar!/:1.1.9]
    at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3049) ~[druid-1.1.9.jar!/:1.1.9]
    at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:167) ~[druid-1.1.9.jar!/:1.1.9]
    at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:498) ~[druid-1.1.9.jar!/:1.1.9]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_212]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_212]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_212]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_212]
    at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59) ~[mybatis-3.5.2.jar!/:3.5.2]
    at com.sun.proxy.$Proxy232.execute(Unknown Source) ~[na:na]
    at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:47) ~[mybatis-3.5.2.jar!/:3.5.2]
    at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74) ~[mybatis-3.5.2.jar!/:3.5.2]
    at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50) ~[mybatis-3.5.2.jar!/:3.5.2]
    at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117) ~[mybatis-3.5.2.jar!/:3.5.2]
    at com.sportswin.soa.misc.interceptor.db.DefaultTimeInterceptor.invokeUpdate(DefaultTimeInterceptor.java:97) ~[soa-misc-1.0.0-SNAPSHOT.jar!/:na]
    at com.sportswin.soa.misc.interceptor.db.DefaultTimeInterceptor.intercept(DefaultTimeInterceptor.java:59) ~[soa-misc-1.0.0-SNAPSHOT.jar!/:na]
    at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61) ~[mybatis-3.5.2.jar!/:3.5.2]
    at com.sun.proxy.$Proxy230.update(Unknown Source) ~[na:na]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_212]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_212]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_212]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_212]
    at org.apache.ibatis.plugin.Invocation.proceed(Invocation.java:49) ~[mybatis-3.5.2.jar!/:3.5.2]
    at com.sportswin.soa.misc.interceptor.db.DynamicDataSourceInterceptor.intercept(DynamicDataSourceInterceptor.java:98) ~[soa-misc-1.0.0-SNAPSHOT.jar!/:na]
    at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61) ~[mybatis-3.5.2.jar!/:3.5.2]
    at com.sun.proxy.$Proxy230.update(Unknown Source) ~[na:na]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_212]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_212]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_212]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_212]
    at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63) ~[mybatis-3.5.2.jar!/:3.5.2]
    at com.sun.proxy.$Proxy230.update(Unknown Source) ~[na:na]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_212]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_212]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_212]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_212]
    at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63) ~[mybatis-3.5.2.jar!/:3.5.2]
    at com.sun.proxy.$Proxy230.update(Unknown Source) ~[na:na]
    at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:197) ~[mybatis-3.5.2.jar!/:3.5.2]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_212]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_212]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_212]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_212]
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:426) ~[mybatis-spring-2.0.2.jar!/:2.0.2]
    ... 39 common frames omitted

一会儿这个错误消失了，我执行更新语句期间。我检查了 pod 并没有重启 MySQL 节点。什么情况会导致这个问题？

标签： mysql

Answer 1

现在我强制应用使用主节点来避免这个问题：

spring.datasource.druid.illidan.master.url = jdbc:mysql://report-mysqlha-0.middleware.svc.cluster.local:3306/report_pro?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&useSSL=false&verifyServerCertificate=false

report-mysqlha-0是kubernetes集群中MySQL的主节点服务名。