This documentation is for WSO2 Identity Server 5.2.0. View documentation for the latest release.
Page Comparison - Removing Unused Tokens from the Database (v.5 vs v.6) - Identity Server 5.2.0 - WSO2 Documentation

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

  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 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.

    Code Block
    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 WSO2IS_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 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:

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