How to fix issue in subscription page of the WSO2 API Manager due to missed API( ERROR - UserAwareAPIConsumer Failed to get API from : /apimgt/applicationdata/provider/)

Sometimes you may not able to view subscriptions in the Store of the WSO2 API Manager due to missing API from registry. When you visit subscription page
(https://192.168.1.4:9443/store/site/pages/subscriptions.jag) you might get following error.

[2013-11-11 21:14:58,904] ERROR - UserAwareAPIConsumer Failed to get API from : /apimgt/applicationdata/provider/admin/ddd/1.0.0/api
org.wso2.carbon.registry.core.exceptions.ResourceNotFoundException: Resource does not exist at path /_system/governance/apimgt/applicationdata/provider/admin/ddd/1.0.0/api
    at org.wso2.carbon.registry.core.jdbc.EmbeddedRegistry.get(EmbeddedRegistry.java:532)
    at org.wso2.carbon.registry.core.caching.CacheBackedRegistry.get(CacheBackedRegistry.java:166)
    at org.wso2.carbon.registry.core.session.UserRegistry.get(UserRegistry.java:524)
    at org.wso2.carbon.apimgt.impl.AbstractAPIManager.getAPI(AbstractAPIManager.java:292)
    at org.wso2.carbon.apimgt.hostobjects.APIStoreHostObject.addAPIObj(APIStoreHostObject.java:1289)
    at org.wso2.carbon.apimgt.hostobjects.APIStoreHostObject.jsFunction_getAllSubscriptions(APIStoreHostObject.java:1278)


The reason for this issue is api resource is not there in provided path(/_system/governance/apimgt/applicationdata/provider/admin/ddd/1.0.0/api ). Here, subscriptions are run time data that stored in api manager db and API are meta data stored in registry. This error log says application is having API which is not there in registry.

This can happen due to multiple reasons.
01. Deleting registry API resource without deleting it from publisher UI(then it will delete registry data and entries in API manager tables).
02. change governance space after create subscriptions().

For this issue you can do followings.
If governance space changed you can mount to old space again. Then missed API will appear in given path.
You can create missed (API ddd/1.0.0) again in the system. for this delete API from api manager tables and create API from publisher UI.
You can delete subscriptions associated with missed API(ddd/1.0.0). For this you need to delete all associated entries from api manager tables carefully.

To fix this issue please run following db queries against you API manager database. Its always recommend to run these queries on test environment and then apply to production servers.

Here first we need to get API_ID associated with problematic API. To get that please run following command
SELECT API_ID FROM AM_API where API_NAME = 'ddd' and API_VERSION = '1.0.0'

Then we need to delete all subscriptions associated with that API. Let say we got 3 as the result of above query. Run following query to delete all associated subscriptions.
DELETE FROM AM_SUBSCRIPTION where API_ID = '3'

Then you are done  :)