How to Regularly Delete Log Instances in DolphinScheduler

After Apache DolphinScheduler has been running for a period of time, the instance scheduling logs increase significantly and need to be cleaned up regularly.

SQL Error [1701] [42000]: Cannot truncate a table referenced in a foreign key constraint (`dolphinscheduler`.`t_ds_task_instance`, CONSTRAINT `foreign_key_instance_id`) How to delete DolphinScheduler instance logs

SQL Error [1701] [42000] indicates an attempt to truncate (delete all rows from) a table that is referenced by a foreign key constraint in another table. In this case, the dolphinscheduler.t_ds_task_instance the foreign key constraint references table foreign_key_instance_id.

To resolve this issue, you can follow these steps:

1. Remove the Foreign Key Constraint:

Before deleting the data from the table, you must remove or disable the foreign key constraint referencing this table. This ensures that the database integrity is not compromised. For example, you can use the following SQL statement to remove the foreign key constraint (adjust the syntax according to your database system):

ALTER TABLE referencing_table DROP FOREIGN KEY foreign_key_instance_id;

Here, referencing_table is the name of the table that references t_ds_task_instance.

2. Truncate the Table:

After removing the foreign key constraint, you can now truncate the table.

TRUNCATE TABLE dolphinscheduler.t_ds_task_instance;

3. Re-add the Foreign Key Constraint:

After truncating the table, if needed, you can re-add the foreign key constraint.

ALTER TABLE referencing_table ADD CONSTRAINT foreign_key_instance_id FOREIGN KEY (instance_id) REFERENCES t_ds_task_instance(id);

Please note that before performing these operations, make sure you have sufficient permissions and understand the impact of these operations on your database. It is recommended to back up the database before operating in a production environment.

Additionally, if you are using a database like MySQL, you can also consider using SET FOREIGN_KEY_CHECKS = 0; to temporarily disable foreign key constraints, then perform the truncate operation, and finally re-enable the foreign key constraints:

Final Solution

SET FOREIGN_KEY_CHECKS = 0;TRUNCATE TABLE dolphinscheduler.t_ds_task_instance;SET FOREIGN_KEY_CHECKS = 1;

But note that this method temporarily violates database integrity, so use it with caution. The community recommends using the open API to safely delete workflow instances and task instances. The open API can delete the running logs of task instances.