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.

Cloudera Manager fail to start all of Monitor, Exception while executing ddl scripts

Cloudera Manager fail to start all of Monitor, Exception while executing ddl scripts

New Contributor

Just through the cloud manager 0.8.2 deployed cdh4, when i start mgmt1 through cm web console, the ServiceMonitor, ActivityMonitor, and HostMonitor fail to start, logs print :

2014-03-11 19:50:36,373 FATAL org.hsqldb.cmdline.SqlFile: SQL Error at 'UTF-8' line 1: "ALTER TABLE PARTITION_INFO ADD COLUMN BEGIN_TS BIGINT(20)" Duplicate column name 'BEGIN_TS'

2014-03-11 19:50:36,374 ERROR com.cloudera.enterprise.dbutil.SqlFileRunner: Exception while executing ddl scripts.

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Duplicate column name 'BEGIN_TS'

                         at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

                         ....

All of the same error for all monitor, this puzzled me, I do not know why?

12 REPLIES 12

Re: Cloudera Manager fail to start all of Monitor, Exception while executing ddl scripts

Hi,

 

Did you configure all of the services to use distinct databases?

 

Be sure to follow the documentation for configuring mysql itself as well as setting up databases:

http://www.cloudera.com/content/cloudera-content/cloudera-docs/CM4Ent/latest/Cloudera-Manager-Instal...

 

Thanks,

Darren

Re: Cloudera Manager fail to start all of Monitor, Exception while executing ddl scripts

New Contributor

tks,

I configure all of the services to use the same mysql,then build amon,smon, hmon e.t.
I follow the documentation for configuring mysql to do it again, and replaced with a new mysql , the same exception occurs

 

Because of the time , I select the Installation Path A - Automated Installation by Cloudera Manager, Automated installation automatically creates embedded PostgreSQL databases for all services.  It works!

Re: Cloudera Manager fail to start all of Monitor, Exception while executing ddl scripts

Explorer

I've had a similar issue happen just now with CDH manager for 4.8.2 using MySQL for a database.

 

I followed the steps for configuring the mysql instance and used separate databases for each service.  The services that failed to start are hostmonitor, servicemonitor, and activity monitor.

 

Looking at the logs for repeat tries, it appears that perhaps the schema version that is installed (11) is incorrect - perhaps it should be a different version as the partition_info table already has a BEGIN_TS field defined as a bigint(20).

 

is there anyway to see what schema version is created vs what the service is expecting when it starts up?

 

Here's what the table(s) look like:

 

mysql> desc `PARTITION_INFO`;

+------------+-------------+------+-----+---------+----------------+

| Field      | Type        | Null | Key | Default | Extra          |

+------------+-------------+------+-----+---------+----------------+

| ID         | bigint(20)  | NO   | PRI | NULL    | auto_increment |

| NAME       | varchar(80) | NO   | UNI | NULL    |                |

| BASE_TABLE | varchar(80) | NO   |     | NULL    |                |

| END_TS     | bigint(20)  | NO   |     | NULL    |                |

| BEGIN_TS   | bigint(20)  | YES  |     | NULL    |                |

| TYPE       | int(11)     | NO   |     | NULL    |                |

+------------+-------------+------+-----+---------+----------------+

6 rows in set (0.00 sec)

 

mysql> desc `CMON_SCHEMA_VERSION`;

+---------+---------+------+-----+---------+-------+

| Field   | Type    | Null | Key | Default | Extra |

+---------+---------+------+-----+---------+-------+

| VERSION | int(11) | NO   |     | NULL    |       |

+---------+---------+------+-----+---------+-------+

1 row in set (0.01 sec)

 

mysql> select * from `CMON_SCHEMA_VERSION`;

+---------+

| VERSION |

+---------+

|      11 |

+---------+

1 row in set (0.00 sec)

 

mysql> 

 

 

Re: Cloudera Manager fail to start all of Monitor, Exception while executing ddl scripts

Hi Phil,

These services create their schemas when first started. This problem can occur if the databases you provided are not empty, or if multiple services are sharing the same database.

Try creating a brand new database for one role, configuring it, starting it, then seeing if that works.

Thanks,
Darren

Re: Cloudera Manager fail to start all of Monitor, Exception while executing ddl scripts

