Handling “Hive Metastore not working – Syntax error ‘OPTION SQL_SELECT_LIMIT=DEFAULT’ at line 1”

Problem Description

While dropping a hive table the following exception encountered in hive shell.

FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message: 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)

Hive version – 2.3.3

mysql-java-connector version – 5.1.18

Root cause

  • MySql server version was patched(upgraded to 5.1.38) however mysql-java-connector was not upgraded.
  • Hive was configured to use Mysql as Metastore DB.
  • More details – http://bugs.mysql.com/bug.php?id=66659

Resolution

Replace mysql-java-connector from mysql-java-connector-5.1.18 to mysql-java-connector-5.1.22 in lib directory of hive installation.

Download link https://downloads.mysql.com/archives/c-j/

 


About the course

Big Data Hadoop Training in Bangalore provided by NPN Training is a program designed to help professionals gain proficiency to work with the latest and core components of Hadoop like MapReduce, Hive, HBase, Sqoop, Quartz Scheduler, Pig and more. The course is prepared with inputs from the best in the industry.

 

Naveen P.N

12+ years of experience in IT with vast experience in executing complex projects using Java, Micro Services , Big Data and Cloud Platforms. I found NPN Training Pvt Ltd a India based startup to provide high quality training for IT professionals. I have trained more than 3000+ IT professionals and helped them to succeed in their career in different technologies. I am very passionate about Technology and Training. I have spent 12 years at Siemens, Yahoo, Amazon and Cisco, developing and managing technology.