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.

The Hive Metastore canary failed to create a database.

The Hive Metastore canary failed to create a database.

Explorer

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)

7 REPLIES 7

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

Explorer

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

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

Explorer

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

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

Super Guru

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

Highlighted

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

Champion

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

 

 

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.

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

New Contributor

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.

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

New Contributor

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

Don't have an account?
Coming from Hortonworks? Activate your account here