Set up Database cleanup task for WSO2 Identity Provider
According to the use cases in wso2is database grow up happens very fast and cleaning the database also expensive task. Therefore wso2is introduce of store procedure for database cleanup task. Mainly session data, token expired and registry logs will be clean form these procedures.
These examples are for mysql database.
Note: Useful db queries in mysql database
To check the table growth use below command in mysql database
To get the last value from the table
SELECT * FROM IDN_AUTH_SESSION_STORE ORDER BY TIME_CREATED ASC LIMIT 1;
To get the first value of the table
SELECT * FROM IDN_AUTH_SESSION_STORE ORDER BY TIME_CREATED DECS LIMIT 1;
There only 2 steps for execute the Data cleanup task. This can be execute by the user or can schedule the execution for given time.
Step 01: Source mysql-session-data-cleanup.sql Identity data base of wso2is
Ex: mysql> source /home/ubuntu/<cleanup_task_scripts>
cleanup_task_scripts can be get from wso2/carbon-identity-framework git repository.
Step 02: In this step you can execute the script at a time or can schedule a procedure for given time. Recommendation is to schedule the store procedure by suitable time considering the data growth of the database
If the store procedure is execute one time, call the procedure by, call <name_store_procedure>
Ex: call CLEANUP_SESSION_DATA();
If the user schedule the store procedure, execute the following in mysql database to set up the store procedure.
USE `<DATABASE_NAME>`;
DROP EVENT IF EXISTS <EVENT_NAME>;
CREATE EVENT <PROCEDURE_NAME>
ON SCHEDULE
EVERY <TIME_DDURATION> STARTS ‘<START_TIME>’
DO
CALL `<DATABASE_NAME>`.<PROCEDURE_NAME>();
Note: Before scheduling the procedure task you have to enable the event schedule permission for the relevant database. If your using the AWS RDS please refer this to get configuration details.
Now lets check example of how to set the clean up task of session data, token and registry logs. Here the DB is myql-5.7.0
How to schedule the SESSION DATA cleanup task
Step 01: Execute the session data cleanup task against IDENTITY DB.
Note: We can decide the which session and operation data to be remove according to how old they are. Update below section of the script.
Step 02: schedule the procedure
Note: sometimes syntax error can occur with given DB query. In case use image for create the query.
USE `IDENTITY_DB`;
DROP EVENT IF EXISTS cleanup_session_data_event;
CREATE EVENT CLEANUP_SESSION_DATA
ON SCHEDULE
EVERY 12 HOUR STARTS ‘2020–03–06 00:00.00’
DO
CALL `IDENTITY_DB`.CLEANUP_SESSION_DATA();
How to schedule the token DATA cleanup task
Step 01: Execute the token data cleanup task against IDENTITY DB.
Note: SET enableAudit = FALSE; if not to send the clean token to temporary table. This is useful when running long run test with limited DB storage. In production environment.
Step 02: Schedule the procedure.
Note: sometimes syntax error can occur with given DB query. In case use image for create the query.
USE IDENTITY_DB;
DROP EVENT IF EXISTS cleanup_token_data_event;
CREATE EVENT WSO2_TOKEN_CLEANUP_SP
ON SCHEDULE
EVERY 12 HOUR STARTS ‘2020–02–24 00:00.00’
DO
CALL `IDENTITY_DB`.WSO2_TOKEN_CLEANUP_SP();
How to schedule the Registry logs cleanup task
Step 01: Execute the registry logs cleanup script and confirmation code cleanup against REGISTRY DB.
Step 02: Schedule the procedure.
Note: sometimes syntax error can occur with given DB query. In case use image for create the query.
USE REG_DB;
DROP EVENT IF EXISTS cleanup_reg_log_data_event;
CREATE EVENT WSO2_REG_LOG_CLEANUP
ON SCHEDULE
EVERY 12 HOUR STARTS ‘2020–02–24 00:00.00’
DO
CALL `REG_DB`.WSO2_REG_LOG_CLEANUP();DROP EVENT IF EXISTS cleanup_confirmation_code_data_event;
CREATE EVENT WSO2_CONFIRMATION_CODE_CLEANUP
ON SCHEDULE
EVERY 12 HOUR STARTS ‘2020–02–24 00:00.00’
DO
CALL `REG_DB`.WSO2_CONFIRMATION_CODE_CLEANUP();
Verify the task:
See the logs to check the schedule procedures has been triggered according to the given time
Sample for database grow with database clean up task and without database clean up task
