MySQL升级至5.7.35问题处理过程
报错1： could not retrieve transation read-only status server
2021-08-19T15:26:46,042 INFO [HiveServer2-Handler-Pool: Thread-110] metastore.ObjectStore: Retriable exception while instantiating ObjectStore, retrying. 9 tries left
javax.jdo.JDODataStoreException: Could not retrieve transaction read-only status from server
at org.datanucleus.api.jdo.NucleusJDOHelper.getJDOExceptionForNucleusException(NucleusJDOHelper.java:543) ~[datanucleus-api-jdo-4.2.1.jar:?]
at org.datanucleus.api.jdo.JDOPersistenceManager.getDataStoreConnection(JDOPersistenceManager.java:2275) ~[datanucleus-api-jdo-4.2.1.jar:?]
at org.apache.hadoop.hive.metastore.MetaStoreDirectSql.getProductName(MetaStoreDirectSql.java:210) ~[hive-exec-2.1.0.2.6.3.0-235.jar:2.1.0.2.6.3.0-235]
...
at java.lang.Thread.run(Thread.java:748) [?:1.8.0_181]
Caused by: java.sql.SQLException: Could not retrieve transaction read-only status from server
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965) ~[mysql-connector-java.jar:5.1.45]
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898) ~[mysql-connector-java.jar:5.1.45]
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887) ~[mysql-connector-java.jar:5.1.45]
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861) ~[mysql-connector-java.jar:5.1.45]
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:878) ~[mysql-connector-java.jar:5.1.45]
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:874) ~[mysql-connector-java.jar:5.1.45]
at com.mysql.jdbc.ConnectionImpl.isReadOnly(ConnectionImpl.java:3552) ~[mysql-connector-java.jar:5.1.45]
at com.mysql.jdbc.ConnectionImpl.isReadOnly(ConnectionImpl.java:3521) ~[mysql-connector-java.jar:5.1.45]
at com.jolbox.bonecp.ConnectionHandle.isReadOnly(ConnectionHandle.java:867) ~[bonecp-0.8.0.RELEASE.jar:?]
at org.datanucleus.store.rdbms.ConnectionFactoryImpl$ManagedConnectionImpl.getConnection(ConnectionFactoryImpl.java:406) ~[datanucleus-rdbms-4.1.7.jar:?]
at org.datanucleus.store.rdbms.RDBMSStoreManager.getNucleusConnection(RDBMSStoreManager.java:1357) ~[datanucleus-rdbms-4.1.7.jar:?]
at org.datanucleus.api.jdo.JDOPersistenceManager.getDataStoreConnection(JDOPersistenceManager.java:2263) ~[datanucleus-api-jdo-4.2.1.jar:?]
解决
升级mysql-connector版本：由mysql-connector-java-5.1.45-bin.jar 升级为 mysql-connector-java-8.0.25.jar
大量 [Note] Aborted connection ... 日志
新版本mysql ，增加变量：log_error_verbosity，默认为3.
1:Error messages
2:Error and warning messages
3:Error, warning, and note messages
参考： https://www.cnblogs.com/kerrycode/p/8973285.html
[root@wsjylog02 logs]# tail mysqld.log
2021-08-20T08:48:51.374193Z 23809 [Note] Aborted connection 23809 to db: 'airflow' user: 'airflow' host: 'localhost' (Got an error reading communication packets)
2021-08-20T08:48:51.375652Z 23808 [Note] Aborted connection 23808 to db: 'airflow' user: 'airflow' host: 'localhost' (Got an error reading communication packets)
2021-08-20T08:48:52.378243Z 23810 [Note] Aborted connection 23810 to db: 'airflow' user: 'airflow' host: 'localhost' (Got an error reading communication packets)
2021-08-20T08:48:52.408406Z 23811 [Note] Aborted connection 23811 to db: 'airflow' user: 'airflow' host: 'localhost' (Got an error reading communication packets)
2021-08-20T08:48:53.411960Z 23813 [Note] Aborted connection 23813 to db: 'airflow' user: 'airflow' host: 'localhost' (Got an error reading communication packets)
2021-08-20T08:48:53.442959Z 23812 [Note] Aborted connection 23812 to db: 'airflow' user: 'airflow' host: 'localhost' (Got an error reading communication packets)
2021-08-20T08:48:54.481261Z 23814 [Note] Aborted connection 23814 to db: 'airflow' user: 'airflow' host: 'localhost' (Got an error reading communication packets)
2021-08-20T08:48:54.488554Z 23815 [Note] Aborted connection 23815 to db: 'airflow' user: 'airflow' host: 'localhost' (Got an error reading communication packets)
2021-08-20T08:48:55.476873Z 23816 [Note] Aborted connection 23816 to db: 'airflow' user: 'airflow' host: 'localhost' (Got an error reading communication packets)
2021-08-20T08:48:55.518244Z 23817 [Note] Aborted connection 23817 to db: 'airflow' user: 'airflow' host: 'localhost' (Got an error reading communication packets)
mysql> show variables like 'log%';
+----------------------------------------+------------------------------------------+
| Variable_name | Value |
+----------------------------------------+------------------------------------------+
| log_bin | ON |
| log_bin_basename | /usr/local/mysql/logs/mysql-binlog |
| log_bin_index | /usr/local/mysql/logs/mysql-binlog.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| log_builtin_as_identified_by_password | OFF |
| log_error | /usr/local/mysql/logs/mysqld.log |
| log_error_verbosity | 3 |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| log_statements_unsafe_for_binlog | ON |
| log_syslog | OFF |
| log_syslog_facility | daemon |
| log_syslog_include_pid | ON |
| log_syslog_tag | |
| log_throttle_queries_not_using_indexes | 0 |
| log_timestamps | UTC |
| log_warnings | 2 |
解决
set global log_error_verbosity=2;
