Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Unable to delete hive table MySQL server version for the right syntax to use near 'OPTION SQL_SELECT_LIMIT= DEFAULT' at line 1

Unable to delete hive table MySQL server version for the right syntax to use near 'OPTION SQL_SELECT_LIMIT= DEFAULT' at line 1

New Contributor

Hi All,

short description:

select scripts, count(*) (using tez.MR2) are working good. but when I try to drop table getting below error and mysql connection is crashing, I need to restart mysql server again to establish connection.

I tried below thread and changed the below mysql java connector versions as mentioned here https://community.hortonworks.com/articles/70912/hive-metastore-not-working-syntax-error-option-sql....

but still not working.

mysql-connector-java-5.1.22-bin.jar

mysql-connector-java-5.1.44-bin.jar

mysql-connector-java-5.1.45-bin.jar

Below are my cluster details

HDP 2.6

Mysql version 5.7.20

Ambari Version 2.6.0.0

Should I need to add hive home and hadoop home in .bashrc file?

appreciate if some one helps on this..

Complete Error stack:

: jdbc:hive2://sandbox-hdf.hortonworks.com:1> drop table test1; Error: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.e$ ec.DDLTask. MetaException(message:javax.jdo.JDODataStoreException: You have an error in your SQL syntax; check t$ e 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:238) at org.apache.hadoop.hive.metastore.ObjectStore.getMTable(ObjectStore.java:1160) at org.apache.hadoop.hive.metastore.ObjectStore.getTable(ObjectStore.java:1058) at sun.reflect.GeneratedMethodAccessor45.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:103) at com.sun.proxy.$Proxy9.getTable(Unknown Source) at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.get_table_core(HiveMetaStore.java:2030) at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.drop_table_core(HiveMetaStore.java:1563) at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.drop_table_with_environment_context(HiveMeta Store.java:1793) at sun.reflect.GeneratedMethodAccessor71.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:147) at org.apache.hadoop.hive.metastore.RetryingHMSHandler.invoke(RetryingHMSHandler.java:105) at com.sun.proxy.$Proxy13.drop_table_with_environment_context(Unknown Source) at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.drop_table_with_environment_context(HiveMetaStore Client.java:2216) at org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient.drop_table_with_environment_context(Sess ionHiveMetaStoreClient.java:120) at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.dropTable(HiveMetaStoreClient.java:1035) at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.dropTable(HiveMetaStoreClient.java:971) 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.RetryingMetaStoreClient.invoke(RetryingMetaStoreClient.java:178) at com.sun.proxy.$Proxy14.dropTable(Unknown Source) at org.apache.hadoop.hive.ql.metadata.Hive.dropTable(Hive.java:1112) at org.apache.hadoop.hive.ql.metadata.Hive.dropTable(Hive.java:1049) at org.apache.hadoop.hive.ql.exec.DDLTask.dropTable(DDLTask.java:4138) at org.apache.hadoop.hive.ql.exec.DDLTask.dropTableOrPartitions(DDLTask.java:3991) at org.apache.hadoop.hive.ql.exec.DDLTask.execute(DDLTask.java:343) at org.apache.hadoop.hive.ql.exec.Task.executeTask(Task.java:162) at org.apache.hadoop.hive.ql.exec.TaskRunner.runSequential(TaskRunner.java:89) at org.apache.hadoop.hive.ql.Driver.launchTask(Driver.java:1756) at org.apache.hadoop.hive.ql.Driver.execute(Driver.java:1497) at org.apache.hadoop.hive.ql.Driver.runInternal(Driver.java:1294) at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1161) at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1156) at org.apache.hive.service.cli.operation.SQLOperation.runQuery(SQLOperation.java:197) at org.apache.hive.service.c

1 REPLY 1

Re: Unable to delete hive table MySQL server version for the right syntax to use near 'OPTION SQL_SELECT_LIMIT= DEFAULT' at line 1

Rising Star

@Anand Babu As you pointed the doc,have you followed the last step(after linking exact connector jars)

  1. rm -rf /var/lib/ambari-agent/tmp/mysql-* (On Hivemetastore and HIveserver2 hosts -- restart services)
Don't have an account?
Coming from Hortonworks? Activate your account here