How to fix Database consistency warnings in Ambari 2.7.x

[#1]

Steps to fix below Database consistency WARNINGs in Ambari 2.7.x

WARN - You have config(s): webhcat-site-version1540591929056,webhcat-log4j-version1540591928580,hive-exec-log4j-version1540591929168,webhcat-env-version1540591929289,hive-log4j-version1540591929465,hcat-env-version1540591928892 that is(are) not mapped (in serviceconfigmapping table) to any service!

CREATE TEMPORARY TABLE orphaned_configs AS
(select config_id,type_name,version_tag from clusterconfig where unmapped != 1 and type_name not in ('cluster-env') and config_id not in (
SELECT
cc.config_id 
FROM clusterconfig cc, serviceconfig sc, serviceconfigmapping scm
WHERE cc.type_name != 'cluster-env'
AND cc.config_id = scm.config_id
AND scm.service_config_id = sc.service_config_id));


Update clusterconfig set unmapped = 1 where config_id in (select config_id from orphaned_configs);


drop table orphaned_configs;

If it is 2.6.x then following query can be used

SELECT cc.config_id, cc.type_name, cc.version_tag
FROM ambari.clusterconfig cc, ambari.clusterconfig ccm
WHERE cc.config_id NOT IN (SELECT scm.config_id FROM ambari.serviceconfigmapping scm) 
AND cc.type_name != 'cluster-env' AND cc.type_name = ccm.type_name AND cc.version_tag = 
ccm.version_tag;

CREATE TEMPORARY TABLE orphaned_configs AS
(SELECT cc.config_id FROM ambari.clusterconfig cc WHERE cc.config_id NOT IN 
(SELECT scm.config_id FROM ambari.serviceconfigmapping scm) AND cc.type_name != 
'cluster-env');

DELETE FROM ambari.clusterconfig WHERE config_id IN 
(SELECT config_id from orphaned_configs);

[#2]

well written article ,

For those who are using oracle database, we need to commit the transaction done after this changes.