This site contains the documentation that is relevant to older WSO2 product versions and offerings.
For the latest WSO2 documentation, visit https://wso2.com/documentation/.
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:
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.
- Take a backup of the running database.Â
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.
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 ;
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;