Community Articles
Find and share helpful community-sourced technical articles
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.
Labels (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);
417 Views
Comments

well written article ,


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

Don't have an account?
Coming from Hortonworks? Activate your account here
Version history
Revision #:
1 of 1
Last update:
‎11-14-2018 06:33 PM
Updated by:
 
Contributors
Top Kudoed Authors