Explorer

Yeah - that doesn't seem to work.  I dropped the smon database, enabled the mysql general log and restarted the service, and though it created all the tables, it still failed with the same issue:

 

mysql> drop database smon;

Query OK, 14 rows affected (0.31 sec)

 

mysql> create database smon DEFAULT CHARACTER SET utf8;

Query OK, 1 row affected (0.00 sec)

 

mysql> use smon

Database changed

mysql> show tables;

Empty set (0.00 sec)

 

<<  STARTED SMON SERVICE HERE >>

 

mysql> show tables;

+-------------------------------+

| Tables_in_smon                |

+-------------------------------+

| CMON_ACTIVITY                 |

| CMON_ACTIVITY_ATTRIBUTE       |

| CMON_ACTIVITY_DP              |

| CMON_ACTIVITY_LAST_METRIC_VAL |

| CMON_ATTEMPT                  |

| CMON_ATTEMPT_LAST_METRIC_VAL  |

| CMON_LL_DP                    |

| CMON_LL_DP_2014_03_19         |

| CMON_LL_DP_HR                 |

| CMON_LL_TIME_SERIES_ATTRIBUTE |

| CMON_LL_TIME_SERIES_ID        |

| CMON_METRIC_INFO              |

| CMON_SCHEMA_VERSION           |

| PARTITION_INFO                |

+-------------------------------+

14 rows in set (0.00 sec)

 

Error: 

 

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Duplicate column name 'BEGIN_TS'
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
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.ConnectionImpl.execSQL(ConnectionImpl.java:2569)
at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:813)
at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:656)
at org.hsqldb.cmdline.SqlFile.processSQL(Unknown Source)
at org.hsqldb.cmdline.SqlFile.scanpass(Unknown Source)
at org.hsqldb.cmdline.SqlFile.execute(Unknown Source)
at com.cloudera.enterprise.dbutil.SqlFileRunner.runSqlFile(SqlFileRunner.java:68)
... 6 more

 

Looking at the MySQL general log, it appears the startup runs the alter table multiple times: 

 

...

                460 Query     update CMON_SCHEMA_VERSION set VERSION=6

...

                461 Query     CREATE TABLE CMON_PARTITION_INFO (

  ID bigint NOT NULL AUTO_INCREMENT,

  NAME varchar(80) NOT NULL,

  BASE_TABLE varchar(80) NOT NULL,

  END_TS bigint NOT NULL,

  PRIMARY KEY (ID)

) ENGINE=InnoDB

...

                474 Query     update CMON_SCHEMA_VERSION set VERSION=11

...

                475 Query     ALTER TABLE PARTITION_INFO ADD COLUMN BEGIN_TS BIGINT(20)

...

                486 Query     ALTER TABLE PARTITION_INFO ADD COLUMN BEGIN_TS BIGINT(20)

...

 

Re: Cloudera Manager fail to start all of Monitor, Exception while executing ddl scripts

Can you repeat this, but instead of restarting the whole service, recreate the db for a single role and restart that single role, one by one?

Re: Cloudera Manager fail to start all of Monitor, Exception while executing ddl scripts

Explorer

I tried with each of the hmon, smon, and amon services, and in each case they failed with the same issue.

 

I went through all the scripts in /usr/share/cmf/schema/mysql/firehose, and found some issues in 00007 and 00012 around renames of table (likely due to the oracle constraints) and a couple where the alter tables didn't work with existing foreign keys, and one issue where there appeard to be missing comments.

 

Making those fixes, I was able to get amon working fine, but both smon and hmon still fail it seems with issues creating the initial partitioned tables for  CMON_LL_DP. (see log entries below)

 

patch for 00007_cmon_schema.mysql.ddl:
 
--- 00007_cmon_schema.mysql.ddl 2014-03-20 13:13:55.797696430 -0700
+++ 00007_cmon_schema.mysql.ddl.new     2014-03-20 13:13:36.252976729 -0700
@@ -22,7 +22,9 @@
 
 -- Tables used for partition have YYYY_MM_DD suffix'ed to them, so we need to
 -- rename them too so that overall name doesn't exceed 30 chars.
