Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 16 Next »

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:

Tip : It is safe to run these steps in read-only mode or during a time when traffic on the server is low, but that is not mandatory.

  1. Take a backup of the running database. 
  2. Set up the database dump in a test environment and test it for any issues.

    For more information on setting up a database dump, go to the MySQL, SQL Server, and Oracle offical documentation.

    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.

    The following script has been tested on MySQL 5.7.

    USE WSO2AM_DB;      -- Replace WSO2AM_DB with your database name
     
    DELIMITER $$
     
    DROP PROCEDURE IF EXISTS WSO2_TOKEN_CLEANUP_SP$$
    CREATE PROCEDURE WSO2_TOKEN_CLEANUP_SP ()
     
    BEGIN
     
    -- ------------------------------------------
    -- DECLARE VARIABLES
    -- ------------------------------------------
    DECLARE batchSize INT;
    DECLARE backupTables BOOLEAN;
    DECLARE sleepTime FLOAT;
    DECLARE safePeriod INT;
    DECLARE deleteTillTime DATETIME;
    DECLARE rowCount INT;
    DECLARE enableTraceLog BOOLEAN;
    DECLARE enableAudit BOOLEAN;
    DECLARE counter INT;
    DECLARE done INT DEFAULT FALSE;
     
     
    DECLARE OAT_TOKEN_ID VARCHAR(999);
    DECLARE OAT_ACCESS_TOKEN VARCHAR(999);
    DECLARE OAT_TOKEN_STATE VARCHAR(25);
    DECLARE OAT_TIME_CREATED timestamp;
    DECLARE OAT_VALIDITY_PERIOD bigint(20);
    DECLARE OAT_REFRESH_TOKEN_TIME_CREATED timestamp;
    DECLARE OAT_REFRESH_TOKEN_VALIDITY_PERIOD bigint(20);
     
    DECLARE OAC_CODE_ID VARCHAR(999);
    DECLARE OAC_TOKEN_ID VARCHAR(999);
    DECLARE OAC_TOKEN_STATE VARCHAR(25);
    DECLARE OAC_TIME_CREATED timestamp;
    DECLARE OAC_VALIDITY_PERIOD bigint(20);
     
     
    DECLARE cursrOathToken CURSOR FOR SELECT TOKEN_ID,ACCESS_TOKEN,TOKEN_STATE,TIME_CREATED,VALIDITY_PERIOD,REFRESH_TOKEN_TIME_CREATED,REFRESH_TOKEN_VALIDITY_PERIOD FROM IDN_OAUTH2_ACCESS_TOKEN where TIME_CREATED < deleteTillTime;
     
    DECLARE cursrAuthCode  CURSOR FOR SELECT CODE_ID,TOKEN_ID,STATE,TIME_CREATED,VALIDITY_PERIOD FROM IDN_OAUTH2_AUTHORIZATION_CODE where TIME_CREATED < deleteTillTime;
     
     
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    -- ------------------------------------------
    -- CONFIGURABLE ATTRIBUTES
    -- ------------------------------------------
    SET batchSize = 10000;          -- SET BATCH SIZE FOR AVOID TABLE LOCKS    [DEFAULT : 10000]
    SET backupTables = TRUE;         -- SET IF TOKEN TABLE NEEDS TO BACKUP BEFORE DELETE     [DEFAULT : TRUE]
    SET sleepTime = 2;            -- SET SLEEP TIME FOR AVOID TABLE LOCKS     [DEFAULT : 2]
    SET safePeriod = 2;             -- SET SAFE PERIOD OF HOURS FOR TOKEN DELETE, SINCE TOKENS COULD BE CASHED    [DEFAULT : 2]
    SET deleteTillTime = DATE_ADD(NOW(), INTERVAL -(safePeriod) HOUR);    -- SET CURRENT TIME - safePeriod FOR BEGIN THE TOKEN DELETE
    SET @rowCount=0;
    SET enableTraceLog = FALSE;
    SET enableAudit = TRUE;             -- SET TRUE FOR  KEEP TRACK OF ALL THE DELETED TOKENS USING A TEMP TABLE    [DEFAULT : TRUE]
    SET counter = 0;
    SET autocommit = 0;            -- SET AUTOCOMMIT FALSE TO IMPROVE THE BATCH PROCESSING [DEFAULT : 0]
     
     
    SET SQL_MODE='ALLOW_INVALID_DATES';                                -- MAKE THIS UNCOMMENT IF MYSQL THROWS "ERROR 1067 (42000): Invalid default value for 'TIME_CREATED'"
    -- SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;      -- SET ISOLATION LEVEL TO AVOID TABLE LOCKS IN SELECT.
     
     
    SELECT 'TOKEN_CLEANUP_SP STARTED .... !' FROM DUAL;
     
    IF (backupTables)
    THEN
        SELECT 'TABLE BACKUP STARTED ... !' FROM DUAL;
    -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    -- BACKUP IDN_OAUTH2_ACCESS_TOKEN TABLE
    -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'IDN_OAUTH2_ACCESS_TOKEN_BAK'))
        THEN
            DROP TABLE IDN_OAUTH2_ACCESS_TOKEN_BAK;
        END IF;
     
        IF (enableTraceLog)
        THEN
            SELECT 'BACKING UP IDN_OAUTH2_ACCESS_TOKEN TOKENS :', COUNT(1) FROM IDN_OAUTH2_ACCESS_TOKEN;
        END IF;
     
        CREATE TABLE IDN_OAUTH2_ACCESS_TOKEN_BAK SELECT * FROM IDN_OAUTH2_ACCESS_TOKEN;
     
    -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    -- BACKUP IDN_OAUTH2_AUTHORIZATION_CODE TABLE
    -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'IDN_OAUTH2_AUTHORIZATION_CODE_BAK'))
        THEN
            DROP TABLE IDN_OAUTH2_AUTHORIZATION_CODE_BAK;
        END IF;
     
        IF (enableTraceLog)
        THEN
            SELECT 'BACKING UP IDN_OAUTH2_AUTHORIZATION_CODE TOKENS :', COUNT(1) FROM IDN_OAUTH2_AUTHORIZATION_CODE;
        END IF;
     
        CREATE TABLE IDN_OAUTH2_AUTHORIZATION_CODE_BAK SELECT * FROM IDN_OAUTH2_AUTHORIZATION_CODE;
     
    END IF;
     
     
     
    -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    -- TEMP_TABLE FOR DELETING TOKENS
    -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    IF (enableAudit)
    THEN
        IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'IDN_OAUTH2_ACCESS_TOKEN_DELETING_TEMP'))
        THEN
            DROP TABLE IDN_OAUTH2_ACCESS_TOKEN_DELETING_TEMP;
        END IF;
     
        CREATE TABLE IDN_OAUTH2_ACCESS_TOKEN_DELETING_TEMP SELECT * FROM IDN_OAUTH2_ACCESS_TOKEN WHERE 1 = 2;
    END IF;
     
     
     
    -- ------------------------------------------------------
    -- BATCH DELETE IDN_OAUTH2_ACCESS_TOKEN
    -- ------------------------------------------------------
    SELECT 'BATCH DELETE IDN_OAUTH2_ACCESS_TOKEN .... !' FROM DUAL;
     
    IF (enableTraceLog)
    THEN
        SELECT 'TOTAL TOKENS ON IDN_OAUTH2_ACCESS_TOKEN TABLE BEFORE DELETE', COUNT(1) FROM IDN_OAUTH2_ACCESS_TOKEN;
    END IF;
     
    OPEN cursrOathToken;
    SET done = false;
    SET counter=0;
     
    delete_loop_1: LOOP
     
        FETCH cursrOathToken INTO OAT_TOKEN_ID, OAT_ACCESS_TOKEN, OAT_TOKEN_STATE, OAT_TIME_CREATED, OAT_VALIDITY_PERIOD, OAT_REFRESH_TOKEN_TIME_CREATED, OAT_REFRESH_TOKEN_VALIDITY_PERIOD;
     
        IF done THEN LEAVE delete_loop_1; END IF;
     
        IF counter = batchSize THEN COMMIT; DO SLEEP(sleepTime); SET counter=0; END IF;
     
        IF (OAT_TOKEN_STATE='EXPIRED' OR  OAT_TOKEN_STATE='INACTIVE' OR OAT_TOKEN_STATE='REVOKED' OR (OAT_TOKEN_STATE='ACTIVE' AND (deleteTillTime > DATE_ADD(OAT_TIME_CREATED , INTERVAL +((OAT_VALIDITY_PERIOD/1000)/60) MINUTE)) AND (deleteTillTime > DATE_ADD(OAT_REFRESH_TOKEN_TIME_CREATED,INTERVAL +((OAT_REFRESH_TOKEN_VALIDITY_PERIOD/1000)/60) MINUTE))))
        THEN
            DELETE FROM IDN_OAUTH2_ACCESS_TOKEN WHERE TOKEN_ID = OAT_TOKEN_ID;
            DELETE FROM IDN_OAUTH2_AUTHORIZATION_CODE WHERE TOKEN_ID = OAT_TOKEN_ID;
     
            IF (enableAudit)
            THEN
                INSERT INTO WSO2AM_DB.IDN_OAUTH2_ACCESS_TOKEN_DELETING_TEMP
                (TOKEN_ID, ACCESS_TOKEN, REFRESH_TOKEN, CONSUMER_KEY_ID, AUTHZ_USER, TENANT_ID, USER_DOMAIN, USER_TYPE, GRANT_TYPE, TIME_CREATED, REFRESH_TOKEN_TIME_CREATED, VALIDITY_PERIOD, REFRESH_TOKEN_VALIDITY_PERIOD, TOKEN_SCOPE_HASH, TOKEN_STATE, TOKEN_STATE_ID, SUBJECT_IDENTIFIER)
                VALUES(OAT_TOKEN_ID, OAT_ACCESS_TOKEN, '', 0, '', 0, '', '', '', OAT_TIME_CREATED, OAT_REFRESH_TOKEN_TIME_CREATED, OAT_VALIDITY_PERIOD, OAT_REFRESH_TOKEN_VALIDITY_PERIOD, '', OAT_TOKEN_STATE, 'NONE', '');
            END IF;
     
            SET counter=counter+1;
        END IF;
     
    END LOOP;
     
    COMMIT;
     
    CLOSE cursrOathToken;
     
    -- ------------------------------------------------------
     
    -- ------------------------------------------------------
    -- BATCH DELETE IDN_OAUTH2_AUTHORIZATION_CODE
    -- ------------------------------------------------------
    SELECT 'BATCH DELETE IDN_OAUTH2_AUTHORIZATION_CODE .... !' FROM DUAL;
     
    OPEN cursrAuthCode;
    SET done = false;
    SET counter=0;
     
    delete_loop_2: LOOP
     
        FETCH cursrAuthCode INTO OAC_CODE_ID,OAC_TOKEN_ID,OAC_TOKEN_STATE,OAC_TIME_CREATED,OAC_VALIDITY_PERIOD;
     
        IF done THEN LEAVE delete_loop_2; END IF;
     
        IF counter = batchSize THEN COMMIT; DO SLEEP(sleepTime); SET counter=0; END IF;
     
        IF ((OAC_TOKEN_STATE NOT IN ('ACTIVE') AND (OAC_TOKEN_ID IS NULL OR OAC_TOKEN_ID ='')) OR (OAC_TOKEN_STATE = 'ACTIVE' AND deleteTillTime > DATE_ADD(OAC_TIME_CREATED , INTERVAL +((OAC_VALIDITY_PERIOD/1000)/60) MINUTE)))
        THEN
            DELETE FROM IDN_OAUTH2_AUTHORIZATION_CODE WHERE CODE_ID = OAC_CODE_ID;
            SET counter=counter+1;
        END IF;
     
    END LOOP;
     
    COMMIT;
     
    CLOSE cursrAuthCode;
     
    -- ------------------------------------------------------
     
     
    IF (enableTraceLog)
    THEN
        SELECT 'TOTAL TOKENS ON IDN_OAUTH2_ACCESS_TOKEN TABLE AFTER DELETE', COUNT(1) FROM IDN_OAUTH2_ACCESS_TOKEN;
    END IF;
     
     
    SELECT 'TOKEN_CLEANUP_SP COMPLETED .... !' FROM DUAL;
     
    -- ------------------------------------------------------
     
     
    END$$
     
    DELIMITER ;
    -- Replace WSO2AM_DB with your database name
    USE WSO2AM_DB;
    IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'cleanup_tokens')
    DROP PROCEDURE cleanup_tokens
    GO
     
    CREATE PROCEDURE cleanup_tokens
     
    AS
    BEGIN
     
    -- Backup IDN_OAUTH2_ACCESS_TOKEN table
    IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'IDN_OAUTH2_ACCESS_TOKEN_BAK'))
    BEGIN
      DROP TABLE dbo.IDN_OAUTH2_ACCESS_TOKEN_BAK;
    END
       
     SELECT * INTO IDN_OAUTH2_ACCESS_TOKEN_BAK FROM dbo.IDN_OAUTH2_ACCESS_TOKEN;
      
     -- 'Keep the most recent INACTIVE key for each CONSUMER_KEY, AUTHZ_USER, TOKEN_SCOPE combination'
    SELECT 'BEFORE:TOTAL_INACTIVE_TOKENS', COUNT(*) FROM dbo.IDN_OAUTH2_ACCESS_TOKEN WHERE TOKEN_STATE = 'INACTIVE';
    SELECT 'TO BE RETAINED', COUNT(ACCESS_TOKEN) FROM(SELECT max(ACCESS_TOKEN) ACCESS_TOKEN FROM (SELECT ACCESS_TOKEN, CONSUMER_KEY_ID, AUTHZ_USER, TOKEN_SCOPE_HASH FROM dbo.IDN_OAUTH2_ACCESS_TOKEN WHERE TOKEN_STATE = 'INACTIVE') x
     GROUP BY CONSUMER_KEY_ID, AUTHZ_USER, TOKEN_SCOPE_HASH)y;
      DELETE FROM dbo.IDN_OAUTH2_ACCESS_TOKEN WHERE TOKEN_STATE = 'INACTIVE' AND ACCESS_TOKEN NOT IN (SELECT ACCESS_TOKEN FROM(SELECT max(ACCESS_TOKEN) ACCESS_TOKEN FROM (SELECT ACCESS_TOKEN, CONSUMER_KEY_ID, AUTHZ_USER, TOKEN_SCOPE_HASH
      FROM dbo.IDN_OAUTH2_ACCESS_TOKEN WHERE TOKEN_STATE = 'INACTIVE') x GROUP BY CONSUMER_KEY_ID, AUTHZ_USER, TOKEN_SCOPE_HASH)y);
       
       
       
    -- 'Keep the most recent REVOKED key for each CONSUMER_KEY, AUTHZ_USER, TOKEN_SCOPE combination'
    SELECT 'BEFORE:TOTAL_REVOKED_TOKENS', COUNT(*) FROM dbo.IDN_OAUTH2_ACCESS_TOKEN WHERE TOKEN_STATE = 'REVOKED';
     
    SELECT 'TO BE RETAINED', COUNT(*) FROM(SELECT max(ACCESS_TOKEN)ACCESS_TOKEN FROM (SELECT ACCESS_TOKEN, CONSUMER_KEY_ID, AUTHZ_USER, TOKEN_SCOPE_HASH FROM dbo.IDN_OAUTH2_ACCESS_TOKEN WHERE TOKEN_STATE = 'REVOKED') x GROUP BY CONSUMER_KEY_ID, AUTHZ_USER, TOKEN_SCOPE_HASH)y;
    DELETE FROM dbo.IDN_OAUTH2_ACCESS_TOKEN WHERE TOKEN_STATE = 'REVOKED' AND ACCESS_TOKEN NOT IN (SELECT ACCESS_TOKEN FROM(SELECT max(ACCESS_TOKEN) ACCESS_TOKEN FROM (SELECT ACCESS_TOKEN, CONSUMER_KEY_ID, AUTHZ_USER, TOKEN_SCOPE_HASH FROM dbo.IDN_OAUTH2_ACCESS_TOKEN WHERE TOKEN_STATE = 'REVOKED') x GROUP BY CONSUMER_KEY_ID, AUTHZ_USER, TOKEN_SCOPE_HASH)y);
     
    SELECT 'AFTER:TOTAL_REVOKED_TOKENS', COUNT(*) FROM dbo.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 dbo.IDN_OAUTH2_ACCESS_TOKEN WHERE TOKEN_STATE = 'EXPIRED';
     
    SELECT 'TO BE RETAINED', COUNT(*) FROM(SELECT max(ACCESS_TOKEN) ACCESS_TOKEN FROM (SELECT ACCESS_TOKEN, CONSUMER_KEY_ID, AUTHZ_USER, TOKEN_SCOPE_HASH FROM dbo.IDN_OAUTH2_ACCESS_TOKEN WHERE TOKEN_STATE = 'EXPIRED') x GROUP BY CONSUMER_KEY_ID, AUTHZ_USER, TOKEN_SCOPE_HASH)y;
     
    DELETE FROM dbo.IDN_OAUTH2_ACCESS_TOKEN WHERE TOKEN_STATE = 'EXPIRED' AND ACCESS_TOKEN NOT IN (SELECT ACCESS_TOKEN FROM(SELECT max(ACCESS_TOKEN) ACCESS_TOKEN FROM (SELECT ACCESS_TOKEN, CONSUMER_KEY_ID, AUTHZ_USER, TOKEN_SCOPE_HASH FROM dbo.IDN_OAUTH2_ACCESS_TOKEN WHERE TOKEN_STATE = 'EXPIRED') x GROUP BY CONSUMER_KEY_ID, AUTHZ_USER, TOKEN_SCOPE_HASH)y);
     
    SELECT 'AFTER:TOTAL_EXPIRED_TOKENS', COUNT(*) FROM dbo.IDN_OAUTH2_ACCESS_TOKEN WHERE TOKEN_STATE = 'EXPIRED';
       
    END

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

    The following script has been tested on Oracle 11g

    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:

    MySQL
    USE 'WSO2AM_DB';   -- Replace WSO2AM_DB with your database name
    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'.'WSO2_TOKEN_CLEANUP_SP'();
     
    -- 'Turn on the event_scheduler'
    SET GLOBAL event_scheduler = ON;
    USE WSO2AM_DB ;    -- Replace WSO2AM_DB with your database name
    GO 
    -- Creates a schedule named CleanupTask.  
    -- Jobs that use this schedule execute every day when the time on the server is 01:00.  
    EXEC sp_add_schedule 
        @schedule_name = N'CleanupTask' , 
        @freq_type = 4, 
        @freq_interval = 1, 
        @active_start_time = 010000 ; 
    GO 
    -- attaches the schedule to the job BackupDatabase 
    EXEC sp_attach_schedule 
       @job_name = N'BackupDatabase', 
       @schedule_name = N'CleanupTask' ; 
    GO
  • No labels