记录一个因 MYSQL 服务端和 JDBC 驱动版本不一致导致 HMS 启动失败问题
1. 问题现象
某运维同学安装 CDH 后 HIVE 服务报警，查看发现 HS2 的金丝雀检查失败，进一步查看发现，HS2 无法创建默认数据库，且 HMS 启动失败。遂找到笔者进行排查。
2 问题原因
按照老套路，直接通过 CM WEB UI 查看 HMS 日志, 可以清晰发现，HMS 启动后尝试创建默认数据库（即 hive 的 default database)时，有报错：
RetryingHMSHandler [pool-5-thread-194]: Retrying HMSHandler after 2000 ms (attempt 4 of 10) with error: javax.jdo.JDODataStoreException: You have an error in your SQL syntax; check the manual tha t corresponds to your MySQL server version for the right syntax to use near 'OPTION SQL_SELECT LIMIT=DEFAULT' at line 1
复制代码
该问题直接指向了 SQL 语法错误，其底层原因其实是，HMS 底层的 MYSQL 数据库和 HMS 使用的 MYSQL JDBC 驱动版本不匹配，比如在上述环境中：
mysql 数据库版本: mysql-5.6.36-linux-glibc2.5-x86_64.tar.gz；
mysql 驱动版本为：mysql-connector-java-5.1.43-bin.jar；
由于 5.6 之前的老版本的 MYSQL JDBC 驱动类 com.mysql.jdbc.Statement 中有显示调用代码 "SET OPTION SQL_SELECT_LIMIT=DEFAULT"，而 MYSQL 服务端自 5.6 版本之后更改了 SET 配置参数的调用语法，不再支持 "SET OPTION SQL_SELECT_LIMIT=DEFAULT"，而只支持 "SET SQL_SELECT_LIMIT=DEFAULT"，所以当使用 5.6 之后的 MYSQL 服务端，而对应的 MYSQL JDBC 驱动是 5.6 之前的版本，就会报上述错误。
3 问题解决
删除老版本（5.6 之前） MYSQL JDBC 驱动，并替换使用新版本的 MYSQL JDBC 驱动（5.6 之后，比如 8.0)，重启 HIVE 服务，即可解决问题。
4 参考链接与详细日志
# 参考链接：https://bugs.mysql.com/bug.php?id=66659# 详细日志：RetryingHMSHandler [pool-5-thread-194]: Retrying HMSHandler after 2000 ms (attempt 4 of 10) with error: javax.jdo.JDODataStoreException: You have an error in your SQL syntax; check the manual tha t corresponds to your MySQL server version for the right syntax to use near 'OPTION SQL_SELECT LIMIT=DEFAULT' at line 1at org.datanucleus.api.jdo. NucleusJDOHelper.getJDOExceptionForNucleusException(NucleusJDOHelper.java:451)at org.datanucleus.api.jdo.JDOQuery.execute(JDOQuery.java:275)at org.apache.hadoop.hive.metastore.ObjectStore.getMTable(ObjectStore.java:1208)at org.apache.hadoop.hive.metastore.ObjectStore.getTable(ObjectStore.java:1023)at sun.reflect.GeneratedMethodAccessor9.invoke(Unknown Source)at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.at java.lang.reflect.Method.invoke(Method.java:498)at org.apache.hadoop.hive.metastore. RawStoreProxy.invoke(RawStoreProxy.java:103) at com.sun.proxy.$Proxy7.getTable(Unknown Source)at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.get_table_core (Hive MetaStore.java:1950)at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.get_table(HiveMetaS tore.java:1905)at sun.reflect.GeneratedMethodAccessor14.invoke(Unknown Source)at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)at java.lang.reflect.Method.invoke(Method.java:498)at org.apache.hadoop.hive.metastore.RetryingHMSHandler.invokeInternal (RetryingHM SHandler.java:140)at org.apache.hadoop.hive.metastore.RetryingHMSHandler.invoke(RetryingHMSHandler.java:99) at com.sun.proxy.$Proxy9.get_table(Unknown Source)at org.apache.hadoop.hive.metastore.api. ThriftHiveMetastore$Processor$get_table.getResult(ThriftHiveMetastore.java:10128)at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Processor$get_table.getResult(ThriftHiveMetastore.java:10112)at org.apache.thrift. ProcessFunction.process(ProcessFunction.java:39).......NestedThrowablesStackTrace:va:286)：com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OPTION SQL_SELECT_LIMIT=DEFAULT' at line 1 at sun.reflect.GeneratedConstructorAccessor29.newInstance (Unknown Source)at sun.reflect.DelegatingConstructorAccessorImpl.newInstance (DelegatingConstructorAccessorImpl.java:45)at java.lang.reflect.Constructor.newInstance (Constructor.java:423)at com.mysql.jdbc.Util.handleNewInstance (Util.java:406)at com.mysql.jdbc.Util.getInstance (Util.java:381)at com.mysql.jdbc.SQLError.createSQLException (SQLError.java:1031) at com.mysql.jdbc.SQLError.createSQLException (SQLError.java:957) at com.mysql.jdbc.MysqlIO.checkErrorPacket (MysqlIO.java:3376) at com.mysql.jdbc.MysqlIO.checkErrorPacket (MysqlIO.java:3308) at com.mysql.jdbc.MysqlIO.sendCommand (MysqlIO.java:1837)at com.mysql.jdbc.MysqlIO.sqlQueryDirect (MysqlIO.java:1961)at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2537)at com.mysql.jdbc.StatementImpl.executeSimpleNonQuery (StatementImpl.java:1463)at com.mysql.jdbc.StatementImpl.executeQuery (StatementImpl.java:1365)at com.mysql.jdbc.ConnectionImpl.getTransactionIsolation (ConnectionImpl.java:3114)at com.jolbox.bonecp.ConnectionHandle.getTransactionIsolation (ConnectionHandle.java:825)at org.datanucleus.store.rdbms.ConnectionFactoryImpl$ManagedConnectionImpl.getConnection/Conn ava:444)at org.datanucleus.store.rdbms.ConnectionFactoryImpl$Managed ConnectionImpl.getXAResolva: 378)at org.datanucleus.store.connection. ConnectionManagerImpl.allocateConnection (ConnectionManagerImpl.java:328)at org.datanucleus.store.connection. AbstractConnectionFactory.getConnection (AbstractConnectionFactory.java:94)at org.datanucleus.store.AbstractStoreManager.getConnection (AbstractStoreManager.java:430) at org.datanucleus.store.AbstractStoreManager.getConnection (AbstractStoreManager.java:396) at org.datanucleus.store.rdbms.query. JDOQLQuery.performExecute (JDOQLQuery.java:621)at org.datanucleus.store.query.Query.executeQuery (Query.java:1786)at org.datanucleus.store.query.Query.executeWithArray (Query.java:1672)at org.datanucleus.api.jdo. JDOQuery.execute(JDOQuery.java:266)at org.apache.hadoop.hive.metastore.ObjectStore.getMTable (ObjectStore.java:1208)at org.apache.hadoop.hive.metastore.ObjectStore.getTable(ObjectStore.java:1023)at sun.reflect.GeneratedMethodAccessor9. invoke (Unknown Source)at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
复制代码
评论