Removing Unused Tokens from the Database

As you use WSO2 API Manager, the number of revoked, inactive and expired tokens accumulates in the IDN_OAUTH2_ACCESS_TOKEN table. These tokens are kept in the database for logging and audit purposes, but they can have a negative impact on the server's performance over time. Therefore, it is recommended to clean them periodically as given in the instructions below:

1. Take a backup of the running database. 

2. Set up the database dump in a test environment and test it for any issues.

   Tip: We recommend you to test the database dump before the cleanup task as the cleanup can take some time.

3. Run the following scripts on the database dump. It takes a backup of the necessary tables, turns off SQL updates and cleans the database of unused tokens.

   USE 'WSO2AM_DB';
   DROP PROCEDURE IF EXISTS 'cleanup_tokens';
   
   DELIMITER $$
   CREATE PROCEDURE 'cleanup_tokens' ()
   BEGIN
   
   -- Backup IDN_OAUTH2_ACCESS_TOKEN table
   DROP TABLE IF EXISTS 'IDN_OAUTH2_ACCESS_TOKEN_BAK';
   CREATE TABLE 'IDN_OAUTH2_ACCESS_TOKEN_BAK' AS SELECT * FROM 'IDN_OAUTH2_ACCESS_TOKEN'; 
   
   -- 'Turn off SQL_SAFE_UPDATES'
   SET @OLD_SQL_SAFE_UPDATES = @@SQL_SAFE_UPDATES;
   SET SQL_SAFE_UPDATES = 0;
   
   -- 'Keep the most recent INACTIVE key for each CONSUMER_KEY, AUTHZ_USER, TOKEN_SCOPE combination'
   SELECT 'BEFORE:TOTAL_INACTIVE_TOKENS', COUNT(*) FROM IDN_OAUTH2_ACCESS_TOKEN WHERE TOKEN_STATE = 'INACTIVE'; 
   
   SELECT 'TO BE RETAINED', COUNT(*) FROM(SELECT ACCESS_TOKEN FROM (SELECT ACCESS_TOKEN, CONSUMER_KEY, AUTHZ_USER, TOKEN_SCOPE FROM IDN_OAUTH2_ACCESS_TOKEN WHERE TOKEN_STATE = 'INACTIVE') x GROUP BY CONSUMER_KEY, AUTHZ_USER, TOKEN_SCOPE)y;
   
   DELETE FROM IDN_OAUTH2_ACCESS_TOKEN WHERE TOKEN_STATE = 'INACTIVE' AND ACCESS_TOKEN NOT IN (SELECT ACCESS_TOKEN FROM(SELECT ACCESS_TOKEN FROM (SELECT ACCESS_TOKEN, CONSUMER_KEY, AUTHZ_USER, TOKEN_SCOPE FROM IDN_OAUTH2_ACCESS_TOKEN WHERE TOKEN_STATE = 'INACTIVE') x GROUP BY CONSUMER_KEY, AUTHZ_USER, TOKEN_SCOPE)y);
   
   SELECT 'AFTER:TOTAL_INACTIVE_TOKENS', COUNT(*) FROM IDN_OAUTH2_ACCESS_TOKEN WHERE TOKEN_STATE = 'INACTIVE'; 
   
   -- 'Keep the most recent REVOKED key for each CONSUMER_KEY, AUTHZ_USER, TOKEN_SCOPE combination'
   SELECT 'BEFORE:TOTAL_REVOKED_TOKENS', COUNT(*) FROM IDN_OAUTH2_ACCESS_TOKEN WHERE TOKEN_STATE = 'REVOKED';
   
   SELECT 'TO BE RETAINED', COUNT(*) FROM(SELECT ACCESS_TOKEN FROM (SELECT ACCESS_TOKEN, CONSUMER_KEY, AUTHZ_USER, TOKEN_SCOPE FROM IDN_OAUTH2_ACCESS_TOKEN WHERE TOKEN_STATE = 'REVOKED') x GROUP BY CONSUMER_KEY, AUTHZ_USER, TOKEN_SCOPE)y;
   
   DELETE FROM IDN_OAUTH2_ACCESS_TOKEN WHERE TOKEN_STATE = 'REVOKED' AND ACCESS_TOKEN NOT IN (SELECT ACCESS_TOKEN FROM(SELECT ACCESS_TOKEN FROM (SELECT ACCESS_TOKEN, CONSUMER_KEY, AUTHZ_USER, TOKEN_SCOPE FROM IDN_OAUTH2_ACCESS_TOKEN WHERE TOKEN_STATE = 'REVOKED') x GROUP BY CONSUMER_KEY, AUTHZ_USER, TOKEN_SCOPE)y);
   
   SELECT 'AFTER:TOTAL_REVOKED_TOKENS', COUNT(*) FROM IDN_OAUTH2_ACCESS_TOKEN WHERE TOKEN_STATE = 'REVOKED';
   
   -- 'Keep the most recent EXPIRED key for each CONSUMER_KEY, AUTHZ_USER, TOKEN_SCOPE combination'
   SELECT 'BEFORE:TOTAL_EXPIRED_TOKENS', COUNT(*) FROM IDN_OAUTH2_ACCESS_TOKEN WHERE TOKEN_STATE = 'EXPIRED';
   
   SELECT 'TO BE RETAINED', COUNT(*) FROM(SELECT ACCESS_TOKEN FROM (SELECT ACCESS_TOKEN, CONSUMER_KEY, AUTHZ_USER, TOKEN_SCOPE FROM IDN_OAUTH2_ACCESS_TOKEN WHERE TOKEN_STATE = 'EXPIRED') x GROUP BY CONSUMER_KEY, AUTHZ_USER, TOKEN_SCOPE)y;
   
   DELETE FROM IDN_OAUTH2_ACCESS_TOKEN WHERE TOKEN_STATE = 'EXPIRED' AND ACCESS_TOKEN NOT IN (SELECT ACCESS_TOKEN FROM(SELECT ACCESS_TOKEN FROM (SELECT ACCESS_TOKEN, CONSUMER_KEY, AUTHZ_USER, TOKEN_SCOPE FROM IDN_OAUTH2_ACCESS_TOKEN WHERE TOKEN_STATE = 'EXPIRED') x GROUP BY CONSUMER_KEY, AUTHZ_USER, TOKEN_SCOPE)y);
   
   SELECT 'AFTER:TOTAL_EXPIRED_TOKENS', COUNT(*) FROM IDN_OAUTH2_ACCESS_TOKEN WHERE TOKEN_STATE = 'EXPIRED';
   
   -- 'Restore the original SQL_SAFE_UPDATES value'
   SET SQL_SAFE_UPDATES = @OLD_SQL_SAFE_UPDATES;
   
   END$$
   DELIMITER ;

4. Once the cleanup is over, start the API Manager pointing to the cleaned-up database dump and test throughlyfor any issues. 
   You can also schedule a cleanup task that will be automatically run after a given period of time. Here's an example:

   USE 'WSO2AM_DB';
   DROP EVENT IF EXISTS 'cleanup_tokens_event';
   CREATE EVENT 'cleanup_tokens_event'
       ON SCHEDULE
         EVERY 1 WEEK STARTS '2015-01-01 00:00.00'
       DO
         CALL 'WSO2AM_DB'.'cleanup_tokens'();
   
   -- 'Turn on the event_scheduler'
   SET GLOBAL event_scheduler = ON;