Cloudera Community

Community Articles

Find and share helpful community-sourced technical articles.
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Advanced Search

Caused by: MetaException(message: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

Labels (1)
Labels:
avatar
author-rank srai1
Guru

Created on ‎04-27-2018 02:07 AM

Problem

While using LLAP, (can happen anytime, even though it was working a second ago), you might see a MetaException complaining about incorrect MySQL server syntax. Here is the the error output with 10 retries by default, so this could get lengthy.

0: jdbc:hive2://xlautomation-1.h.c:10500/defa> analyze table L3_MONTHLY_dw_dimRisk compute statistics for columns;
Getting log thread is interrupted, since query is done!
Error: Error while compiling statement: FAILED: RuntimeException Unable to instantiate org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient (state=42000,code=40000)
org.apache.hive.service.cli.HiveSQLException: Error while compiling statement: FAILED: RuntimeException Unable to instantiate org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient
	at org.apache.hive.jdbc.Utils.verifySuccess(Utils.java:277)
	at org.apache.hive.jdbc.Utils.verifySuccessWithInfo(Utils.java:263)
	at org.apache.hive.jdbc.HiveStatement.runAsyncOnServer(HiveStatement.java:303)
	at org.apache.hive.jdbc.HiveStatement.execute(HiveStatement.java:244)
	at org.apache.hive.beeline.Commands.execute(Commands.java:871)
	at org.apache.hive.beeline.Commands.sql(Commands.java:729)
	at org.apache.hive.beeline.BeeLine.dispatch(BeeLine.java:1000)
	at org.apache.hive.beeline.BeeLine.execute(BeeLine.java:835)
	at org.apache.hive.beeline.BeeLine.begin(BeeLine.java:793)
	at org.apache.hive.beeline.BeeLine.mainWithInputRedirection(BeeLine.java:493)
	at org.apache.hive.beeline.BeeLine.main(BeeLine.java:476)
	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.util.RunJar.run(RunJar.java:233)
	at org.apache.hadoop.util.RunJar.main(RunJar.java:148)
Caused by: org.apache.hive.service.cli.HiveSQLException: Error while compiling statement: FAILED: RuntimeException Unable to instantiate org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient
	at org.apache.hive.service.cli.operation.Operation.toSQLException(Operation.java:376)
	at org.apache.hive.service.cli.operation.SQLOperation.prepare(SQLOperation.java:193)
	at org.apache.hive.service.cli.operation.SQLOperation.runInternal(SQLOperation.java:278)
	at org.apache.hive.service.cli.operation.Operation.run(Operation.java:312)
	at org.apache.hive.service.cli.session.HiveSessionImpl.executeStatementInternal(HiveSessionImpl.java:517)
	at org.apache.hive.service.cli.session.HiveSessionImpl.executeStatementAsync(HiveSessionImpl.java:504)
	at org.apache.hive.service.cli.CLIService.executeStatementAsync(CLIService.java:310)
	at org.apache.hive.service.cli.thrift.ThriftCLIService.ExecuteStatement(ThriftCLIService.java:509)
	at org.apache.hive.service.rpc.thrift.TCLIService$Processor$ExecuteStatement.getResult(TCLIService.java:1497)
	at org.apache.hive.service.rpc.thrift.TCLIService$Processor$ExecuteStatement.getResult(TCLIService.java:1482)
	at org.apache.thrift.ProcessFunction.process(ProcessFunction.java:39)
	at org.apache.thrift.TBaseProcessor.process(TBaseProcessor.java:39)
	at org.apache.hive.service.auth.TSetIpAddressProcessor.process(TSetIpAddressProcessor.java:56)
	at org.apache.thrift.server.TThreadPoolServer$WorkerProcess.run(TThreadPoolServer.java:286)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
	at java.lang.Thread.run(Thread.java:745)
Caused by: java.lang.RuntimeException: Unable to instantiate org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient
	at org.apache.hadoop.hive.metastore.MetaStoreUtils.newInstance(MetaStoreUtils.java:1667)
	at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.<init>(RetryingMetaStoreClient.java:83)
	at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.getProxy(RetryingMetaStoreClient.java:133)
	at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.getProxy(RetryingMetaStoreClient.java:104)
	at org.apache.hadoop.hive.ql.metadata.Hive.createMetaStoreClient(Hive.java:3627)
	at org.apache.hadoop.hive.ql.metadata.Hive.getMSC(Hive.java:3679)
	at org.apache.hadoop.hive.ql.metadata.Hive.getMSC(Hive.java:3659)
	at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:465)
	at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:358)
	at org.apache.hadoop.hive.ql.Driver.compileInternal(Driver.java:1300)
	at org.apache.hadoop.hive.ql.Driver.compileAndRespond(Driver.java:1272)
	at org.apache.hive.service.cli.operation.SQLOperation.prepare(SQLOperation.java:191)
	... 15 more
