Removing Executions from the Database

Removing data from the database should be treated with great care.

In order to remove an execution or a group of executions until a specific date there are two stored procedures rexeution and rexecuntil.

• rexecution takes execution date time as parameter, the store procedure will remove the execution with a date time equal to

• rexecuntil takes a high limit date time, so all the execution with date time previous to the one specified in the parameter will be removed.

It is highly recommended to execute those stored procedures using MySQL Workbench, but they can be executed in any MySQL compatible client.

How to call the stored procedures

CALL aiq.rexecution(<Execution Date Time>);

CALL aiq.rexecuntil(<Execution Date Time>);

Where to find the desired execution date time?

Click Report option at the left menu and then click Analyze link of the execution containing the execution date time until it is desired to remove.

Once you have clicked Analyze link and the report dashboard is displayed, take a look to the URL and you will notice the exeId parameter, the value is what is needed as the parameter for the stored procedures.

Following the example.

CALL aiq.rexecution(1680636448677);

This will remove the execution with exactly that execution date time.

CALL aiq.rexecuntil(1680636448677);

This will remove all the executions until the specified execution date time. This process can take even hours if there are many executions to be deleted.

Automating the deletion of old executions from database

The following sample bash script will calculate the time stamp for two months prior and then call the stored procedure to delete all executions prior to the calculated date.
delete_old_executions.sh

#!/bin/bash
# Calculate timestamp for 2 months ago
timestamp_2months_back=$(date -u -v-2m +%s)

echo "Removing executions prior to" $timestamp_2months_back
# Call the MySQL stored procedure with the calculated timestamp
MYSQL_PWD='aiq' mysql --user=aiq -h localhost --database=aiq --execute="CALL aiq.rexecuntil('$timestamp_2months_back')"

Make the script executable using (chmod +x delete_old_executions.sh).

Add the script to your crontab to run it at your desired schedule (e.g., nightly):

0 0 * * * /path/to/delete_old_executions.sh

If you’re on Windows OS, use the Task Scheduler to execute a Windows PowerShell script: delete_old_executions.ps1

# Calculate timestamp for 2 months ago
$twoMonthsAgo = (Get-Date).AddMonths(-2)
epochTime = $twoMonthsAgo.ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss")

# Call the MySQL stored procedure with the calculated timestamp
$mysqlCommand = @"mysql.exe --user=aiq --password=aiq -h localhost --database=aiq --execute="CALL aiq.rexecuntil('$epochTime')""@
Invoke-Expression $mysqlCommand