MySQL升级至5.7.35问题处理过程

转载

mb5fd86a050ef28 2021-08-20 13:31:00

文章标签 java mysql sql jar hive 文章分类 代码人生

报错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

[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                                        |