Caused by: java.lang.RuntimeException: java.lang.reflect.InvocationTargetException:null
	at sun.reflect.GeneratedConstructorAccessor105.newInstance(Unknown Source)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at org.apache.hadoop.hive.metastore.MetaStoreUtils.newInstance(MetaStoreUtils.java:1665)
	... 26 more
Caused by: MetaException(message: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.getObjectCount(ObjectStore.java:1294)
	at org.apache.hadoop.hive.metastore.ObjectStore.getPartitionCount(ObjectStore.java:1277)
	at sun.reflect.GeneratedMethodAccessor20.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.RawStoreProxy.invoke(RawStoreProxy.java:101)
	at com.sun.proxy.$Proxy28.getPartitionCount(Unknown Source)
	at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.updateMetrics(HiveMetaStore.java:6960)
	at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.init(HiveMetaStore.java:451)
	at sun.reflect.GeneratedMethodAccessor17.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(RetryingHMSHandler.java:148)
	at org.apache.hadoop.hive.metastore.RetryingHMSHandler.invoke(RetryingHMSHandler.java:107)
	at org.apache.hadoop.hive.metastore.RetryingHMSHandler.<init>(RetryingHMSHandler.java:79)
	at org.apache.hadoop.hive.metastore.RetryingHMSHandler.getProxy(RetryingHMSHandler.java:92)
	at org.apache.hadoop.hive.metastore.HiveMetaStore.newRetryingHMSHandler(HiveMetaStore.java:7034)
	at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.<init>(HiveMetaStoreClient.java:140)
	at org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient.<init>(SessionHiveMetaStoreClient.java:74)
	at sun.reflect.GeneratedConstructorAccessor105.newInstance(Unknown Source)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at org.apache.hadoop.hive.metastore.MetaStoreUtils.newInstance(MetaStoreUtils.java:1665)
	at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.<init>(RetryingMetaStoreClient.java:83)
	at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.getProxy(RetryingMetaStoreClient.java:133)
	at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.getProxy(RetryingMetaStoreClient.java:104)
	at org.apache.hadoop.hive.ql.metadata.Hive.createMetaStoreClient(Hive.java:3627)
	at org.apache.hadoop.hive.ql.metadata.Hive.getMSC(Hive.java:3679)
	at org.apache.hadoop.hive.ql.metadata.Hive.getMSC(Hive.java:3659)
	at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:465)
	at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:358)
	at org.apache.hadoop.hive.ql.Driver.compileInternal(Driver.java:1300)
	at org.apache.hadoop.hive.ql.Driver.compileAndRespond(Driver.java:1272)
	at org.apache.hive.service.cli.operation.SQLOperation.prepare(SQLOperation.java:191)
	at org.apache.hive.service.cli.operation.SQLOperation.runInternal(SQLOperation.java:278)
	at org.apache.hive.service.cli.operation.Operation.run(Operation.java:312)
	at org.apache.hive.service.cli.session.HiveSessionImpl.executeStatementInternal(HiveSessionImpl.java:517)
	at org.apache.hive.service.cli.session.HiveSessionImpl.executeStatementAsync(HiveSessionImpl.java:504)
	at org.apache.hive.service.cli.CLIService.executeStatementAsync(CLIService.java:310)
	at org.apache.hive.service.cli.thrift.ThriftCLIService.ExecuteStatement(ThriftCLIService.java:509)
	at org.apache.hive.service.rpc.thrift.TCLIService$Processor$ExecuteStatement.getResult(TCLIService.java:1497)
	at org.apache.hive.service.rpc.thrift.TCLIService$Processor$ExecuteStatement.getResult(TCLIService.java:1482)
	at org.apache.thrift.ProcessFunction.process(ProcessFunction.java:39)
	at org.apache.thrift.TBaseProcessor.process(TBaseProcessor.java:39)
	at org.apache.hive.service.auth.TSetIpAddressProcessor.process(TSetIpAddressProcessor.java:56)
	at org.apache.thrift.server.TThreadPoolServer$WorkerProcess.run(TThreadPoolServer.java:286)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
	at java.lang.Thread.run(Thread.java:745)


