How to fix "You have config groups present in the database with no corresponding service found" warnings

Due to AMBARI-24283 BUG some times there would be warnings like below,

2018-08-09 09:38:27,666 WARN - You have config groups present in the database with no corresponding service found, [(ConfigGroup, Service) => ( HDFS_DL111, null ), ( YARN_2222, null )]. Run --auto-fix-database to fix this automatically.

Possible root cause: In some of the Ambari 2.5.x/2.6.x versions, service DELETE does not delete config groups cleanly and it shows the WARNING while starting ambari-server.

To fix above WARNINGS below steps can be followed.

1. Take Ambari database backup.

2. Run below queries

delete from confgroupclusterconfigmapping where config_group_id in (select group_id from configgroup where group_name in ( 'HDFS_DL111', 'YARN_2222'));

delete from configgrouphostmapping where config_group_id in (select group_id from configgroup where group_name in ( 'HDFS_DL111', 'YARN_2222'));


delete from configgroup where group_name in ( 'HDFS_DL111', 'YARN_2222');