Hive Metastore not working - Syntax error 'OPTION SQL_SELECT_LIMIT=DEFAULT' at line 1

[#1]

My Environment details:

HDP Version - 2.4.2.0

Ambari - 2.2.2.0

Mysql Version - 5.1.34

.

Hive shell was taking lot of time to load and when I checked Hive Metastore logs, I found below exception:

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 

.

Complete StackTrace:

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) 

.

Root cause:

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

.

Resolution:

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

.

Where to place upgraded connector?

On Ambari server(as root user):

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 HiveMetastore

mkdir ~/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 Hiveserver2

mkdir ~/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-*

.

Restart HiveMestastore and Hiveserver2 via Ambari and this should fix your issue! 🙂

.

Please comment if you have any quesiton or feedback. Happy Hadooping!! 🙂

[#2]

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)