DB Inconsistency Issues with HDP 2.6

[#1]

I had a DB inconsistency error while restarting ambari-server and had to skip database check. The links and ambari updates for the services are not showing anymore. I run ambari 2.5.0.3 on HDP 2.6. Anyone know how to fix this?

ERROR - Found tables with engine type that is not InnoDB : [DS_FILERESOURCEITEM_11, DS_FILERESOURCEITEM_5, DS_JOBIMPL_15, DS_JOBIMPL_8, DS_SAVEDQUERY_12, DS_SAVEDQUERY_9, DS_SETTING_14, DS_TESTBEAN_13, DS_TESTBEAN_6, DS_UDF_16, DS_UDF_7]

[#2]

@Joshua Adeleke

Yes, it looks like an issue looks related to: https://issues.apache.org/jira/browse/AMBARI-18951

https://github.com/apache/ambari/commit/80084a9d8de9110365c6f1ef68362b502e07cf28

.

Prior to Ambaei 2.5 some of the DB tables for MySQL might be using the "MyISAM" engin instead of "InnoDB".

As a work around you can try the following approach to fix the issue.

# Collect the Ambari DB Dump.
mysqldump -U ambari -p ambari > /PATH/TO/ambari.original.mysql
cp /PATH/TO/ambari.original.mysql /tmp/ambari.innodb.mysql

# Now Replace the engine with INNODB (Earlier it might be MyISAM)
sed -ie 's/MyISAM/INNODB/g' /PATH/TO/ambari.innodb.mysql

# Drop and recreate Ambari DB as following:
mysql -u ambari -p ambari
mysql> DROP DATABASE ambari;
mysql> CREATE DATABASE ambari;

# Import new DB with correct storage engine
mysql -u ambari -pbigdata --force ambari < /PATH/TO/ambari.innodb.mysql

.

[#3 · Accepted solution]

@Joshua Adeleke

Or alternatively you might have to set the Engine to "InnoDB" as following for all those listed table names in your Mysql db and then restart the ambari server.

ALTER TABLE DS_XXXXX ENGINE=InnoDB; 

.

And for some other tables like "ClusterHostMapping", "hostgroup_configuration" etc if not set already.

[#4]

@Jay SenSharma @Geoffrey Shelton Okot

Thank you. I have been able to change the affected tables to InnoDB with the ALTER statement. However, on restarting ambari-server, there was another error related to the atlas configs in the clusterconfigmapping table;

2017-06-02 13:31:11,549  WARN - You have config(s): [atlas-solrconfig, atlas-logsearch-conf, atlas-env, atlas-log4j, ranger-atlas-security, ranger-atlas-policymgr-ssl, application-properties, ranger-atlas-plugin-properties] that is(are) not mapped (in clusterconfigmapping table) to any cluster!

2017-06-02 13:31:11,560 ERROR - Required config(s): atlas-solrconfig,atlas-logsearch-conf,atlas-env,atlas-log4j,application-properties is(are) not available for service ATLAS with service config version 2 in cluster XXXX

[#5]

@Joshua Adeleke

Those WARNING messages are related to BUG: https://issues.apache.org/jira/browse/AMBARI-20875 which will be addressed in the Ambari 2.5.2

The ERRORs indicates DB corruption. That needs to be fixed manually.

[#6]

@Joshua Adeleke

For now the warnigns can be ignored. (As they will be addressed in Amabri 2.5.2), OR run the SQL queries mentioned in the JIRA to fix the WARNING messages. https://issues.apache.org/jira/browse/AMBARI-20875

.

For the errors try the following approach to see if this fixes the issue:

1. Start the ambari server in "--skip-database-check" mode as following:

ambari-server start --skip-database-check

2. Now login to ambari UI and then make some dummy changes to the properties. For example:

Ambari UI --> "Atlas" --> "Configs" --> "Advanced" --> "Custom application-properties" Click on "Add Property ..." link and then add some dummy properly like

aaa=bbb

3. Restart ambari server without "--skip-database-check" option and then this time check the error log. You should not see then "application-properties" Value in the error if that is fixed. Same thing you can try with other config types.

ERROR - Required config(s): atlas-solrconfig,atlas-logsearch-conf,atlas-env,atlas-log4j is(are) not available for service ATLAS with service config version 2 in cluster XXXX

.

[#7]

Thanks @Jay SenSharma and @Willem Conradie

I did put in some dummy properties for the configs but only managed to get past all the errors by removing Atlas service.

[#8]

@Joshua Adeleke

HDP 2.6 uses the innoDB engine instead of MyISAM engine in comparison to earlier versions of HDP till 2.5.3 if you are running Mysql.

You will need to change the execution engine to InnoDB so run the below statements to check all the tables while logged on as mysql root user you will need

Converting an Existing Table

SELECT`ENGINE`FROM`information_schema`.`TABLES`WHERE`TABLE_SCHEMA`='your_database_name'AND`TABLE_NAME`='your_table_name';

check table engine

SHOW TABLE STATUS WHERE Name = 'xxx'

Change the execution engine

ALTER TABLE my_table ENGINE = InnoDB;

Then you can restart your database and the errors shouldn't show.

Hope that helps