Created on 12-11-2016 10:04 AM
HDP Version - 2.4.2.0
Ambari - 2.2.2.0
Mysql Version - 5.1.34
.
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
.
NestedThrowablesStackTrace: 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.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:526) at com.mysql.jdbc.Util.handleNewInstance(Util.java:411) at com.mysql.jdbc.Util.getInstance(Util.java:386) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3597) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3529) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1990) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2151) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2619) at com.mysql.jdbc.ConnectionImpl.unsetMaxRows(ConnectionImpl.java:5421) at com.mysql.jdbc.StatementImpl.realClose(StatementImpl.java:2441) at com.mysql.jdbc.PreparedStatement.realClose(PreparedStatement.java:3079) at com.mysql.jdbc.PreparedStatement.close(PreparedStatement.java:1156) at com.jolbox.bonecp.StatementHandle.close(StatementHandle.java:138) at org.datanucleus.store.rdbms.ParamLoggingPreparedStatement.close(ParamLoggingPreparedStatement.java:318) at org.datanucleus.store.rdbms.SQLController.closeStatement(SQLController.java:568) at org.datanucleus.store.rdbms.query.SQLQuery.performExecute(SQLQuery.java:357) at org.datanucleus.store.query.Query.executeQuery(Query.java:1786) at org.datanucleus.store.query.AbstractSQLQuery.executeWithArray(AbstractSQLQuery.java:339) at org.datanucleus.api.jdo.JDOQuery.executeWithArray(JDOQuery.java:312) at org.apache.hadoop.hive.metastore.MetaStoreDirectSql.executeWithArray(MetaStoreDirectSql.java:1628) at org.apache.hadoop.hive.metastore.MetaStoreDirectSql.getPartitionsViaSqlFilterInternal(MetaStoreDirectSql.java:466) at org.apache.hadoop.hive.metastore.MetaStoreDirectSql.getPartitions(MetaStoreDirectSql.java:393) at org.apache.hadoop.hive.metastore.ObjectStore$2.getSqlResult(ObjectStore.java:1738) at org.apache.hadoop.hive.metastore.ObjectStore$2.getSqlResult(ObjectStore.java:1734) at org.apache.hadoop.hive.metastore.ObjectStore$GetHelper.run(ObjectStore.java:2394) at org.apache.hadoop.hive.metastore.ObjectStore.getPartitionsInternal(ObjectStore.java:1734) at org.apache.hadoop.hive.metastore.ObjectStore.getPartitions(ObjectStore.java:1728) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:606) at org.apache.hadoop.hive.metastore.RawStoreProxy.invoke(RawStoreProxy.java:114) at com.sun.proxy.$Proxy10.getPartitions(Unknown Source) at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.dropPartitionsAndGetLocations(HiveMetaStore.java:1700) at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.drop_table_core(HiveMetaStore.java:1539) at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.drop_table_with_environment_context(HiveMetaStore.java:1744) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:606) at org.apache.hadoop.hive.metastore.RetryingHMSHandler.invoke(RetryingHMSHandler.java:107) at com.sun.proxy.$Proxy11.drop_table_with_environment_context(Unknown Source) at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.drop_table_with_environment_context(HiveMetaStoreClient.java:2062) at org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient.drop_table_with_environment_context(SessionHiveMetaStoreClient.java:118)
.
1. MySql server version was patched(upgraded to 5.1.38) however mysql-java-connector was not upgraded.
2. Hive was configured to use Mysql as Metastore DB.
3. More details - http://bugs.mysql.com/bug.php?id=66659
.
After checking release notes on MySql community, found that this BUG has been fixed in mysql-java-connector-5.1.22
Please read more details here(Second last point) - https://dev.mysql.com/doc/relnotes/connector-j/5.1/en/news-5-1-22.html
To fix this - I had to upgrade my mysql-java-connector from mysql-java-connector-5.1.17 to mysql-java-connector-5.1.22.
Here is the download link for mysql-java-connector-5.1.22
.
mkdir ~/backups mv /var/lib/ambari-server/resources/mysql* ~/backups/ cp $some_location/mysql-connector-java-5.1.22.jar /var/lib/ambari-server/resources/ ln -s /var/lib/ambari-server/resources/mysql-connector-java-5.1.22.jar /var/lib/ambari-server/resources/mysql-jdbc-driver.jar mv /usr/share/java/mysql* ~/backups/ cp /var/lib/ambari-server/resources/mysql-connector-java-5.1.22.jar /usr/share/java/ ln -s /usr/share/java/mysql-connector-java-5.1.22.jar /usr/share/java/mysql-connector-java.jar #Please run below command carefully (Please Don't copy and paste, or copy it carefully!) rm -rf /var/lib/ambari-agent/tmp/mysql-*
.
On HiveMetastoremkdir ~/backupsmv /usr/share/java/mysql* ~/backups/ cp $local_path/mysql-connector-java-5.1.22.jar /usr/share/java/ ln -s /usr/share/java/mysql-connector-java-5.1.22.jar /usr/share/java/mysql-connector-java.jar #Please run below command carefully (Please Don't copy and paste, or copy it carefully!) rm -rf /var/lib/ambari-agent/tmp/mysql-*
.
On Hiveserver2mkdir ~/backupsmv /usr/share/java/mysql* ~/backups/ cp $local_path/mysql-connector-java-5.1.22.jar /usr/share/java/ ln -s /usr/share/java/mysql-connector-java-5.1.22.jar /usr/share/java/mysql-connector-java.jar #Please run below command carefully (Please Don't copy and paste, or copy it carefully!) rm -rf /var/lib/ambari-agent/tmp/mysql-*
.
.
Created on 11-01-2018 01:01 PM
Hi Kuldeep,
I updated the MySQL connector jar to mysql-connector-java-5.1.41-bin.jar.
HDP: HDP-2.6.5.0
MySQL: mysql Ver 14.14 Distrib 5.1.73
Performed the above steps and restarted ambari-server, ambari-agent, hiveserver2, and hive metastore components. However, I am still getting the same error in the logs.
jdbc:hive2://hdpmaster1-dev.<domain>.c> show databases; Error: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:Got exception: org.apache.hadoop.hive.metastore.api.MetaException javax.jdo.JDODataStoreException: 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 org.datanucleus.api.jdo.NucleusJDOHelper.getJDOExceptionForNucleusException(NucleusJDOHelper.java:543) at org.datanucleus.api.jdo.JDOQuery.executeInternal(JDOQuery.java:388) at org.datanucleus.api.jdo.JDOQuery.execute(JDOQuery.java:213) at org.apache.hadoop.hive.metastore.ObjectStore.getDatabases(ObjectStore.java:826) at org.apache.hadoop.hive.metastore.ObjectStore.getAllDatabases(ObjectStore.java:842) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.apache.hadoop.hive.metastore.RawStoreProxy.invoke(RawStoreProxy.java:103) at com.sun.proxy.$Proxy8.getAllDatabases(Unknown Source) at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.get_all_databases(HiveMetaStore.java:1270)