Hive Jobs Fail with com.mysql.jdbc.exceptions.MySQLSyntaxErrorException

Problem

When executing a Hive, creating a Hive Connection or editing an existing Hive connection, a com.mysql.jdbc.exceptions.MySQLSyntaxErrorException is thrown. The initial content in the stacktrace is as follows:

ERROR [2015-01-26 23:26:02.613] [JobScheduler thread-1] (JobScheduler.java:791) - Job 12103 failed with exception.
java.lang.RuntimeException: 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.jdo.NucleusJDOHelper.getJDOExceptionForNucleusException(NucleusJDOHelper.java:313)
        at org.datanucleus.jdo.JDOQuery.execute(JDOQuery.java:275)
        at org.apache.hadoop.hive.metastore.ObjectStore.getMTable(ObjectStore.java:824)
        at org.apache.hadoop.hive.metastore.ObjectStore.getTable(ObjectStore.java:758)
        at sun.reflect.GeneratedMethodAccessor3.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:606)
        at org.apache.hadoop.hive.metastore.RetryingRawStore.invoke(RetryingRawStore.java:111)
        at com.sun.proxy.$Proxy4.getTable(Unknown Source)
        at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.get_table(HiveMetaStore.java:1367)
        at sun.reflect.GeneratedMethodAccessor2.invoke(Unknown Source)
        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:102)
        at com.sun.proxy.$Proxy5.get_table(Unknown Source)
        at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Processor$get_table.getResult(ThriftHiveMetastore.java:6459)
        at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Processor$get_table.getResult(ThriftHiveMetastore.java:6443)
        at org.apache.thrift.ProcessFunction.process(ProcessFunction.java:39)
        at org.apache.thrift.TBaseProcessor.process(TBaseProcessor.java:39)
        at org.apache.hadoop.hive.metastore.TSetIpAddressProcessor.process(TSetIpAddressProcessor.java:48)
        at org.apache.thrift.server.TThreadPoolServer$WorkerProcess.run(TThreadPoolServer.java:206)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
        at java.lang.Thread.run(Thread.java:744)
NestedThrowablesStackTrace:
com.mysql.jdbc.exceptions.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

This issue is only observed in environments that are using MySQL to store the Hive Metastore information.

Cause

This issue is caused by an outdated MySQL JDBC driver for the Hive Metastore. 

Solution

To resolve this issue, update the MySQL JDBC driver for the Hive Metastore. Specifically, when Hive creates a connection to the Metastore, it utilizes a MySQL driver to connect to the Metastore. This driver is not provided by Datameer.

The following is a list of known directories where this file may exist:

/opt/mapr/hadoop/hadoop-0.20.2/lib

/usr/lib/hive/lib

This list may be incomplete and this file may reside in a different location. If the location is not found, please consult with the Hive Administrator to identify the path. If required, search the file system for files matching this pattern as a clue: "mysql*jar".