Ambari server not able to start after ambari-server upgrade

[#1]

Issue: Upgrade Ambari server from X.X.X version to Y.Y.Y version which was successful with errors as showing below -

sudo ambari-server upgrade 
Using python /usr/bin/python 
Upgrading ambari-server 
Updating properties in ambari.properties ... 
WARNING: Can not find ambari-env.sh.rpmsave file from previous version, skipping restore of environment settings 
Fixing database objects owner 
Ambari Server configured for Embedded Postgres. Confirm you have made a backup of the Ambari Server database [y/n] (y)? y 
Upgrading database schema 
Error output from schema upgrade command: 
com.google.inject.ProvisionException: Guice provision errors: 

1) null returned by binding at org.apache.ambari.server.state.ServiceComponentHostFactory.createExisting() 
but parameter 2 of org.apache.ambari.server.state.svccomphost.ServiceComponentHostImpl.<init>() is not @Nullable 
while locating org.apache.ambari.server.orm.entities.HostComponentDesiredStateEntity annotated with @com.google.inject.assistedinject.Assisted(value=) 
for parameter 2 at org.apache.ambari.server.state.svccomphost.ServiceComponentHostImpl.<init>(ServiceComponentHostImpl.java:785) 
while locating org.apache.ambari.server.state.ServiceComponentHost annotated with interface com.google.inject.assistedinject.Assisted 

1 error 
at com.google.inject.internal.InjectorImpl$4.get(InjectorImpl.java:987) 
at com.google.inject.assistedinject.FactoryProvider2.invoke(FactoryProvider2.java:632) 
at com.sun.proxy.$Proxy19.createExisting(Unknown Source) 
at org.apache.ambari.server.state.ServiceComponentImpl.<init>(ServiceComponentImpl.java:161) 
at org.apache.ambari.server.state.ServiceComponentImpl$$EnhancerByGuice$$ecf0911c.<init>(<generated>) 
at org.apache.ambari.server.state.ServiceComponentImpl$$EnhancerByGuice$$ecf0911c$$FastClassByGuice$$5fef485a.newInstance(<generated>) 
at com.google.inject.internal.cglib.reflect.$FastConstructor.newInstance(FastConstructor.java:40) 
at com.google.inject.internal.ProxyFactory$ProxyConstructor.newInstance(ProxyFactory.java:260) 
at com.google.inject.internal.ConstructorInjector.construct(ConstructorInjector.java:85) 
at com.google.inject.internal.ConstructorBindingImpl$Factory.get(ConstructorBindingImpl.java:254) 
at com.google.inject.internal.InjectorImpl$4$1.call(InjectorImpl.java:978) 
at com.google.inject.internal.InjectorImpl.callInContext(InjectorImpl.java:1031) 
at com.google.inject.internal.InjectorImpl$4.get(InjectorImpl.java:974) 
at com.google.inject.assistedinject.FactoryProvider2.invoke(FactoryProvider2.java:632) 
at com.sun.proxy.$Proxy18.createExisting(Unknown Source) 
at org.apache.ambari.server.state.ServiceImpl.<init>(ServiceImpl.java:163) 
at org.apache.ambari.server.state.ServiceImpl$$EnhancerByGuice$$d0875be7.<init>(<generated>) 
at org.apache.ambari.server.state.ServiceImpl$$EnhancerByGuice$$d0875be7$$FastClassByGuice$$f62c0b29.newInstance(<generated>) 
at com.google.inject.internal.cglib.reflect.$FastConstructor.newInstance(FastConstructor.java:40) 
at com.google.inject.internal.ProxyFactory$ProxyConstructor.newInstance(ProxyFactory.java:260) 
at com.google.inject.internal.ConstructorInjector.construct(ConstructorInjector.java:85) 
at com.google.inject.internal.ConstructorBindingImpl$Factory.get(ConstructorBindingImpl.java:254) 
at com.google.inject.internal.InjectorImpl$4$1.call(InjectorImpl.java:978) 
at com.google.inject.internal.InjectorImpl.callInContext(InjectorImpl.java:1024) 
at com.google.inject.internal.InjectorImpl$4.get(InjectorImpl.java:974) 
at com.google.inject.assistedinject.FactoryProvider2.invoke(FactoryProvider2.java:632) 
at com.sun.proxy.$Proxy14.createExisting(Unknown Source) 
at org.apache.ambari.server.state.cluster.ClusterImpl.loadServices(ClusterImpl.java:426) 
at org.apache.ambari.server.state.cluster.ClusterImpl.getServices(ClusterImpl.java:961) 
at org.apache.ambari.server.upgrade.AbstractUpgradeCatalog.addNewConfigurationsFromXml(AbstractUpgradeCatalog.java:311) 
at org.apache.ambari.server.upgrade.UpgradeCatalog212.executeDMLUpdates(UpgradeCatalog212.java:157) 
at org.apache.ambari.server.upgrade.AbstractUpgradeCatalog.upgradeData(AbstractUpgradeCatalog.java:664) 
at org.apache.ambari.server.upgrade.SchemaUpgradeHelper.executeDMLUpdates(SchemaUpgradeHelper.java:228) 
at org.apache.ambari.server.upgrade.SchemaUpgradeHelper.main(SchemaUpgradeHelper.java:305) 
com.google.inject.ProvisionException: Guice provision errors: 


