Embedded to External mysql - Cloudera Manager is not starting

[#1]

Hi there,

Can someone help me please ? I am trying to replace embedded postgres with an external mysql running on a remote CentOS 7.5 machine.

mysql --version
mysql  Ver 14.14 Distrib 5.7.22, for Linux (x86_64) using  EditLine wrapper

I followed all the steps in the cloudera document and the problem is...

/usr/share/cmf/schema/scm_prepare_database.sh mysql -h mysqlserver.domain.com --scm-host cdhmgr.domain.com scm scm
Enter SCM password: 
JAVA_HOME=/usr/java/latest
Verifying that we can write to /etc/cloudera-scm-server
Creating SCM configuration file in /etc/cloudera-scm-server
Executing:  /usr/java/latest/bin/java -cp /usr/share/java/mysql-connector-java.jar:/usr/share/java/oracle-connector-java.jar:/usr/share/cmf/schema/../lib/* com.cloudera.enterprise.dbutil.DbCommandExecutor /etc/cloudera-scm-server/db.properties com.cloudera.cmf.db.
Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
Fri Jul 20 08:46:44 EDT 2018 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
[                          main] DbCommandExecutor              ERROR Error when connecting to database.
java.sql.SQLException: The server time zone value 'EDT' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:127)[mysql-connector-java.jar:8.0.11]
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:95)[mysql-connector-java.jar:8.0.11]
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:87)[mysql-connector-java.jar:8.0.11]
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:61)[mysql-connector-java.jar:8.0.11]
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:71)[mysql-connector-java.jar:8.0.11]
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:76)[mysql-connector-java.jar:8.0.11]
	at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:862)[mysql-connector-java.jar:8.0.11]
	at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:444)[mysql-connector-java.jar:8.0.11]
	at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:230)[mysql-connector-java.jar:8.0.11]
	at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:226)[mysql-connector-java.jar:8.0.11]
	at java.sql.DriverManager.getConnection(DriverManager.java:664)[:1.8.0_172]
	at java.sql.DriverManager.getConnection(DriverManager.java:247)[:1.8.0_172]
	at com.cloudera.enterprise.dbutil.DbCommandExecutor.testDbConnection(DbCommandExecutor.java:253)[db-common-5.14.3.jar:]
	at com.cloudera.enterprise.dbutil.DbCommandExecutor.main(DbCommandExecutor.java:138)[db-common-5.14.3.jar:]
[                          main] DbCommandExecutor              ERROR Exiting with exit code 4
--> Error 4, giving up (use --force if you wish to ignore the error)

[#2]

@Anudas,

This is the cause of the exception
java.sql.SQLException: The server time zone value 'EDT' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.

I did find some evidence that the latest Connector/J 8.0.x drivers may cause some trouble in this area.

See:

https://bugs.mysql.com/bug.php?id=85816

We have encountered such issues internally and are recommending against using Connector/J 8.

In cases where the exception occurs with the 8 driver, version 5 drivers owrk fine.

Cloudera is looking at documenting this publicly and considering how to address the issue long term.

[#3]

@bgooley

Thank you so much for the prompt reply and also confirming that lower version of jdbc connector works. I tried that too. This time It was able to pass that step as below.

/usr/share/cmf/schema/scm_prepare_database.sh mysql -h mysqlserver.domain.com --scm-host cdhmgr.domain.com scm scm
Enter SCM password: 
JAVA_HOME=/usr/java/latest
Verifying that we can write to /etc/cloudera-scm-server
Creating SCM configuration file in /etc/cloudera-scm-server
Executing:  /usr/java/latest/bin/java -cp /usr/share/java/mysql-connector-java.jar:/usr/share/java/oracle-connector-java.jar:/usr/share/cmf/schema/../lib/* com.cloudera.enterprise.dbutil.DbCommandExecutor /etc/cloudera-scm-server/db.properties com.cloudera.cmf.db.
Sun Jul 22 04:38:14 EDT 2018 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
[                          main] DbCommandExecutor              INFO  Successfully connected to database.
All done, your SCM database is configured correctly!

However systemctl start cloudera-scm-server is not starting and in the log I see this.

2018-07-20 08:13:24,453 INFO main:com.cloudera.server.cmf.Main: ================================================================================
2018-07-20 08:13:24,468 INFO main:com.cloudera.server.cmf.Main: Starting SCM Server. JVM Args: [-Dlog4j.configuration=file:/etc/cloudera-scm-server/log4j.properties, -Dfile.encoding=UTF-8, -Dcmf.root.logger=INFO,LOGFILE, -Dcmf.log.dir=/var/log/cloudera-scm-server, -Dcmf.log.file=cloudera-scm-server.log, -Dcmf.jetty.threshhold=WARN, -Dcmf.schema.dir=/usr/share/cmf/schema, -Djava.awt.headless=true, -Djava.net.preferIPv4Stack=true, -Dpython.home=/usr/share/cmf/python, -XX:+UseConcMarkSweepGC, -XX:+UseParNewGC, -XX:+HeapDumpOnOutOfMemoryError, -Xmx2G, -XX:MaxPermSize=256m, -XX:+HeapDumpOnOutOfMemoryError, -XX:HeapDumpPath=/tmp, -XX:OnOutOfMemoryError=kill -9 %p], Args: [], Version: 5.14.3 (#4 built by jenkins on 20180414-0409 git: 7f2725eea4edeb1d184a2888db790d332167b6f8)
2018-07-20 08:13:24,567 INFO main:org.mortbay.log: Logging to org.slf4j.impl.Log4jLoggerAdapter(org.mortbay.log) via org.mortbay.log.Slf4jLog
2018-07-20 08:13:24,688 INFO main:org.springframework.context.support.ClassPathXmlApplicationContext: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@528931cf: startup date [Fri Jul 20 08:13:24 EDT 2018]; root of context hierarchy
2018-07-20 08:13:24,747 INFO main:org.springframework.beans.factory.xml.XmlBeanDefinitionReader: Loading XML bean definitions from class path resource [webapp/WEB-INF/spring/beanRefFactory.xml]
2018-07-20 08:13:25,165 INFO main:org.springframework.beans.factory.support.DefaultListableBeanFactory: Pre-instantiating singletons in org.springframework.beans.factory.support.DefaultListableBeanFactory@4afcd809: defining beans [bootstrapContext,rootContext]; root of factory hierarchy
2018-07-20 08:13:25,194 INFO main:org.springframework.context.support.GenericApplicationContext: Refreshing org.springframework.context.support.GenericApplicationContext@1f2586d6: startup date [Fri Jul 20 08:13:25 EDT 2018]; root of context hierarchy
2018-07-20 08:13:25,195 INFO main:org.springframework.beans.factory.support.DefaultListableBeanFactory: Pre-instantiating singletons in org.springframework.beans.factory.support.DefaultListableBeanFactory@7c9d8e2: defining beans [commandLineConfigurationBean,entityManagerFactoryBean,com.cloudera.server.cmf.TrialState,com.cloudera.server.cmf.TrialManager,com.cloudera.cmf.crypto.LicenseLoader]; root of factory hierarchy
2018-07-20 08:13:25,271 INFO main:com.cloudera.enterprise.CommonMain: Reading database properties from /etc/cloudera-scm-server/db.properties
2018-07-20 08:13:25,275 INFO main:com.cloudera.enterprise.CommonMain: Statistics not enabled, c3p0 JMX disabled
2018-07-20 08:13:25,423 INFO main:org.hibernate.annotations.common.Version: HCANN000001: Hibernate Commons Annotations {4.0.2.Final}
2018-07-20 08:13:25,428 INFO main:org.hibernate.Version: HHH000412: Hibernate Core {4.2.2.Final}
2018-07-20 08:13:25,431 INFO main:org.hibernate.cfg.Environment: HHH000206: hibernate.properties not found
2018-07-20 08:13:25,432 INFO main:org.hibernate.cfg.Environment: HHH000021: Bytecode provider name : javassist
2018-07-20 08:13:25,743 INFO main:org.hibernate.cfg.Configuration: HHH000221: Reading mappings from resource: cmf.hbm.xml
2018-07-20 08:13:25,977 INFO main:org.hibernate.service.jdbc.connections.internal.ConnectionProviderInitiator: HHH000130: Instantiating explicit connection provider: org.hibernate.service.jdbc.connections.internal.C3P0ConnectionProvider
2018-07-20 08:13:25,978 INFO main:org.hibernate.service.jdbc.connections.internal.C3P0ConnectionProvider: HHH010002: C3P0 using driver: com.mysql.jdbc.Driver at URL: jdbc:mysql://mysqlserver.domain.com/scm?useUnicode=true&characterEncoding=UTF-8
2018-07-20 08:13:25,978 INFO main:org.hibernate.service.jdbc.connections.internal.C3P0ConnectionProvider: HHH000046: Connection properties: {user=scm, password=****, autocommit=true, release_mode=auto}
2018-07-20 08:13:25,978 INFO main:org.hibernate.service.jdbc.connections.internal.C3P0ConnectionProvider: HHH000006: Autocommit mode: true
2018-07-20 08:13:25,980 INFO main:org.springframework.beans.factory.support.DefaultListableBeanFactory: Destroying singletons in org.springframework.beans.factory.support.DefaultListableBeanFactory@7c9d8e2: defining beans [commandLineConfigurationBean,entityManagerFactoryBean,com.cloudera.server.cmf.TrialState,com.cloudera.server.cmf.TrialManager,com.cloudera.cmf.crypto.LicenseLoader]; root of factory hierarchy
2018-07-20 08:13:25,981 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 javax.persistence.PersistenceException: [PersistenceUnit: cmf.server] Unable to build EntityManagerFactory
	at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveReference(BeanDefinitionValueResolver.java:328)
	at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveValueIfNecessary(BeanDefinitionValueResolver.java:106)
	at org.springframework.beans.factory.support.ConstructorResolver.resolveConstructorArguments(ConstructorResolver.java:616)
	at org.springframework.beans.factory.support.ConstructorResolver.autowireConstructor(ConstructorResolver.java:148)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.autowireConstructor(AbstractAutowireCapableBeanFactory.java:1003)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:907)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:485)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:456)
	at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:293)
	at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)
	at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:290)
	at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:192)
	at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:585)
	at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:895)
	at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:425)
	at com.cloudera.server.cmf.Main.bootstrapSpringContext(Main.java:393)
	at com.cloudera.server.cmf.Main.<init>(Main.java:243)
	at com.cloudera.server.cmf.Main.main(Main.java:216)
Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactoryBean': FactoryBean threw exception on object creation; nested exception is javax.persistence.PersistenceException: [PersistenceUnit: cmf.server] Unable to build EntityManagerFactory
	at org.springframework.beans.factory.support.FactoryBeanRegistrySupport.doGetObjectFromFactoryBean(FactoryBeanRegistrySupport.java:149)
	at org.springframework.beans.factory.support.FactoryBeanRegistrySupport.getObjectFromFactoryBean(FactoryBeanRegistrySupport.java:102)
	at org.springframework.beans.factory.support.AbstractBeanFactory.getObjectForBeanInstance(AbstractBeanFactory.java:1440)
	at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:247)
	at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:192)
	at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveReference(BeanDefinitionValueResolver.java:322)
	... 17 more
Caused by: javax.persistence.PersistenceException: [PersistenceUnit: cmf.server] Unable to build EntityManagerFactory
	at org.hibernate.ejb.Ejb3Configuration.buildEntityManagerFactory(Ejb3Configuration.java:924)
	at org.hibernate.ejb.Ejb3Configuration.buildEntityManagerFactory(Ejb3Configuration.java:899)
	at org.hibernate.ejb.HibernatePersistence.createEntityManagerFactory(HibernatePersistence.java:59)
	at javax.persistence.Persistence.createEntityManagerFactory(Persistence.java:63)
	at com.cloudera.enterprise.CommonMain.setupHibernate(CommonMain.java:183)
	at com.cloudera.server.cmf.bootstrap.EntityManagerFactoryBean.getObject(EntityManagerFactoryBean.java:114)
	at com.cloudera.server.cmf.bootstrap.EntityManagerFactoryBean.getObject(EntityManagerFactoryBean.java:65)
	at org.springframework.beans.factory.support.FactoryBeanRegistrySupport.doGetObjectFromFactoryBean(FactoryBeanRegistrySupport.java:142)
	... 22 more
Caused by: org.hibernate.service.classloading.spi.ClassLoadingException: HHH010003: JDBC Driver class not found: com.mysql.jdbc.Driver
	at org.hibernate.service.jdbc.connections.internal.C3P0ConnectionProvider.configure(C3P0ConnectionProvider.java:142)
	at org.hibernate.service.internal.StandardServiceRegistryImpl.configureService(StandardServiceRegistryImpl.java:75)
	at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:159)
	at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:131)
	at org.hibernate.engine.jdbc.internal.JdbcServicesImpl.buildJdbcConnectionAccess(JdbcServicesImpl.java:223)
	at org.hibernate.engine.jdbc.internal.JdbcServicesImpl.configure(JdbcServicesImpl.java:89)
	at org.hibernate.service.internal.StandardServiceRegistryImpl.configureService(StandardServiceRegistryImpl.java:75)
	at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:159)
	at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:131)
	at org.hibernate.cfg.Configuration.buildTypeRegistrations(Configuration.java:1797)
	at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1755)
	at org.hibernate.ejb.EntityManagerFactoryImpl.<init>(EntityManagerFactoryImpl.java:96)
	at org.hibernate.ejb.Ejb3Configuration.buildEntityManagerFactory(Ejb3Configuration.java:914)
	... 29 more
Caused by: org.hibernate.service.classloading.spi.ClassLoadingException: Unable to load class [com.mysql.jdbc.Driver]
	at org.hibernate.service.classloading.internal.ClassLoaderServiceImpl.classForName(ClassLoaderServiceImpl.java:149)
	at org.hibernate.service.jdbc.connections.internal.C3P0ConnectionProvider.configure(C3P0ConnectionProvider.java:139)
	... 41 more
Caused by: java.lang.ClassNotFoundException: Could not load requested class : com.mysql.jdbc.Driver
	at org.hibernate.service.classloading.internal.ClassLoaderServiceImpl$AggregatedClassLoader.findClass(ClassLoaderServiceImpl.java:296)
	at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
	at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
	at java.lang.Class.forName0(Native Method)
	at java.lang.Class.forName(Class.java:348)
	at org.hibernate.service.classloading.internal.ClassLoaderServiceImpl.classForName(ClassLoaderServiceImpl.java:146)
	... 42 more

[#4]

# systemctl status cloudera-scm-server 

cloudera-scm-server.service - LSB: Cloudera SCM Server Loaded: 

loaded (/etc/rc.d/init.d/cloudera-scm-server; bad; vendor preset: disabled) 
Active: failed (Result: exit-code) since Mon 2018-07-23 09:27:48 EDT; 6min ago 
Docs: man:systemd-sysv-generator(8) 
Process: 1036 ExecStart=/etc/rc.d/init.d/cloudera-scm-server start (code=exited, status=1/FAILURE) 
Jul 23 09:27:43 cdhmgr.domain.com systemd[1]: Starting LSB: Cloudera SCM Server... 
Jul 23 09:27:43 cdhmgr.domain.com su[1138]: (to cloudera-scm) root on none 
Jul 23 09:27:48 cdhmgr.domain.com cloudera-scm-server[1036]: Starting cloudera-scm-server: [FAILED] 
Jul 23 09:27:48 cdhmgr.domain.com systemd[1]: cloudera-scm-server.service: control process exited, code=exited status=1 
Jul 23 09:27:48 cdhmgr.domain.com systemd[1]: Failed to start LSB: Cloudera SCM Server. 
Jul 23 09:27:48 cdhmgr.domain.com systemd[1]: Unit cloudera-scm-server.service entered failed state. 
Jul 23 09:27:48 cdhmgr.domain.com systemd[1]: cloudera-scm-server.service failed.

 Some where in the doc I found that it can be because selinux is running. But in my case it is in Disabled state also firewalld is not running.

[#5]

@Anudas,

Since the error shows that the "JDBC Driver class not found: com.mysql.jdbc.Driver" that means there must be something wrong with permissions or otherwise.

In /etc/default/cloudera-scm-server there is the following configuration by default:

export CMF_JDBC_DRIVER_JAR="/usr/share/java/mysql-connector-java.jar:/usr/share/java/oracle-connector-java.jar"

So, let's make sure that permissions are such that the cloudera-scm user can access that JAR.

Run:

ls -la /usr/share/java

See if permissions allow the cloudera-scm user access.

Since you ran scm_prepare_database.sh as root, that might explain why that worked but CM's JVM cannot locate the class.

[#6]

@bgooley

ls -ls /usr/share/java/
total 984
984 -rw-r--r-- 1 root root 1004840 Jul 22 04:29 mysql-connector-java.jar

However when I checked parent folder I found that java folder was not having proper permissions.

# cd /usr/share/
# ls -ltr | grep java
drwx------    2 root root    38 Jul 20 05:34 java

# chmod 755 java

# ls -ltr | grep java
drwxr-xr-x    2 root root    38 Jul 20 05:34 java

Now I am able to start cloudera manager . Thanks a lot for the help !

One more query .. how to migrate the data to the external mysql DB ? After starting cloudera manager when I access the GUI what I see the fresh page of CM which asks for the nodes to add to the cluster. But I already have the CDH cluster configured with nodes and assigned services/roles . This data is there in the embedded postgres DB. How to migrate this data from embedded postgres to external mysql ?

[#7]

@Anudas,

Migration from the embedded database to mysql is a bit of an undertaking.  Cloudera does not have public documentation explaining how to do this currently.  If you are OK with migrating to an external postgres, consider doing so as it is much simpler and documented:

https://www.cloudera.com/documentation/enterprise/latest/topics/cm_ag_migrate_postgres_db.html

If you are using the embedded database for CM, it is likely you are also using the embedded database for your other roles like Hive Metastore, Sentry, Hue, Oozie etc.  Each has its own method of migrating.

For CM, the basics are to

- export your deployment descriptor from your current database (via CM)

- prepare the mysql db (as you have done)

- import the deployment descriptor you exported into CM backed by your mysql db

- remove the cloudera-manager-server-db-2 package

Have you considered whether you need existing database information for other services?  My suggestion is to identify what database information you can do without and start clean if possible.  If not, this message board can help with the CM migration, but for other services you should consult other community boards to get the best tips.

If you are still interested in trying the mysql migration, I can post some basic steps to try.

[#8]

Someone might find below link useful. I used it when I had to migrate MySQL to different machines.

https://gist.github.com/bugcy013/91cd7767e5306d1703e5

Regards,

Nic

[#9]

@nicusanThis looks promising. I shall take a look for sure.

@bgooleyIs there any way to retrieve the scm password for the embedded postgres DB ? Without taking the backup of db.properties file I had ran the script which configured external mysql DB. Now db.properties only shows external mysql DB related info . I am not sure if scm_prepare_database.sh script took the backup of old db.properties file somewhere in the system.

[#10]

@bgooleyYeah, I am interested. Please let me know those steps.