Oct 23, 8:06:21.357 AM INFO org.apache.hadoop.hive.metastore.HiveMetaStore [pool-6-thread-21]: 17: source:10.2.5.43 create_database: Database(name:cloudera_manager_metastore_canary_test_db_CD_HIVE_wOVeKiEw_HIVEMETASTORE_1935502b2f811a50157385a4a6378fa7, description:Cloudera Manager Metastore Canary Test Database, locationUri:/user/hue/.cloudera_manager_hive_metastore_canary/CD_HIVE_wOVeKiEw_HIVEMETASTORE_1935502b2f811a50157385a4a6378fa7, parameters:null) Oct 23, 8:06:21.358 AM INFO org.apache.hadoop.hive.metastore.HiveMetaStore.audit [pool-6-thread-21]: ugi=hue ip=10.2.5.43 cmd=source:10.2.5.43 create_database: Database(name:cloudera_manager_metastore_canary_test_db_CD_HIVE_wOVeKiEw_HIVEMETASTORE_1935502b2f811a50157385a4a6378fa7, description:Cloudera Manager Metastore Canary Test Database, locationUri:/user/hue/.cloudera_manager_hive_metastore_canary/CD_HIVE_wOVeKiEw_HIVEMETASTORE_1935502b2f811a50157385a4a6378fa7, parameters:null) Oct 23, 8:06:21.358 AM INFO org.apache.hadoop.hive.metastore.HiveMetaStore [pool-6-thread-21]: 17: Opening raw store with implemenation class:org.apache.hadoop.hive.metastore.ObjectStore Oct 23, 8:06:21.358 AM INFO org.apache.hadoop.hive.metastore.ObjectStore [pool-6-thread-21]: ObjectStore, initialize called Oct 23, 8:06:21.369 AM ERROR org.apache.hadoop.hive.metastore.RetryingHMSHandler [pool-6-thread-21]: Retrying HMSHandler after 2000 ms (attempt 7 of 10) with error: 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:451) at org.datanucleus.api.jdo.JDOPersistenceManager.getDataStoreConnection(JDOPersistenceManager.java:2259) at org.apache.hadoop.hive.metastore.MetaStoreDirectSql.getProductName(MetaStoreDirectSql.java:155) at org.apache.hadoop.hive.metastore.MetaStoreDirectSql.determineDbType(MetaStoreDirectSql.java:146) at org.apache.hadoop.hive.metastore.MetaStoreDirectSql.(MetaStoreDirectSql.java:120) at org.apache.hadoop.hive.metastore.ObjectStore.initialize(ObjectStore.java:295) at org.apache.hadoop.hive.metastore.ObjectStore.setConf(ObjectStore.java:258) at org.apache.hadoop.util.ReflectionUtils.setConf(ReflectionUtils.java:73) at org.apache.hadoop.util.ReflectionUtils.newInstance(ReflectionUtils.java:133) at org.apache.hadoop.hive.metastore.RawStoreProxy.(RawStoreProxy.java:56) at org.apache.hadoop.hive.metastore.RawStoreProxy.getProxy(RawStoreProxy.java:65) at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.newRawStore(HiveMetaStore.java:579) at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.getMS(HiveMetaStore.java:557) at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.get_database_core(HiveMetaStore.java:933) at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.create_database(HiveMetaStore.java:876) at sun.reflect.GeneratedMethodAccessor7.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.$Proxy18.create_database(Unknown Source) at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Processor$create_database.getResult(ThriftHiveMetastore.java:8519) at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Processor$create_database.getResult(ThriftHiveMetastore.java:8503) at org.apache.thrift.ProcessFunction.process(ProcessFunction.java:39) at org.apache.hadoop.hive.metastore.TUGIBasedProcessor$1.run(TUGIBasedProcessor.java:110) at org.apache.hadoop.hive.metastore.TUGIBasedProcessor$1.run(TUGIBasedProcessor.java:106) at java.security.AccessController.doPrivileged(Native Method) at javax.security.auth.Subject.doAs(Subject.java:415) at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1671) at org.apache.hadoop.hive.metastore.TUGIBasedProcessor.process(TUGIBasedProcessor.java:118) at org.apache.thrift.server.TThreadPoolServer$WorkerProcess.run(TThreadPoolServer.java:285) 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:745) 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.GeneratedConstructorAccessor31.newInstance(Unknown Source) 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.StatementImpl.executeSimpleNonQuery(StatementImpl.java:1606) at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1503) at com.mysql.jdbc.ConnectionImpl.getTransactionIsolation(ConnectionImpl.java:3173) at com.jolbox.bonecp.ConnectionHandle.getTransactionIsolation(ConnectionHandle.java:825) at org.datanucleus.store.rdbms.ConnectionFactoryImpl$ManagedConnectionImpl.getConnection(ConnectionFactoryImpl.java:444) at org.datanucleus.store.rdbms.ConnectionFactoryImpl$ManagedConnectionImpl.getXAResource(ConnectionFactoryImpl.java:378) at org.datanucleus.store.connection.ConnectionManagerImpl.allocateConnection(ConnectionManagerImpl.java:328) at org.datanucleus.store.connection.AbstractConnectionFactory.getConnection(AbstractConnectionFactory.java:94) at org.datanucleus.store.rdbms.RDBMSStoreManager.getNucleusConnection(RDBMSStoreManager.java:1357) at org.datanucleus.api.jdo.JDOPersistenceManager.getDataStoreConnection(JDOPersistenceManager.java:2245) at org.apache.hadoop.hive.metastore.MetaStoreDirectSql.getProductName(MetaStoreDirectSql.java:155) at org.apache.hadoop.hive.metastore.MetaStoreDirectSql.determineDbType(MetaStoreDirectSql.java:146) at org.apache.hadoop.hive.metastore.MetaStoreDirectSql.(MetaStoreDirectSql.java:120) at org.apache.hadoop.hive.metastore.ObjectStore.initialize(ObjectStore.java:295) at org.apache.hadoop.hive.metastore.ObjectStore.setConf(ObjectStore.java:258) at org.apache.hadoop.util.ReflectionUtils.setConf(ReflectionUtils.java:73) at org.apache.hadoop.util.ReflectionUtils.newInstance(ReflectionUtils.java:133) at org.apache.hadoop.hive.metastore.RawStoreProxy.(RawStoreProxy.java:56) at org.apache.hadoop.hive.metastore.RawStoreProxy.getProxy(RawStoreProxy.java:65) at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.newRawStore(HiveMetaStore.java:579) at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.getMS(HiveMetaStore.java:557) at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.get_database_core(HiveMetaStore.java:933) at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.create_database(HiveMetaStore.java:876) at sun.reflect.GeneratedMethodAccessor7.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.$Proxy18.create_database(Unknown Source) at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Processor$create_database.getResult(ThriftHiveMetastore.java:8519) at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Processor$create_database.getResult(ThriftHiveMetastore.java:8503) at org.apache.thrift.ProcessFunction.process(ProcessFunction.java:39) at org.apache.hadoop.hive.metastore.TUGIBasedProcessor$1.run(TUGIBasedProcessor.java:110) at org.apache.hadoop.hive.metastore.TUGIBasedProcessor$1.run(TUGIBasedProcessor.java:106) at java.security.AccessController.doPrivileged(Native Method) at javax.security.auth.Subject.doAs(Subject.java:415) at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1671) at org.apache.hadoop.hive.metastore.TUGIBasedProcessor.process(TUGIBasedProcessor.java:118) at org.apache.thrift.server.TThreadPoolServer$WorkerProcess.run(TThreadPoolServer.java:285) 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:745)
Created on 10-24-2016 01:03 AM - edited 10-24-2016 01:13 AM
The problem is that the SET OPTION syntax has been deprecated for some time and is now no longer valid in MySQL 5.6.
http://stackoverflow.com/questions/15113707/error-code-1064-sql-state-42000-you-have-an-error-in-you...
What alternative to use for 'OPTION SQL_SELECT_LIMIT=DEFAULT' with MySQL 5.6 with CDH 5.4.0 ?
Can I change some configuration 'OPTION SQL_SELECT_LIMIT=DEFAULT' to 'SQL_SELECT_LIMIT=1' ?
Regards,
Kartik
Created 11-03-2016 10:14 AM
Minimum Mysql version available over AWS RDS is 5.5.40a.
Please advice which version of mysql will not throw canary health issue.
It is just due to incompatiblity in health test command.
Regards,
kartikbha
Created 11-03-2016 04:33 PM
The problem here is that the Hive MetaStore is trying to talk to the backing MySQL database, but that is failing. The Canary is merely testing to make sure your metastore is functional.
It could be your driver... I'd check out th einformation here and see if that might hlep:
http://www.cloudera.com/documentation/enterprise/latest/topics/cm_ig_mysql.html#cmig_topic_5_5_3
Regards,
Ben
Created 12-06-2016 09:24 AM
I got the similar issue. Copying mysql-connector-java.xxx to /usr/share/java/ has fixed the issue. Follow the below steps:
1. Get the latest (or) suitable mysql-connector from the below link
http://dev.mysql.com/downloads/connector/j/5.1.html
wget http://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-5.1.40.tar.gz
2. tar zxvf mysql-connector-java-5.1.40.tar.gz
3. sudo cp mysql-connector-java-5.1.40/mysql-connector-java-5.1.40-bin.jar /usr/share/java/
4. If /usr/share/java path already has a link mysql-connector-java.jar and it is referring to any old mysql-connector. Ex: mysql-connector-java-5.1.31-bin.jar then remove the link mysql-connector-java.jar and make it refer to the newly copied mysql-connector-java-5.1.40-bin.jar
rm mysql-connector-java.jar
ln -s /usr/share/java/mysql-connector-java-5.1.40-bin.jar mysql-connector-java.jar
mysql-connector-java.jar -> /usr/share/java/mysql-connector-java-5.1.40-bin.jar
5. Restart your hive service in CM
6. For more details, refer http://www.cloudera.com/documentation/enterprise/5-7-x/topics/cm_ig_mysql.html
Thanks
Kumar
Created 01-17-2017 11:48 AM
Thanks, your solution worked for me as well. Had to update mysql-connector jar on both HiveServer and HiveMetastore.
Created on 02-08-2017 12:18 AM - edited 02-08-2017 12:18 AM
One thing we have to notice is that the jar file has to have right to execute, so I use "chmod a+rx mysql-XXX.jar" to set the permission.
Created 04-11-2018 02:08 PM
Had same kind of issue, resolved by updating Hive-Config V5.14
Hive Metastore Server Advanced Configuration Snippet (Safety Valve) for hive-site.xml
hive.metastore.event.listeners