Reply
Expert Contributor
Posts: 66
Registered: ‎12-24-2015

The Hive Metastore canary failed to create a database.

[ Edited ]

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)

Expert Contributor
Posts: 66
Registered: ‎12-24-2015

Re: The Hive Metastore canary failed to create a database.

[ Edited ]

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

Expert Contributor
Posts: 66
Registered: ‎12-24-2015

Re: The Hive Metastore canary failed to create a database.

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

Posts: 1,033
Topics: 1
Kudos: 257
Solutions: 128
Registered: ‎04-22-2014

Re: The Hive Metastore canary failed to create a database.

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

Posts: 519
Topics: 14
Kudos: 92
Solutions: 45
Registered: ‎09-02-2016

Re: The Hive Metastore canary failed to create a database.

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

 

 

Explorer
Posts: 7
Registered: ‎12-20-2016

Re: The Hive Metastore canary failed to create a database.

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

New Contributor
Posts: 2
Registered: ‎10-26-2016

Re: The Hive Metastore canary failed to create a database.

[ Edited ]

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.

New Contributor
Posts: 1
Registered: ‎03-07-2018

Re: The Hive Metastore canary failed to create a database - PostgreSQL

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

Announcements