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. 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]: 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 . dolphinscheduler.t_ds_task_instance foreign_key_instance_id To resolve this issue, you can follow these steps: 1. Remove the Foreign Key Constraint: 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; 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 . referencing_table t_ds_task_instance 2. Truncate the Table: 2. Truncate the Table: After removing the foreign key constraint, you can now truncate the table. TRUNCATE TABLE dolphinscheduler.t_ds_task_instance; TRUNCATE TABLE dolphinscheduler.t_ds_task_instance; 3. Re-add the Foreign Key Constraint: 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); 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: SET FOREIGN_KEY_CHECKS = 0; Final Solution Final Solution SET FOREIGN_KEY_CHECKS = 0;TRUNCATE TABLE dolphinscheduler.t_ds_task_instance;SET FOREIGN_KEY_CHECKS = 1; 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.

The code in this story is for educational purposes. The readers are solely responsible for whatever they build with it.

Walkthroughs, tutorials, guides, and tips. This story will teach you how to do something new or how to do something better.

A Guide to Deploying Dolphinscheduler With Docker

A Comprehensive Guide to Building DolphinScheduler 3.2.0 Production-Grade Cluster Deployment

Read My Stories

Too Long; Didn't Read

Make resilience your competitive advantage

How to Regularly Delete Log Instances in DolphinScheduler

About Author

Comments

TOPICS

THIS ARTICLE WAS FEATURED IN

Related Stories

Untitled Story

Inside Tongcheng Travel’s Journey to a Unified Data Channel with Apache SeaTunnel

How to Seamlessly Transfer From Airflow to Apache DolphinScheduler With Air2phin

A Comprehensive Guide to Building DolphinScheduler 3.2.0 Production-Grade Cluster Deployment

How to Enable Auto-Start for Apache DolphinScheduler

Solutions for Upgrading Apache DolphinScheduler from Version 1.3.4 to 3.1.2

Inside Tongcheng Travel’s Journey to a Unified Data Channel with Apache SeaTunnel

How to Seamlessly Transfer From Airflow to Apache DolphinScheduler With Air2phin

A Comprehensive Guide to Building DolphinScheduler 3.2.0 Production-Grade Cluster Deployment

How to Enable Auto-Start for Apache DolphinScheduler

Solutions for Upgrading Apache DolphinScheduler from Version 1.3.4 to 3.1.2

Light-Mode

Classic

Newspaper

Dark-Mode

Neon Noir

Minty

HN StartUps