Table 'metastore.CM_VERSION' doesn't exist when start Cloudera Manager

[#1]

I use this command to start:

sudo systemctl start cloudera-scm-server.

I 'm using CDH-6.2.0-1.cdh6.2.0. and I use mysql as metastore.

Then it tells me as below:

```

2019-07-16 15:06:38,947 ERROR main:com.cloudera.server.cmf.Main: Server failed.
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'com.cloudera.server.cmf.TrialState': Cannot resolve reference to bean 'entityManagerFactoryBean' while setting constructor argument; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactoryBean': FactoryBean threw exception on object creation; nested exception is java.lang.RuntimeException: Unable to obtain CM release version.
at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveReference(BeanDefinitionValueResolver.java:359)
at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveValueIfNecessary(BeanDefinitionValueResolver.java:108)
at org.springframework.beans.factory.support.ConstructorResolver.resolveConstructorArguments(ConstructorResolver.java:634)
at org.springframework.beans.factory.support.ConstructorResolver.autowireConstructor(ConstructorResolver.java:145)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.autowireConstructor(AbstractAutowireCapableBeanFactory.java:1198)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:1100)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:511)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:481)
at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:312)
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:308)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:197)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:761)
at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:867)
at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:543)
at com.cloudera.server.cmf.Main.bootstrapSpringContext(Main.java:418)
at com.cloudera.server.cmf.Main.<init>(Main.java:260)
at com.cloudera.server.cmf.Main.main(Main.java:233)
Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactoryBean': FactoryBean threw exception on object creation; nested exception is java.lang.RuntimeException: Unable to obtain CM release version.
at org.springframework.beans.factory.support.FactoryBeanRegistrySupport.doGetObjectFromFactoryBean(FactoryBeanRegistrySupport.java:185)
at org.springframework.beans.factory.support.FactoryBeanRegistrySupport.getObjectFromFactoryBean(FactoryBeanRegistrySupport.java:103)
at org.springframework.beans.factory.support.AbstractBeanFactory.getObjectForBeanInstance(AbstractBeanFactory.java:1640)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:254)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:197)
at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveReference(BeanDefinitionValueResolver.java:351)
... 17 more
Caused by: java.lang.RuntimeException: Unable to obtain CM release version.
at com.cloudera.server.cmf.bootstrap.EntityManagerFactoryBean.getPersistedCmRelease(EntityManagerFactoryBean.java:311)
at com.cloudera.server.cmf.bootstrap.EntityManagerFactoryBean.checkVersionDoFail(EntityManagerFactoryBean.java:358)
at com.cloudera.server.cmf.bootstrap.EntityManagerFactoryBean.getObject(EntityManagerFactoryBean.java:172)
at com.cloudera.server.cmf.bootstrap.EntityManagerFactoryBean.getObject(EntityManagerFactoryBean.java:75)
at org.springframework.beans.factory.support.FactoryBeanRegistrySupport.doGetObjectFromFactoryBean(FactoryBeanRegistrySupport.java:178)
... 22 more
Caused by: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:147)
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:155)
at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1407)
at org.hibernate.query.internal.AbstractProducedQuery.getSingleResult(AbstractProducedQuery.java:1444)
at com.cloudera.cmf.persist.DbVersionDao.getVersion(DbVersionDao.java:53)
at com.cloudera.server.cmf.bootstrap.EntityManagerFactoryBean.getPersistedCmRelease(EntityManagerFactoryBean.java:307)
... 26 more
Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:63)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:97)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:79)
at org.hibernate.loader.Loader.getResultSet(Loader.java:2123)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1911)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1887)
at org.hibernate.loader.Loader.doQuery(Loader.java:932)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:349)
at org.hibernate.loader.Loader.doList(Loader.java:2615)
at org.hibernate.loader.Loader.doList(Loader.java:2598)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2430)
at org.hibernate.loader.Loader.list(Loader.java:2425)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:335)
at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:2128)
at org.hibernate.internal.AbstractSharedSessionContract.list(AbstractSharedSessionContract.java:991)
at org.hibernate.query.internal.NativeQueryImpl.doList(NativeQueryImpl.java:147)
at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1398)
... 29 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'metastore.CM_VERSION' doesn't exist
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
at com.mysql.jdbc.Util.getInstance(Util.java:408)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:944)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2484)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1966)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:116)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:70)

```

Mysql database is as below:

```

+---------------------------+
| Tables_in_metastore |
+---------------------------+
| AUDITS |
| BUCKETING_COLS |
| CDH_VERSION |
| CDS |
| COLUMNS_V2 |
| COMMANDS |
| CONFIGS |
| CREDENTIALS |
| DATABASE_PARAMS |
| DBS |
| DB_PRIVS |
| DELEGATION_TOKENS |
| FUNCS |
| FUNC_RU |
| GLOBAL_PRIVS |
| HOSTS |
| IDXS |
| INDEX_PARAMS |
| KEY_CONSTRAINTS |
| MASTER_KEYS |
| NOTIFICATION_LOG |
| NOTIFICATION_SEQUENCE |
| NUCLEUS_TABLES |
| PARTITIONS |
| PARTITION_EVENTS |
| PARTITION_KEYS |
| PARTITION_KEY_VALS |
| PARTITION_PARAMS |
| PART_COL_PRIVS |
| PART_COL_STATS |
| PART_PRIVS |
| PROCESSES |
| ROLES |
| ROLE_MAP |
| SCHEMA_VERSION |
| SDS |
| SD_PARAMS |
| SEQUENCE_TABLE |
| SERDES |
| SERDE_PARAMS |
| SKEWED_COL_NAMES |
| SKEWED_COL_VALUE_LOC_MAP |
| SKEWED_STRING_LIST |
| SKEWED_STRING_LIST_VALUES |
| SKEWED_VALUES |
| SORT_COLS |
| TABLE_PARAMS |
| TAB_COL_STATS |
| TBLS |
| TBL_COL_PRIVS |
| TBL_PRIVS |
| TYPES |
| TYPE_FIELDS |
| VERSION |

```

[#2]

Hi Dennisli,

I am also facing the same issue today and working to fix it. Please share with me if you get any solution.

Thanks in Advance:)

Thanks,

Sai Vijaya

[#3]

Here's what I understand from the error;

1. I 'm using CDH-6.2.0-1.cdh6.2.0. and I use mysql as metastore.

2. Then it tells me as below:

2019-07-16 15:06:38,947 ERROR main:com.cloudera.server.cmf.Main: Server failed.

...

at com.cloudera.server.cmf.Main.main(Main.java:233)
Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactoryBean': FactoryBean threw exception on object creation; nested exception is java.lang.RuntimeException: Unable to obtain CM release version.

...

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'metastore.CM_VERSION' doesn't exist

Cloudera Manager uses database [0], and the database name information is under /etc/cloudera-scm-server/db.properties, normally the database name should be different and not "metastore"

Hive "metastore" also uses database [1], and the database configuration settings is stored in Cloudera Manger web UI, these configuration is saved in Cloudera Manager Database.

Now some discrepancies 'metastore.CM_VERSION' (note metastore) and the "Tables_in_metastore" (Mysql database is as below:) which you have provided, is listing the "Hive Metastore Database" tables, intermixed with "Cloudera Manager Database" tables. This is not ideal - as it is difficult to narrow down the issue when they happen. 

The "Tables_in_metastore" doesn't list the table name 'metastore.CM_VERSION' as you can see.

Before we continue to look into why "CM_VERSION" table is not listed in the "metastore" database,

few things to consider, was your CM server, always configured to connect to the "metastore" database in the past? Or was it connected to a different database (do # ls -ltr /etc/cloudera-scm-server/db.*) - check for db.properties backup open the files and check for the database name. And also are you aware of any recent changes in your cluster?

Regards,

Michalis

[0] https://www.cloudera.com/documentation/enterprise/latest/topics/cm_ig_installing_configuring_dbs.htm...

[1] https://www.cloudera.com/documentation/enterprise/latest/topics/cdh_ig_hive_metastore_configure.html...

[#4]

Thanks Michalis, just checked the db.properties as below:

-rw-r--r-- 1 cloudera-scm cloudera-scm 757 3月  15 01:45 /etc/cloudera-scm-server/db.properties.~1~
-rw------- 1 cloudera-scm cloudera-scm 446 6月  18 19:37 /etc/cloudera-scm-server/db.properties.~2~
-rw------- 1 cloudera-scm cloudera-scm 448 6月  18 19:39 /etc/cloudera-scm-server/db.properties.~3~
-rw------- 1 cloudera-scm cloudera-scm 448 6月  18 19:39 /etc/cloudera-scm-server/db.properties.~4~
-rw------- 1 cloudera-scm cloudera-scm 446 6月  18 19:40 /etc/cloudera-scm-server/db.properties.~5~
-rw------- 1 cloudera-scm cloudera-scm 448 6月  18 19:40 /etc/cloudera-scm-server/db.properties.~6~
-rw------- 1 cloudera-scm cloudera-scm 443 6月  18 19:41 /etc/cloudera-scm-server/db.properties.~7~
-rw------- 1 cloudera-scm cloudera-scm 447 6月  18 19:41 /etc/cloudera-scm-server/db.properties.~8~
-rw------- 1 cloudera-scm cloudera-scm 441 6月  18 19:41 /etc/cloudera-scm-server/db.properties.~9~
-rw------- 1 cloudera-scm cloudera-scm 446 6月  18 19:41 /etc/cloudera-scm-server/db.properties.~10~
-rw------- 1 cloudera-scm cloudera-scm 452 6月  18 19:42 /etc/cloudera-scm-server/db.properties.~11~
-rw------- 1 cloudera-scm cloudera-scm 450 6月  18 19:42 /etc/cloudera-scm-server/db.properties.~12~
-rw------- 1 cloudera-scm cloudera-scm 450 6月  18 19:42 /etc/cloudera-scm-server/db.properties.~13~
-rw------- 1 cloudera-scm cloudera-scm 453 6月  18 19:43 /etc/cloudera-scm-server/db.properties.~14~
-rw------- 1 cloudera-scm cloudera-scm 446 6月  18 21:20 /etc/cloudera-scm-server/db.properties.~15~
-rw------- 1 cloudera-scm cloudera-scm 453 6月  19 13:05 /etc/cloudera-scm-server/db.properties.~16~
-rw------- 1 cloudera-scm cloudera-scm 442 7月  16 14:34 /etc/cloudera-scm-server/db.properties.~17~
-rw------- 1 cloudera-scm cloudera-scm 446 7月  16 14:34 /etc/cloudera-scm-server/db.properties.~18~
-rw------- 1 cloudera-scm cloudera-scm 458 7月  16 14:37 /etc/cloudera-scm-server/db.properties.~19~
-rw------- 1 cloudera-scm cloudera-scm 453 7月  16 14:37 /etc/cloudera-scm-server/db.properties.~20~
-rw------- 1 cloudera-scm cloudera-scm 453 7月  16 15:02 /etc/cloudera-scm-server/db.properties.~21~
-rw------- 1 cloudera-scm cloudera-scm 453 7月  16 15:03 /etc/cloudera-scm-server/db.properties.~22~
-rw------- 1 cloudera-scm cloudera-scm 453 7月  16 15:04 /etc/cloudera-scm-server/db.properties.~23~
-rw------- 1 cloudera-scm cloudera-scm 453 7月  16 16:46 /etc/cloudera-scm-server/db.properties.~24~
-rw------- 1 cloudera-scm cloudera-scm 453 7月  16 16:47 /etc/cloudera-scm-server/db.properties.~25~
-rw------- 1 cloudera-scm cloudera-scm 454 7月  16 16:47 /etc/cloudera-scm-server/db.properties.~26~
-rw------- 1 cloudera-scm cloudera-scm 453 7月  16 16:47 /etc/cloudera-scm-server/db.properties.~27~
-rw------- 1 cloudera-scm cloudera-scm 446 7月  16 17:03 /etc/cloudera-scm-server/db.properties.~28~
-rw------- 1 cloudera-scm cloudera-scm 446 7月  16 17:03 /etc/cloudera-scm-server/db.properties

com.cloudera.cmf.db.type=mysql
com.cloudera.cmf.db.host=localhost
com.cloudera.cmf.db.name=scm
com.cloudera.cmf.db.user=scm
com.cloudera.cmf.db.setupType=EXTERNAL
com.cloudera.cmf.db.password=mypassword

few days ago my CM started successfully. But, after restarted my server, it raised that error when I started.

Now I tried to drop all the databases in mysql and create again,

then run the following command respectively :

sudo /opt/cloudera/cm/schema/scm_prepare_database.sh mysql scm scm mypassword
sudo /opt/cloudera/cm/schema/scm_prepare_database.sh mysql amon amon mypassword
sudo /opt/cloudera/cm/schema/scm_prepare_database.sh mysql rman rman mypassword
sudo /opt/cloudera/cm/schema/scm_prepare_database.sh mysql hue hue mypassword
sudo /opt/cloudera/cm/schema/scm_prepare_database.sh mysql scm scm mypassword
sudo /opt/cloudera/cm/schema/scm_prepare_database.sh mysql metastore hive mypassword
sudo /opt/cloudera/cm/schema/scm_prepare_database.sh mysql sentry sentry mypassword
sudo /opt/cloudera/cm/schema/scm_prepare_database.sh mysql nav nav mypassword
sudo /opt/cloudera/cm/schema/scm_prepare_database.sh mysql navms navms mypassword
sudo /opt/cloudera/cm/schema/scm_prepare_database.sh mysql oozie oozie mypassword

[#5 · Accepted solution]

> few days ago my CM started successfully. But, after restarted my server, it raised that error when I started.

Unfortunately without the logs, or stack trace I won't be able to assess what caused the error.

Back to the intermixed "scm" and "metastore" database issue;

> "Now I tried to drop all the databases in mysql and create again, then run the following command respectively :"

I think there may have been some misunderstanding, about the intention of the script "/opt/cloudera/cm/schema/scm_prepare_database.sh". The script as the documenatation state, is to create and configure a database for iteself [Cloudera Manager Server] [0]

As seen in your comment, you've used for various services (amon, rman, ... metastore, ... etc)

Please do check in your RDBMS (MySQL), and confirm which database name CM should be connecting to.

eg:

mysql> show databases;

and for each database confirm which one has the correct schema with the "CM_VERSION" table in.

mysql> show tables;

or something like this;

SELECT table_name, table_schema AS dbname
  FROM INFORMATION_SCHEMA.TABLES
  WHERE table_name='CM_VERSION'

Note this is just a suggestion, to find the correct database where CM server can connect to.

[0] https://www.cloudera.com/documentation/enterprise/latest/topics/prepare_cm_database.html#cmig_topic_...

[#6]

Hi @michalis,

I have downgraded Mysql version from 10.2 to 10.0.

Now it is working fine:)

Thank you so much for your support:)

Thnka,

Vijaya

[#7]

Hi Michalis:

 I just found that, I apologize for my late reply. That time I cleaned all metastore and recreated the database in MySQL and it worked. Recently however, this error happend again, as I restarted the

cloudera-scm-server and cloudera-scm-agent

By following your instruction, I found an important information: the CM_VERSION is in scm database!!

mysql> SELECT table_name, table_schema AS dbname
    ->   FROM INFORMATION_SCHEMA.TABLES
    ->   WHERE table_name='CM_VERSION';
+------------+--------+
| table_name | dbname |
+------------+--------+
| CM_VERSION | scm    |
+------------+--------+
1 row in set (2.11 sec)

 I don't know why it's gonna find in metastore database instead of scm. Could you give me some help, Thanks.

Kind regards

[#8]

Hi Michalis:

I solved this by re-run the command: 

sudo /opt/cloudera/cm/schema/scm_prepare_database.sh mysql scm scm mypassword

This problem is due to my misunderstanding for scm_prepare_database.sh.

Thanks a lot for your help.

Kind regards