.MySQLSyntaxErrorException: Key column 'REVISION_ID' doesn't exist in table While Starting Cloudera

[#1]

Hello All,

I am trying to bootstrap using cloudera director.

CDH Version is 5.13

Maria DB Server version: 10.2.15

I am getting the bleow exception:

2018-07-05 17:48:40,291 INFO main:com.cloudera.enterprise.dbutil.JavaRunner: Processed Java class: com.cloudera.cmf.model.migration.MySQLFixRunningProcesses
2018-07-05 17:48:40,305 INFO main:com.cloudera.enterprise.dbutil.SqlScriptRunnerFactory$SqlStringRunner: Processed: Updated Schema Version to 4606
2018-07-05 17:48:40,348 INFO main:com.cloudera.enterprise.dbutil.SqlScriptRunnerFactory$SqlStringRunner: Processed: /usr/share/cmf/schema/mysql/04700_cmf_schema.mysql.ddl
2018-07-05 17:48:40,362 INFO main:com.cloudera.enterprise.dbutil.SqlScriptRunnerFactory$SqlStringRunner: Processed: Updated Schema Version to 4700
2018-07-05 17:48:40,382 INFO main:com.cloudera.enterprise.dbutil.SqlScriptRunnerFactory$SqlStringRunner: Processed: /usr/share/cmf/schema/mysql/04701_cmf_schema.mysql.ddl
2018-07-05 17:48:40,399 INFO main:com.cloudera.enterprise.dbutil.SqlScriptRunnerFactory$SqlStringRunner: Processed: Updated Schema Version to 4701
2018-07-05 17:48:40,423 INFO main:com.cloudera.enterprise.dbutil.SqlScriptRunnerFactory$SqlStringRunner: Processed: /usr/share/cmf/schema/mysql/04800_cmf_schema.mysql.ddl
2018-07-05 17:48:40,436 INFO main:com.cloudera.enterprise.dbutil.SqlScriptRunnerFactory$SqlStringRunner: Processed: Updated Schema Version to 4800
2018-07-05 17:48:40,496 INFO main:com.cloudera.enterprise.dbutil.SqlScriptRunnerFactory$SqlStringRunner: Processed: /usr/share/cmf/schema/mysql/05000_cmf_schema.mysql.ddl
2018-07-05 17:48:40,508 INFO main:com.cloudera.enterprise.dbutil.SqlScriptRunnerFactory$SqlStringRunner: Processed: Updated Schema Version to 5000
2018-07-05 17:48:40,679 INFO main:com.cloudera.enterprise.dbutil.SqlScriptRunnerFactory$SqlStringRunner: Processed: /usr/share/cmf/schema/mysql/05001_cmf_schema.mysql.ddl
2018-07-05 17:48:40,695 INFO main:com.cloudera.enterprise.dbutil.SqlScriptRunnerFactory$SqlStringRunner: Processed: Updated Schema Version to 5001
2018-07-05 17:48:40,737 INFO main:com.cloudera.enterprise.dbutil.JavaRunner: Processed Java class: com.cloudera.cmf.model.migration.MigrateConfigRevisions
2018-07-05 17:48:40,752 INFO main:com.cloudera.enterprise.dbutil.SqlScriptRunnerFactory$SqlStringRunner: Processed: Updated Schema Version to 5002
2018-07-05 17:48:40,901 FATAL main:org.hsqldb.cmdline.SqlFile: SQL Error at 'UTF-8' line 57:
"alter table ROLE_CONFIG_GROUPS
    drop column REVISION_ID"
Key column 'REVISION_ID' doesn't exist in table
2018-07-05 17:48:40,901 FATAL main:org.hsqldb.cmdline.SqlFile: Rolling back SQL transaction.
2018-07-05 17:48:40,903 ERROR main:com.cloudera.enterprise.dbutil.SqlFileRunner: Exception while executing ddl scripts.
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Key column 'REVISION_ID' doesn't exist in table
        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:411)
        at com.mysql.jdbc.Util.getInstance(Util.java:386)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4187)

I saw the supporting db version with cdh in below url: 

https://www.cloudera.com/documentation/enterprise/release-notes/topics/rn_consolidated_pcm.html#cdh_...

Is that the issue of version compatability ? some thing else ? If its a version compatability issue, is that support version is 10.x or only 10.0.x? 

Cheers,

Sankaranarayanan S.

[#2]

Hello ssankarau,

I did a little searching internally and it appears that this problem has been seen before when using Cloudera Manager with MariaDB 10.2.8 and higher. There is a workaround that involves editing a file CM uses to construct the schema in its database, named 05003_cmf_schema.mysql.ddl:

.... 
alter table CONFIGS 
drop column REVISION_ID; 

ALTER TABLE ROLE_CONFIG_GROUPS DROP INDEX IDX_UNIQUE_ROLE_CONFIG_GROUP; 
ALTER TABLE ROLE_CONFIG_GROUPS DROP INDEX IDX_ROLE_CONFIG_GROUP_CONFIG_REVISION; 

alter table ROLE_CONFIG_GROUPS 
drop column REVISION_ID; 
....

Adding the two "DROP INDEX" lines as shown above should allow the file to be executed successfully. Supposedly the statements should not be necessary according to the semantics of DROP COLUMN; perhaps there is some issue with MariaDB.

Unfortunately, Director does not have a script hook available to apply this workaround automatically as part of cluster bootstrap. You could try creating an image with CM already installed and with the workaround in place, so that Director will simply start CM instead of trying to install it fresh.

I'm going to assign this question over to the Cloudera Manager team to make sure they see that another instance of this problem has occurred. Thank you!

[#3]

Downgraded the MariaDB Version to 5.5 solved the problem.!

[#4]

MariaDB 10.2.8 intentionally introduced compatibility-breaking changes prohibiting dropping columns that are part of composite constraints.  While the behavioral change is sound with respect to SQL standards, the change of behavior in a maintenance release causes problems.  I've registered my concerns with this practice in a blog post and in the relevant MariaDB JIRA.

MariaDB 10.2 is not currently supported, so downgrading to a supported version is an appropriate path forward.  Even if 10.2 were tested and certified by Cloudera, however, compatibility-breaking changes made by MariaDB in maintenance releases - as was done here - will cause problems that very well may not be caught in certification testing.