Assumptions

  • We are using mysql as backed Metastore DB
  • Error shows up only while running LLAP
  • HS2 works fine
  • Issue encountered on HDP-2.6.4 distro

Issue

Known bug with older version of mysql-connector https://bugs.mysql.com/bug.php?id=66659. This seems to have been addressed in version "mysql-connector-java-5.1.26" and above.

Solution(s)

  • Use a new mysql-connector version compatible with your Mysql backend DB version
  • If you do not see issues with HS2, then copy the mysql-connector JAR on this host where HSI (HiveServerInteractive) is installed, in this location "/usr/hdp/2.6.4.0-91/hive2/lib/". There is no softlink so it should be renamed to "mysql-connector-java.jar"

Validating the version

  • On host which serves HiveServer2 instance, look for mysql-connector*
[root@xlautomation-2 ~]# find /usr/hdp/2.6.4.0-91/ -name "mysql-connector*"
/usr/hdp/2.6.4.0-91/hive2/lib/mysql-connector-java-5.1.45-bin.jar
/usr/hdp/2.6.4.0-91/hive2/lib/mysql-connector-java.jar
/usr/hdp/2.6.4.0-91/hive/lib/mysql-connector-java.jar

  • Use the jar -xvf command by copying the files to a temp directory, should you want to validate the version. You should be able to do so, using:
[root@xlautomation-1]# mkdir abc
[root@xlautomation-1]# mv mysql-connector-java.jar ./abc
[root@xlautomation-1]# /usr/jdk64/jdk1.8.0_112/bin/jar -xvf mysql-connector-java.jar
[root@xlautomation-1]# cd META-INF/
[root@xlautomation-1]# head /tmp/abc/META-INF/MANIFEST.MF
Manifest-Version: 1.0
Ant-Version: Apache Ant 1.7.1
Created-By: 4.4.6 20120305 (Red Hat 4.4.6-4) (Free Software Foundation
 , Inc.)
Built-By: mockbuild
Bundle-Vendor: Sun Microsystems Inc.
Bundle-Classpath: .
Bundle-Version: 5.1.17
Bundle-Name: Sun Microsystems' JDBC Driver for MySQL
Bundle-ManifestVersion: 2

  • Default MySQL connector for HiveServer2 comes with a higher version, you can validate this using
[root@xlautomation-1 META-INF]# head /tmp/bcd/META-INF/MANIFEST.MF
Manifest-Version: 1.0
Ant-Version: Apache Ant 1.8.2
Created-By: 1.8.0_92-b14 (Oracle Corporation)
Built-By: pb2user
Specification-Title: JDBC
Specification-Version: 4.2
Specification-Vendor: Oracle Corporation
Implementation-Title: MySQL Connector Java
Implementation-Version: 5.1.45
Implementation-Vendor-Id: com.mysql
2,832 Views
0 Kudos
Announcements
What's New @ Cloudera
[RELEASED] Cloudera Streams Messaging - Kubernetes Operator ...
What's New @ Cloudera
[RELEASED] Cloudera Streaming Analytics 1.14 for Cloudera Pu...
What's New @ Cloudera
Cloudera Data Engineering 1.23: Access Spark from Your Favor...
What's New @ Cloudera
HBase REST server scaling support is Generally Available
What's New @ Cloudera
New CLI option in the update-database command
View More Announcements
Version history
Last update:
‎04-27-2018 02:07 AM
Updated by:
Guru
Contributors