The Hive Metastore canary failed to create a database.

[#1]

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)

[#2]

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

[#3]

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

[#4]

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

[#5]

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

[#6]

Thanks, your solution worked for me as well. Had to update mysql-connector jar on both HiveServer and HiveMetastore.

[#7]

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.

[#8]

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