Accessing Capacity Scheduler View gives 500 internal server error message

[#1]

PROBLEM : Accessing Capacity Scheduler View give 500 internal server error message and ambari server logs and Table './ambari/alert_history' is marked as crashed and should be repaired.

ERROR :

ERROR [ambari-client-thread-35] ContainerResponse:537 - Mapped exception to response: 500 (Internal Server Error) org.apache.ambari.view.capacityscheduler.utils.ServiceFormattedException at org.apache.ambari.view.capacityscheduler.ConfigurationService.readClusterInfo(ConfigurationService.java:162) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

ERROR [ambari-client-thread-137] ReadHandler:102 - Caught a runtime exception executing a queryjavax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.6.2.v20151217-774c696): org.eclipse.persistence.exceptions.DatabaseExceptionInternal Exception: java.sql.SQLException: Table './ambari/alert_history' is marked as crashed and should be repairedError Code: 145

ROOT CAUSE : alert_definition_id present in alert_history table does not exist in alert_definition table.

WORKAROUND / RESOLUTION :

1. Take a dump of Ambari Database.

2. Stop Ambari Server.

$ ambari-server stop

3. Login to the Ambari Database and run below queries:

a) Run below select queries:

> select * from alert_history where alert_definition_id not in (select definition_id from alert_definition);
> select * from alert_current where definition_id not in (select definition_id from alert_definition);

b) When there is an non empty set result for above step 3a queries then run below delete queries.

> delete from alert_history where alert_definition_id not in (select definition_id from alert_definition);
> delete from alert_current where definition_id not in (select definition_id from alert_definition);

4. Start Ambari Server.

$ ambari-server start