This documentation is for WSO2 API Manager 2.0.0 View documentation for the latest release.
Page Comparison - Removing Unused Tokens from the Database (v.7 vs v.8) - API Manager 2.0.0 - WSO2 Documentation

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: https://support.wso2.com/jira/browse/WSODOCINTERNAL-880

...

  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

    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.

    Localtab Group
    Localtab
    activetrue
    titleMySQL
    Code Block
    languagesql
    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 IDN_OAUTH2_ACCESS_TOKEN T1 WHERE TIME_CREATED = (SELECT MAX(TIME_CREATED) AS LATEST_TOKEN_TIME FROM IDN_OAUTH2_ACCESS_TOKEN T2 WHERE TOKEN_STATE = 'INACTIVE' AND T1.CONSUMER_KEY = T2.CONSUMER_KEY AND T1.AUTHZ_USER = T2.AUTHZ_USER GROUP BY CONSUMER_KEY , AUTHZ_USER , TOKEN_STATE));
    
    
    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 IDN_OAUTH2_ACCESS_TOKEN T1 WHERE TIME_CREATED = (SELECT MAX(TIME_CREATED) AS LATEST_TOKEN_TIME FROM IDN_OAUTH2_ACCESS_TOKEN T2 WHERE TOKEN_STATE = 'REVOKED' AND T1.CONSUMER_KEY = T2.CONSUMER_KEY AND T1.AUTHZ_USER = T2.AUTHZ_USER GROUP BY CONSUMER_KEY , AUTHZ_USER , TOKEN_STATE));                                                                     
    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 IDN_OAUTH2_ACCESS_TOKEN T1 WHERE TIME_CREATED = (SELECT MAX(TIME_CREATED) AS LATEST_TOKEN_TIME FROM IDN_OAUTH2_ACCESS_TOKEN T2 WHERE TOKEN_STATE = 'EXPIRED'AND T1.CONSUMER_KEY = T2.CONSUMER_KEY AND T1.AUTHZ_USER = T2.AUTHZ_USER GROUP BY CONSUMER_KEY , AUTHZ_USER , TOKEN_STATE));
    
    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 ;
    Localtab
    activetrue
    titleOracle DB
    Note

    Please note that the stored procedure is not included in the script given below.

    Code Block
    languagesql
    DROP TABLE IDN_OAUTH2_ACCESS_TOKEN_BAK;
    
    CREATE TABLE IDN_OAUTH2_ACCESS_TOKEN_BAK AS SELECT * FROM IDN_OAUTH2_ACCESS_TOKEN;
    
    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_ID, AUTHZ_USER, TOKEN_SCOPE_HASH FROM IDN_OAUTH2_ACCESS_TOKEN WHERE TOKEN_STATE = 'INACTIVE'));
    
    DELETE FROM IDN_OAUTH2_ACCESS_TOKEN WHERE TOKEN_STATE = 'INACTIVE' AND ACCESS_TOKEN NOT IN (SELECT ACCESS_TOKEN FROM IDN_OAUTH2_ACCESS_TOKEN T1 WHERE TIME_CREATED = (SELECT MAX(TIME_CREATED) AS LATEST_TOKEN_TIME FROM IDN_OAUTH2_ACCESS_TOKEN T2 WHERE TOKEN_STATE = 'INACTIVE' AND T1.CONSUMER_KEY_ID = T2.CONSUMER_KEY_ID AND T1.AUTHZ_USER = T2.AUTHZ_USER GROUP BY CONSUMER_KEY_ID , AUTHZ_USER , TOKEN_STATE));
    
    SELECT 'AFTER:TOTAL_INACTIVE_TOKENS', COUNT(*) FROM IDN_OAUTH2_ACCESS_TOKEN WHERE TOKEN_STATE = 'INACTIVE';
    
    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_ID, AUTHZ_USER, TOKEN_SCOPE_HASH FROM IDN_OAUTH2_ACCESS_TOKEN WHERE TOKEN_STATE = 'REVOKED') x GROUP BY ACCESS_TOKEN,CONSUMER_KEY_ID, AUTHZ_USER, TOKEN_SCOPE_HASH) y;
    
    DELETE FROM IDN_OAUTH2_ACCESS_TOKEN WHERE TOKEN_STATE = 'REVOKED' AND ACCESS_TOKEN NOT IN (SELECT ACCESS_TOKEN FROM IDN_OAUTH2_ACCESS_TOKEN T1 WHERE TIME_CREATED = (SELECT MAX(TIME_CREATED) AS LATEST_TOKEN_TIME FROM IDN_OAUTH2_ACCESS_TOKEN T2 WHERE TOKEN_STATE = 'REVOKED' AND T1.CONSUMER_KEY_ID = T2.CONSUMER_KEY_ID AND T1.AUTHZ_USER = T2.AUTHZ_USER GROUP BY CONSUMER_KEY_ID , AUTHZ_USER , TOKEN_STATE));
    
    SELECT 'AFTER:TOTAL_REVOKED_TOKENS', COUNT(*) FROM IDN_OAUTH2_ACCESS_TOKEN WHERE TOKEN_STATE = 'REVOKED';
    
    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_ID, AUTHZ_USER, TOKEN_SCOPE_HASH FROM IDN_OAUTH2_ACCESS_TOKEN WHERE TOKEN_STATE = 'EXPIRED') x GROUP BY ACCESS_TOKEN, CONSUMER_KEY_ID, AUTHZ_USER, TOKEN_SCOPE_HASH) y;
    
    DELETE FROM IDN_OAUTH2_ACCESS_TOKEN WHERE TOKEN_STATE = 'EXPIRED' AND ACCESS_TOKEN NOT IN (SELECT ACCESS_TOKEN FROM IDN_OAUTH2_ACCESS_TOKEN T1 WHERE TIME_CREATED = (SELECT MAX(TIME_CREATED) AS LATEST_TOKEN_TIME FROM IDN_OAUTH2_ACCESS_TOKEN T2 WHERE TOKEN_STATE = 'EXPIRED' AND T1.CONSUMER_KEY_ID = T2.CONSUMER_KEY_ID AND T1.AUTHZ_USER = T2.AUTHZ_USER GROUP BY CONSUMER_KEY_ID , AUTHZ_USER , TOKEN_STATE));
    
    SELECT 'AFTER:TOTAL_EXPIRED_TOKENS', COUNT(*) FROM IDN_OAUTH2_ACCESS_TOKEN WHERE TOKEN_STATE = 'EXPIRED';
  4. Once the cleanup is over, start the API Manager pointing to the cleaned-up database dump and test throughly for any issues. 
    You can also schedule a cleanup task that will be automatically run after a given period of time. Here's an example:

    Code Block
    languagesql
    titleMySQL
    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;