-com.cloudera.cmon.firehose.db.ShortenMySQLTableNames
+/* PRH */
+-- com.cloudera.cmon.firehose.db.ShortenMySQLTableNames
+/* PRH */
 
 -- Rename all the tables having more than 30 characters to bring them within
 -- limit. Then to maintain consistent naming scheme, we rename all other tables
@@ -32,6 +34,12 @@
 RENAME TABLE CMON_LONG_LIVED_TIME_SERIES_ATTRIBUTE TO CMON_LL_TIME_SERIES_ATTRIBUTE;
 RENAME TABLE CMON_LONG_LIVED_TIME_SERIES_ID TO CMON_LL_TIME_SERIES_ID;
 RENAME TABLE CMON_ACTIVITY_DATA_POINTS TO CMON_ACTIVITY_DP;
+/* PRH */
+RENAME TABLE CMON_LONG_LIVED_DATA_POINTS TO CMON_LL_DP;
+RENAME TABLE CMON_LONG_LIVED_DATA_POINTS_HOURLY TO CMON_LL_DP_HR;
+RENAME TABLE CMON_ACTIVITY_LAST_METRIC_VAL TO CMON_ACT_LMV;
+RENAME TABLE CMON_ATTEMPT_LAST_METRIC_VAL TO CMON_ATT_LMV;
+/* PRH */
 
 --
 -- Add foreign key constraint to CMON_LONG_LIVED_DATA_POINTS that inadvertently
 
patch for 00012_cmon_schema.mysql.ddl:
 
--- 00012_cmon_schema.mysql.ddl 2014-03-20 13:19:35.222194452 -0700
+++ 00012_cmon_schema.mysql.ddl.new     2014-03-20 13:18:43.804301132 -0700
@@ -19,7 +19,19 @@
 -- For some reason this does not use a sequence. Let's fix it as it will
 -- make our life when creating partitions easier.
 
+/* PRH */
+
+ALTER TABLE CMON_ATTEMPT DROP FOREIGN KEY `FK_ATTEMPT_ACTIVITY`;
+ALTER TABLE CMON_ATT_LMV DROP FOREIGN KEY `FK_ATTEMPT_LMV_ATTEMPT`;
+
+/* PRH */
+
 ALTER TABLE CMON_ATTEMPT MODIFY COLUMN ID BIGINT(20) NOT NULL AUTO_INCREMENT;
 
+/* PRH */
+ALTER TABLE CMON_ATTEMPT ADD CONSTRAINT `FK_ATTEMPT_ACTIVITY` FOREIGN KEY (`ACTIVITY_ID`) REFERENCES `CMON_ACTIVITY` (`ID`) ON DELETE CASCADE;
+ALTER TABLE CMON_ATT_LMV ADD CONSTRAINT `FK_ATTEMPT_LMV_ATTEMPT` FOREIGN KEY (`ATTEMPT_ID`) REFERENCES `CMON_ATTEMPT` (`ID`) ON DELETE CASCADE;
+/* PRH */
+
 -- Partition old data if necessary.
-com.cloudera.cmon.firehose.db.PartitionOldAmonData
+-- PRH com.cloudera.cmon.firehose.db.PartitionOldAmonData

 

errors:

 

