Upgrading to airflow 2.4.0 from 2.3.4 causes NotNullViolation error

Apache Airflow version

2.4.0

What happened

Stopped existing processes, upgraded from airflow 2.3.4 to 2.4.0, and ran airflow db upgrade successfully. Upon restarting the services, I’m not seeing any dag runs from the past 10 days. I kick off a new job, and I don’t see it show up in the grid view. Upon checking the systemd logs, I see that there are a lot of postgress errors with webserver. Below is a sample of such errors.

[SQL: INSERT INTO ab_view_menu (name) VALUES (%(name)s) RETURNING ab_view_menu.id]
[parameters: {'name': 'Datasets'}]
(Background on this error at: https://sqlalche.me/e/14/gkpj)
[2022-09-19 14:03:16,183] {manager.py:511} ERROR - Creation of Permission View Error: (psycopg2.errors.NotNullViolation) null value in column "id" violates not-null constraint
DETAIL:  Failing row contains (null, 13, null).

[SQL: INSERT INTO ab_permission_view (permission_id, view_menu_id) VALUES (%(permission_id)s, %(view_menu_id)s) RETURNING ab_permission_view.id]
[parameters: {'permission_id': 13, 'view_menu_id': None}]
(Background on this error at: https://sqlalche.me/e/14/gkpj)
[2022-09-19 14:03:16,209] {manager.py:420} ERROR - Add View Menu Error: (psycopg2.errors.NotNullViolation) null value in column "id" violates not-null constraint
DETAIL:  Failing row contains (null, Datasets).

[SQL: INSERT INTO ab_view_menu (name) VALUES (%(name)s) RETURNING ab_view_menu.id]
[parameters: {'name': 'Datasets'}]
(Background on this error at: https://sqlalche.me/e/14/gkpj)
[2022-09-19 14:03:16,212] {manager.py:511} ERROR - Creation of Permission View Error: (psycopg2.errors.NotNullViolation) null value in column "id" violates not-null constraint
DETAIL:  Failing row contains (null, 17, null).

[SQL: INSERT INTO ab_permission_view (permission_id, view_menu_id) VALUES (%(permission_id)s, %(view_menu_id)s) RETURNING ab_permission_view.id]
[parameters: {'permission_id': 17, 'view_menu_id': None}]
(Background on this error at: https://sqlalche.me/e/14/gkpj)
[2022-09-19 14:03:16,229] {manager.py:420} ERROR - Add View Menu Error: (psycopg2.errors.NotNullViolation) null value in column "id" violates not-null constraint
DETAIL:  Failing row contains (null, DAG Warnings).

[SQL: INSERT INTO ab_view_menu (name) VALUES (%(name)s) RETURNING ab_view_menu.id]
[parameters: {'name': 'DAG Warnings'}]
(Background on this error at: https://sqlalche.me/e/14/gkpj)
[2022-09-19 14:03:16,232] {manager.py:511} ERROR - Creation of Permission View Error: (psycopg2.errors.NotNullViolation) null value in column "id" violates not-null constraint
DETAIL:  Failing row contains (null, 17, null).

[SQL: INSERT INTO ab_permission_view (permission_id, view_menu_id) VALUES (%(permission_id)s, %(view_menu_id)s) RETURNING ab_permission_view.id]
[parameters: {'permission_id': 17, 'view_menu_id': None}]
(Background on this error at: https://sqlalche.me/e/14/gkpj)
[2022-09-19 14:03:16,250] {manager.py:511} ERROR - Creation of Permission View Error: (psycopg2.errors.NotNullViolation) null value in column "id" violates not-null constraint
DETAIL:  Failing row contains (null, 13, 23).

I tried running airflow db check, init, check-migration, upgrade without any errors, but the errors still remain.

Please let me know if I missed any steps during the upgrade, or if this is a known issue with a workaround.

What you think should happen instead

All dag runs should be visible

How to reproduce

upgrade airflow, upgrade db, restart the services

Operating System

Ubuntu 18.04.6 LTS

Versions of Apache Airflow Providers

No response

Deployment

Official Apache Airflow Helm Chart

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

Issue Analytics

State:
Created a year ago
Reactions:4
Comments:29 (14 by maintainers)

Top GitHub Comments

9reactions

ashbcommented, Oct 5, 2022

Okay, I’ve found the source of the confusion, and the path needed to trigger this behaviour.

Run airflow webserver with < 1.10.13 in RBAC mode, where FAB creates it’s tables.

In 1.10.13 we introduces a migration that creates the tables with the server_default but that migration only did anything if the tables didn’t already exist. But the tables created by the FAB model have a default (but not a server_default).

Oh, and the final bit of the puzzle, in 2.4 we finally “took control” of the FAB security models in to airflow and those do not have the default set.

I’ll work on a new migration to fix this up.

8reactions

joshowencommented, Sep 29, 2022

We started with 1.8.xx, went to 1.9.xx, 1.10.xx, and somehow all of our FAB tables ended up without sequences set for their IDs, but had the sequences created. We were seeing similar issues in 2.4.0, and manually ran:

ALTER TABLE "public"."ab_permission_view" ALTER COLUMN "id" SET DEFAULT nextval('ab_permission_view_id_seq'::regclass);
ALTER TABLE "public"."ab_permission" ALTER COLUMN "id" SET DEFAULT nextval('ab_permission_id_seq'::regclass);
ALTER TABLE "public"."ab_permission_view_role" ALTER COLUMN "id" SET DEFAULT nextval('ab_permission_view_role_id_seq'::regclass);
ALTER TABLE "public"."ab_register_user" ALTER COLUMN "id" SET DEFAULT nextval('ab_register_user_id_seq'::regclass);
ALTER TABLE "public"."ab_role" ALTER COLUMN "id" SET DEFAULT nextval('ab_role_id_seq'::regclass);
ALTER TABLE "public"."ab_user" ALTER COLUMN "id" SET DEFAULT nextval('ab_user_id_seq'::regclass);
ALTER TABLE "public"."ab_user_role" ALTER COLUMN "id" SET DEFAULT nextval('ab_user_role_id_seq'::regclass);
ALTER TABLE "public"."ab_view_menu" ALTER COLUMN "id" SET DEFAULT nextval('ab_view_menu_id_seq'::regclass);

Which resolved our issue.

Top Results From Across the Web

Upgrading to airflow 2.4.0 from 2.3.4 causes NotNullViolation ...

We tried both airflow db upgrade and airflow db reset . Same problem as OP with both approaches and we couldn't view/schedule/trigger jobs....

Airflow database upgrade fails with "psycopg2.errors ... - GitHub

When I run airflow db upgrade I get the following error: INFO ... NotNullViolation: column "map_index" of relation "task_instance" contains null value"s # ......

Upgrading Airflow to a newer version - Apache Airflow

This can for example be caused by a broken network connection between your CLI and the database while the migration happens, so taking...

DAG seems to be missing from DagBag error in Airflow 2.4.0

UPDATE : Still unresolved. The new dag is shown at Airflow UI and it can be activated. Running the dag is not possible....

commits - The Mail Archive

2022/09/26 [airflow] 03/03: Add release notes for 2.4.1 jedcunningham ... #26497: Upgrading to airflow 2.4.0 from 2.3.4 causes NotNullViolation error GitBox ...