Removing Unused Tokens from the Database

As you use the WSO2 Identity Server (IS), 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 that you 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 WSO2IS_DB;      -- Replace DATABASE_NAME 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 DELETION BECAUSE TOKENS CAN BE CACHED    [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 TO KEEP TRACK OF ALL THE DELETED TOKENS USING A TEMP TABLE    [DEFAULT : TRUE]
   SET counter = 0;
   SET autocommit = 0;            -- SET AUTO-COMMIT FALSE TO IMPROVE BATCH PROCESSING [DEFAULT : 0]
    
    
   SET SQL_MODE='ALLOW_INVALID_DATES';                                -- UNCOMMENT THIS IF MYSQL THROWS "ERROR 1067 (42000): Invalid default value for 'TIME_CREATED'"
   -- SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;      -- SET THE 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 (!(SELECT SIGN(OAT_VALIDITY_PERIOD) < 0 AND (OAT_TOKEN_STATE='ACTIVE')))
       THEN
   	    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 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 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 ;

4. Once the cleanup is over, start the WSO2 Identity Server 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:

   USE 'WSO2IS_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 'WSO2IS_DB'.'WSO2_TOKEN_CLEANUP_SP'();
    
   -- 'Turn on the event_scheduler'
   SET GLOBAL event_scheduler = ON;