airflow: 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

About this issue

Original URL
State: closed
Created 2 years ago
Reactions: 4
Comments: 29 (17 by maintainers)

Most upvoted comments

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.

ashb on Oct 5, 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.

joshowen on Sep 29, 2022

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.

We ran into this issue upgrading from 2.3.1 to 2.4.1 so it doesn’t seem the issue is fixed yet. These table alterations resolved the problem though.

aabouelleil-seniorlink on Nov 17, 2022

@ashb try our upgrade path, may be that’s the key. I have two servers, one started at 1.x and other started at 2.x. The 2.x server upgraded to 2.3.4 and 2.4 without issues, where as the 1.x server upgraded to 2.3.4 and is now failing 2.4 upgrade from 2.3.4

ManikandanUV on Sep 29, 2022