Repository navigation
Error when running airflow db clean on DAG table #52147
Description
Apache Airflow version
3.0.2
If "Other Airflow 2 version" selected, which one?
No response
What happened?
When I tried running airflow db clean -t dag --clean-before-timestamp 2025-06-25 I saw the following error
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/usr/local/bin/airflow", line 10, in <module>
sys.exit(main())
^^^^^^
File "/opt/airflow/airflow-core/src/airflow/__main__.py", line 55, in main
args.func(args)
File "/opt/airflow/airflow-core/src/airflow/cli/cli_config.py", line 48, in command
return func(*args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^
File "/opt/airflow/airflow-core/src/airflow/utils/cli.py", line 112, in wrapper
return f(*args, **kwargs)
^^^^^^^^^^^^^^^^^^
File "/opt/airflow/airflow-core/src/airflow/utils/providers_configuration_loader.py", line 55, in wrapped_function
return func(*args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^
File "/opt/airflow/airflow-core/src/airflow/cli/commands/db_command.py", line 279, in cleanup_tables
run_cleanup(
File "/opt/airflow/airflow-core/src/airflow/utils/session.py", line 101, in wrapper
return func(*args, session=session, **kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/opt/airflow/airflow-core/src/airflow/utils/db_cleanup.py", line 558, in run_cleanup
_cleanup_table(
File "/opt/airflow/airflow-core/src/airflow/utils/db_cleanup.py", line 407, in _cleanup_table
_cleanup_single_table(
File "/opt/airflow/airflow-core/src/airflow/utils/db_cleanup.py", line 386, in _cleanup_single_table
_do_delete(
File "/opt/airflow/airflow-core/src/airflow/utils/db_cleanup.py", line 274, in _do_delete
raise e
File "/opt/airflow/airflow-core/src/airflow/utils/db_cleanup.py", line 270, in _do_delete
session.execute(delete)
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 1717, in execute
result = conn._execute_20(statement, params or {}, execution_options)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1710, in _execute_20
return meth(self, args_10style, kwargs_10style, execution_options)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/sql/elements.py", line 334, in _execute_on_connection
return connection._execute_clauseelement(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1577, in _execute_clauseelement
ret = self._execute_context(
^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1953, in _execute_context
self._handle_dbapi_exception(
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2134, in _handle_dbapi_exception
util.raise_(
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
raise exception
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1910, in _execute_context
self.dialect.do_execute(
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.IntegrityError: (psycopg2.errors.ForeignKeyViolation) update or delete on table "dag_version" violates foreign key constraint "task_instance_dag_version_id_fkey" on table "task_instance"
DETAIL: Key (id)=(01979cd6-24b4-739e-83a7-ca5876085ee8) is still referenced from table "task_instance".
[SQL: DELETE FROM dag USING _airflow_deleted__dag__20250623125028 WHERE dag.dag_id = _airflow_deleted__dag__20250623125028.dag_id]
(Background on this error at: https://sqlalche.me/e/14/gkpj)
root@15fa45f1b905:/opt/airflow#
What you think should happen instead?
No response
How to reproduce
Execute below command
airflow db clean -t dag --clean-before-timestamp 2025-06-25
Operating System
os
Versions of Apache Airflow Providers
No response
Deployment
Astronomer
Deployment details
No response
Anything else?
No response
Are you willing to submit PR?
- Yes I am willing to submit a PR!
Code of Conduct
- I agree to follow this project's Code of Conduct
Activity
[-]Running airflow db clean for DAG table is breaking[/-][+]Error when running airflow db clean on DAG table[/+]aaron-y-chen commentedon Jun 24, 2025
Hi, I'm interested in working on this, could you assign it to me? :)
aaron-y-chen commentedon Jun 26, 2025
Hi, after investigation, I found your command
airflow db clean -t dag --clean-before-timestamp 2025-06-25worked fine a month ago. However, this issue #49580 and PR #51101, changedondeletefrom "CASCADE" to "RESTRICT", which caused your issue.https://github.com/apache/airflow/pull/51101/files#diff-f660147b2482fe9eb7c256a6e9b5c0d8c0623fc31beaf5048e476e9bd1b7823bL568-R571
I'm not sure what the best way to solve this issue is; maybe it needs discussed with other community members. But as a temporary workaround, you can delete the
task_instancetable first.eladkal commentedon Jul 3, 2025
cc @sunank200 looks like a regression
Michael-cd30 commentedon Aug 27, 2025
Same as #50601 ?
Response "From Airflow 3.0 onwards, tasks can not directly access DB. You will need to run airflow db clean command outside of task"
It seems rather Kafkaesque to me to have to set up another orchestrator alongside the Airflow orchestrator to clean up its working base.
potiuk commentedon Aug 27, 2025
No. completely different issue.
Not sure why you'd say it, It's ok to do cleanups, log handling maintenance etc. using cron or whatever way you normally maintain your applicattion (for example kubernetes jobs etc.).
Airflow orchestration is for data related pipelines. And it's even done by different roles and people - and this is part of Airflow Security Model. Dag Authoring is done by Dag Authors, Airflow Deployment management is done by Deployment Managers. See here https://airflow.apache.org/docs/apache-airflow/stable/security/security_model.html. And Dag Authors are not responsible and should not have acess to do the same things that Deployment Manager does.
This is not "kafkesque" thing, this is security feature.
And if you want to bypass that because you fill like it - feel free. You can for example configure your deployment in the way that your workers make an ssh connection to airflow api-server and execute "airflow db clean" command remotely. This is all entirely possible (but of course only if you intentionally want to break security perimeters). In this case you could use Airflow to schedule it.
vatsrahul1001 commentedon Aug 28, 2025
I am not able to replicate it anymore