Adjusting ambari-server permissions and ownership... 
Ambari Server 'upgrade' completed successfully.

When tried to start ambari server it got failed with database consistency check failed.

Debug:

When check ambari database logs we found below error -

$cat /var/log/ambari-server/ambari-check-database.log

2016-11-10 09:40:55,365 INFO - ******************************* Check database started ******************************* 
2016-11-10 09:40:58,841 INFO - Checking for configs not mapped to any cluster 
2016-11-10 09:40:58,858 INFO - Checking for configs selected more than once 
2016-11-10 09:40:58,860 INFO - Checking for hosts without state 
2016-11-10 09:40:58,861 INFO - Checking host component states count equals host component desired states count 
2016-11-10 09:40:58,863 ERROR - Your host component states (hostcomponentstate table) count not equals host component desired states (hostcomponentdesiredstate table) count! 
2016-11-10 09:40:58,863 INFO - Checking services and their configs 
2016-11-10 09:41:01,195 INFO - ******************************* Check database completed ******************************* 

Resolution:

1. As per above logs file ie.ambari-check-database.log we see that there is count differences for hostcomponentstate table and hostcomponentdesiredstate table .

2. Tried to do select count(*) on both tables as below and we found 'X' no of rows differences. [X was = 10 in my case]

select count(*) on  hostcomponentstate;
select count(*) on  hostcomponentdesiredstate;

3. To find out differences we did "group by" on each column as below -

select count(*) from hostcomponentstate group by host_id;
select count(*) from hostcomponentdesiredstate group by host_id; 

Followed same steps for all rest of the columns and was able to find the differences in the count values for the respective columns.

4. We deleted additional entries using delete command as shown below -

delete from hostcomponentstate where host_id=**** and version='****'; 

For Eg.
delete from hostcomponentstate where host_id=4 and version='2.3.0.0-2557'; 

5. Tried restarting ambari server which worked successfully.

6. Stopped ambari server again as there was error previously while we ran 'ambari-server upgrade' initially.

7. Re-run the ambari-server upgrade command which ran successfully.

8. Started ambari-server, which was able to start without any issue.

[#2]

Easy way to detect duplicate value is:

select component_name, service_name, host_id, cluster_id,count(*) from ambari.hostcomponentdesiredstate group by component_name, service_name, host_id, cluster_id order by count desc;

select component_name, service_name, host_id, cluster_id,count(*) from ambari.hostcomponentstate group by component_name, service_name, host_id, cluster_id order by count desc;

You will find that count of one of the table is different from other. Just delete that by id and you are good to go.