1:24:49.740 PM WARN org.hibernate.engine.jdbc.spi.SqlExceptionHelper
SQL Error: 1146, SQLState: 42S02
1:24:49.740 PM ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper
Table 'hmon.CMON_LL_DP_2014_03_20' doesn't exist
1:24:49.744 PM ERROR com.cloudera.cmon.tstore.db.DbTimeSeriesStore
Exception writing timeseries
org.hibernate.exception.SQLGrammarException: Table 'hmon.CMON_LL_DP_2014_03_20' doesn't exist
at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:122)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110)
at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:129)
at org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81)
at $Proxy9.executeBatch(Unknown Source)
at com.cloudera.cmon.tstore.db.TsEntityManager$2.execute(TsEntityManager.java:666)
at org.hibernate.jdbc.WorkExecutor.executeWork(WorkExecutor.java:54)
at org.hibernate.internal.SessionImpl$2.accept(SessionImpl.java:1937)
at org.hibernate.internal.SessionImpl$2.accept(SessionImpl.java:1934)
at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.coordinateWork(JdbcCoordinatorImpl.java:211)
at org.hibernate.internal.SessionImpl.doWork(SessionImpl.java:1955)
at org.hibernate.internal.SessionImpl.doWork(SessionImpl.java:1941)
at com.cloudera.cmon.tstore.db.TsEntityManager.persistTimeSeriesPointsWithJdbc(TsEntityManager.java:604)
at com.cloudera.cmon.tstore.db.TsEntityManager.persistWithJdbc(TsEntityManager.java:716)
at com.cloudera.cmon.tstore.db.TsEntityManager.persistWithJdbc(TsEntityManager.java:726)
at com.cloudera.cmon.tstore.db.DbTimeSeriesStore.write(DbTimeSeriesStore.java:222)
at com.cloudera.cmon.tstore.CachingTimeSeriesStoreImpl.write(CachingTimeSeriesStoreImpl.java:691)
at com.cloudera.cmon.tstore.CachingTimeSeriesStoreImpl.write(CachingTimeSeriesStoreImpl.java:654)
at com.cloudera.cmon.tstore.AggregatingTimeSeriesStore.write(AggregatingTimeSeriesStore.java:228)
at com.cloudera.cmon.kaiser.TimeSeriesHelper.insertInternalMetrics(TimeSeriesHelper.java:271)
at com.cloudera.cmon.firehose.Firehose.insertStartupMetrics(Firehose.java:396)
at com.cloudera.cmon.firehose.Firehose.<init>(Firehose.java:200)
at com.cloudera.cmon.firehose.Main.main(Main.java:368)
Caused by: java.sql.BatchUpdateException: Table 'hmon.CMON_LL_DP_2014_03_20' doesn't exist
at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:2024)
at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1449)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeBatch(NewProxyPreparedStatement.java:1723)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:122)
... 20 more

 

Re: Cloudera Manager fail to start all of Monitor, Exception while executing ddl scripts

Explorer

Any help on this issue.

 

7:10:18.951 AMWARNorg.hibernate.cache.ehcache.AbstractEhcacheRegionFactory
HHH020003: Could not find a specific ehcache configuration for cache named [org.hibernate.cache.internal.StandardQueryCache]; using defaults.
7:10:18.981 AMINFOorg.hibernate.validator.internal.util.Version
HV000001: Hibernate Validator 5.0.1.Final
7:10:19.665 AMINFOcom.cloudera.enterprise.dbutil.DatabaseManager
MYSQL database engine and table mapping: {InnoDB=[CMON_METRIC_INFO, CMON_ACTIVITY, CMON_ACTIVITY_LMV_45_DATA, CMON_ATT_LMV, PARTITION_INFO, CMON_ACTIVITY_DP_45_DATA, CMON_ACTIVITY_ATTRIBUTE, CMON_LL_TIME_SERIES_ID, CMON_ATTEMPT_LMV_45_DATA, CMON_LL_TIME_SERIES_ATTRIBUTE, CMON_ATTEMPT_45_DATA, CMON_LL_DP_2015_01_28, CMON_LL_DP_HR, CMON_LL_DP, CMON_SCHEMA_VERSION]}
7:10:19.680 AMINFOcom.cloudera.enterprise.dbutil.DbUtil
Schema version table already exists.
7:10:19.680 AMINFOcom.cloudera.enterprise.dbutil.DbUtil
DB Schema version 11.
7:10:19.681 AMINFOcom.cloudera.enterprise.dbutil.DbUtil
Current database schema version: 11
7:10:19.760 AMFATALorg.hsqldb.cmdline.SqlFile
SQL Error at 'UTF-8' line 1:
"ALTER TABLE PARTITION_INFO ADD COLUMN BEGIN_TS BIGINT(20)"
Duplicate column name 'BEGIN_TS'
7:10:19.760 AMERRORcom.cloudera.enterprise.dbutil.SqlFileRunner
Exception while executing ddl scripts.
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Duplicate column name 'BEGIN_TS'
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)

Re: Cloudera Manager fail to start all of Monitor, Exception while executing ddl scripts

DDL issues like this are usually because you've configured different management daemons to use the same database. CM server and each of the management daemons that use a database should all have distinct databases. They can be on the same database server but the user name and/or "db name"/"schema" must be distinct so table names don't collide.