How to completely remove remaining API data from WSO2 API-Manager 1.8 after incorrectly deleting an API

Some times you may accidentally delete an Api from the carbon management console. If you delete an API from Management console (Which is not the correct way) It will only delete API related metadata. Some of the data related to that API won't get removed from APi Manager tables. This will corrupt the Database and result in errors.

The recommended way of deleting an API is to use the APIM Publisher application and delete it. This will remove all information related to the API. And if there are subscriptions for that API, it will inform you to unsubscribe it first (these won't happen if you use the management console. It is only provided for management tasks such as user management, etc)

Following is a list of things you need to do to completely remove the api from the API manager 1.8.

Please keep a backup of the database and any other resources before you do this.

Step 1 - Remove subscriptions of that api.

1. execute following query to get the application related information (You will need the APPLICATION_ID value to unsubscribe an API from that application)

SELECT * FROM AM_APPLICATION;

2. execute following query to get the API related information (You will need the API_ID value to unsubscribe an API from an application)

SELECT * FROM AM_API;

3. execute following to delete the subscription (Set the necessary ids. If you do not know which Application id to use, the use all ids from the AM_APPLICATION table. it will delete only the correct combination)

DELETE FROM AM_SUBSCRIPTION WHERE API_ID = ? AND APPLICATION_ID = ?;

Step 2 - Remove API

1. If you have an icon to this api, To delete it, go to following registry location in the management console and and delete the API (select 'Action' and delete)

/_system/governance/apimgt/applicationdata/icons/admin 

2. Delete API related meta data by login to the management console and navigating to Home > Metadata > List > APIs (You have already done that)

3. To delete the Swagger resources related to this API , go to following registy location and delete the resource collection (select on the api-doc resource and it will display swagger docs for all the resources. delete related on by selecting the Action button)

/_system/governance/apimgt/applicationdata/api-docs

4. Delete the synapse configuration for that api in AM_HOME/repository/deployment/server/synapse-configs/default/api location

5. Run following database queries to remove api related data

DELETE FROM AM_API_LC_EVENT WHERE API_ID=? 
DELETE FROM AM_API_COMMENTS WHERE API_ID=? 
DELETE FROM AM_API_RATINGS WHERE API_ID=? 
DELETE FROM AM_SUBSCRIPTION WHERE API_ID=?
DELETE FROM AM_EXTERNAL_STORES WHERE API_ID=?
DELETE FROM AM_API WHERE API_PROVIDER=? AND API_NAME=? AND API_VERSION=? 
DELETE FROM AM_API_URL_MAPPING WHERE API_ID = ?

following are two sample queries

DELETE FROM AM_SUBSCRIPTION WHERE API_ID=5;
DELETE FROM AM_API WHERE API_PROVIDER='admin' AND API_NAME='SampleAPI' AND API_VERSION='1.0';

6. remove api related collection from the registry location
/_system/governance/apimgt/applicationdata/provider/<user>. There should be an empty collection with the api name on it.

7. Restart the AM to remove any cached content.

This should remove all the content related to the api.

I would like to thank ChamilaA who provided the resources needed for this post. Hope this would help someone who is in need. Please drop a comment if you have further queries.