Our Community is getting an upgrade! To get everything ready for the relaunch, we’ll be placing the site in read-only mode starting September 21st.
We really appreciate your understanding while we get things set up behind the scenes. Catch up on all the exciting details about the move here.
Need help or have questions? Drop us a line at [email protected]

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
Announcements
Share your experience with Cloudera on G2 and get a $25 Amazon Gift card.
Hi, I'm CLEO! Something exciting is coming to the Community. Stay Tuned!

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
3,574 Views
0 Kudos
Announcements
What's New @ Cloudera
Announcing Cloudera Data Flow 3.2 for Cloudera on Cloud
Community Announcements
Community Upgrade Announcement
What's New @ Cloudera
Announcing Cloudera Flow Management Operator for Kubernetes ...
What's New @ Cloudera
Announcing Cloudera Streams Messaging Operator for Kubernete...
Community Announcements
July 2026 Community Highlights
View More Announcements
Version history
Last update:
‎04-27-2018 02:07 AM
Updated by:
Guru
Contributors