Upgrading to airflow 2.4.0 from 2.3.4 causes NotNullViolation error #26497

ManikandanUV · 2022-09-19T18:13:02Z

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

The text was updated successfully, but these errors were encountered:

boring-cyborg · 2022-09-19T18:13:04Z

Thanks for opening your first issue here! Be sure to follow the issue template!

zachliu · 2022-09-20T15:27:06Z

ah, seems to be the "backend version" of the same issue #26505

----- update -----
maybe not... because i don't see those postgres INSERT errors from my webserver 🤔

daniel-andresen-jimdo · 2022-09-26T06:24:59Z

Hello, we also facing this problem. Existing dags are showing up in the Airflow UI but we can't create a new dag.
We are using dynamic dag creation to create new dags. After defining the new dag I ran the command "airflow dags reserialize" in the scheduler container. In the output I have had the same SQL like mentioned above.

We also use the officel Helm chart to deploy Airflow. We are using a postgres DB as backend. My feeling is that this is not related to #26505.

uranusjr · 2022-09-26T06:35:07Z

I don’t think this is related to #26505, it looks like something in the permission stack not working well with AIP-48 stuff?

sterling-jackson · 2022-09-27T15:39:05Z

We saw the same problem this past week with in an attempted upgrade to 2.4 from 2.1.0. We are using Ubuntu and Postgres in AWS with the scheduler/webserver on an EC2 instance sending work to a Kubernetes cluster on EKS. 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.

DAGs show up in the CLI and in the UI, but can't be found when you try to view job details in the UI. I noticed that the scheduler constantly throws errors about jobs not being in the serialized DAG table.

Manually running airflow dags reserialize immediately throws a bunch of these null constraint errors, suggesting some link, which is what led me to try the following workaround/experiment.

I used airflow db shell to reach into the DB and manipulate the id column in the four permission tables. I made these columns IDENTITY columns using ALTER TABLE ab_view_menu ALTER id ADD GENERATED ALWAYS AS IDENTITY (START WITH 5000). This appears to have resolved the issues. The errors went away and jobs can be viewed/scheduled/ triggered.

This worked and I'm including it here in case it helps diagnose the root cause, but I have no idea if this introduced other issues and am hesitant to promote this to our upper environments without more research.

Scheduler Errors

View DAG in UI Error

ashb · 2022-09-27T15:45:09Z

Question: while this looks bad, does it actually cause any process to exit with an error?

ManikandanUV · 2022-09-27T15:46:00Z

I upgraded our server from Ubuntu 18.04 to 20.04.5 and tried installing 2.4.0 again. The same issue persisted, so we've rolled back to 2.3.4. I have another server on 18.04 that successfully managed to upgrade from 2.3.4 to 2.4, so if you need me to make any comparison between the two servers (although the failing one is now on 20.04) to get to the root cause, let me know.

ashb · 2022-09-27T15:46:57Z

This will be caused by a difference in the rows in the database, not in the OS version.

Is there any chance you could (privately if needed) share a DB dump of the breaking install?

ashb · 2022-09-27T15:47:52Z

@ManikandanUV What version of SQLAlchemy and Flask-AppBuilder do you have installed in each server?

ManikandanUV · 2022-09-27T15:53:17Z

@ashb
Failing server:
Name: SQLAlchemy
Version: 1.4.41
Name: Flask-AppBuilder
Version: 4.1.3

Working server:
Name: SQLAlchemy
Version: 1.4.40
Name: Flask-AppBuilder
Version: 4.1.3

ManikandanUV · 2022-09-27T15:53:41Z

@ashb how do I get the db dump? The servers have different dags, so they're not comparable in that aspect. Also, the working server started with 2.x (not sure which one, may be 2.2)->2.3.4->2.4, but the failing one was 1.x->1.10.15->2.3.4

sfc-gh-mkmak · 2022-09-27T16:25:01Z

@ashb We have experienced that problem as well while testing to 2.4.0 upgrade (from 2.2.4). We use centos 7 for airflow.
SQLAlchemy - 1.4.27, Flask-AppBuilder 4.1.3. When I pulled DAGs I cannot open any of them on WebUI - I got that error: DAG seems to be missing from DagBag. Let me know if you need any additional info.

ephraimbuddy · 2022-09-27T16:26:43Z

@ashb We have experienced that problem as well while testing to 2.4.0 upgrade (from 2.2.4). We use centos 7 for airflow. SQLAlchemy - 1.4.27, Flask-AppBuilder 4.1.3. When I pulled DAGs I cannot open any of them on WebUI - I got that error: DAG seems to be missing from DagBag. Let me know if you need any additional info.

From what version did you start using airflow? Asking so I can try to upgrade from that version to 2.3.4 to 2.4.0

sfc-gh-mkmak · 2022-09-27T16:35:43Z

@ashb We have experienced that problem as well while testing to 2.4.0 upgrade (from 2.2.4). We use centos 7 for airflow. SQLAlchemy - 1.4.27, Flask-AppBuilder 4.1.3. When I pulled DAGs I cannot open any of them on WebUI - I got that error: DAG seems to be missing from DagBag. Let me know if you need any additional info.

From what version did you start using airflow? Asking so I can try to upgrade from that version to 2.3.4 to 2.4.0

I think we started from version 1.10.14. But I saw some tests on version 1.10.12 - so I am not sure from which version our current database works - it can be 1.10.14 or 1.10.12

I tested it on new 2.2.4 deployment and upgraded it to 2.4.0. I have experienced the same problem.

sterling-jackson · 2022-09-27T17:10:53Z

@ashb I attached examples of the two errors I saw in case it's helpful.

troyharvey · 2022-09-29T02:39:23Z

We are seeing the same issue upgrading from 2.3.4 to 2.4.0

[SQL: INSERT INTO ab_view_menu (name) VALUES (%(name)s) RETURNING ab_view_menu.id]
[parameters: {'name': 'DAG:example_branch_datetime_operator_3'}]
(Background on this error at: https://sqlalche.me/e/14/gkpj)
ERROR [airflow.www.fab_security.sqla.manager] Creation of Permission View Error: (psycopg2.errors.NotNullViolation) null value in column "id" violates not-null constraint
DETAIL:  Failing row contains (null, 1, 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': 1, 'view_menu_id': None}]
(Background on this error at: https://sqlalche.me/e/14/gkpj)
ERROR [airflow.www.fab_security.sqla.manager] Creation of Permission View Error: (psycopg2.errors.NotNullViolation) null value in column "id" violates not-null constraint
DETAIL:  Failing row contains (null, 4, 139).

ashb · 2022-09-29T13:07:41Z

Can anyone give us reproduction steps? Cos trying this with a "minimal" 2.3.4 and the upgrading to 2.4.0 neither myself nor @ephraimbuddy have been able to reproduce this, so we're not sure what step we're missing.

(And until we can reproduce it, we can't fix it)

ManikandanUV · 2022-09-29T13:10:38Z

@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

troyharvey · 2022-09-29T14:12:42Z

Thanks for the tip @sterling-jackson. Your suggestion resolved this one for us. If you use this solution alter the START WITH based on the max id in your tables.

ALTER TABLE public.ab_view_menu ALTER id ADD GENERATED ALWAYS AS IDENTITY (START WITH 300);
ALTER TABLE public.ab_permission_view ALTER id ADD GENERATED ALWAYS AS IDENTITY (START WITH 4000);
ALTER TABLE public.ab_permission_view_role ALTER id ADD GENERATED ALWAYS AS IDENTITY (START WITH 3000);

JCoder01 · 2022-09-29T14:13:31Z

Piling on here. I'm seeing the same thing. Seems to maybe be related Datasets?

[2022-09-29T09:55:10.909-0400] {manager.py:420} ERROR - Add View Menu Error: (psycopg2.errors.NotNullViolation) null value in column "id" of relation "ab_view_menu" violates not-null constraint
DETAIL:  Failing row contains (null, Datasets).

We started on version 1.10.xx too and have been upgrading with each release.

Edit: I see now, there is a Datasets views that is added.

joshowen · 2022-09-29T14:23:50Z

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.

ashb · 2022-09-29T15:02:32Z

Thanks @joshowen that's very helpful

stepanof · 2022-10-04T14:11:00Z

ALTER TABLE "public"."ab_permission_view_role" ALTER COLUMN "id" SET DEFAULT
ALTER TABLE "public"."ab_view_menu" ALTER COLUMN "id" SET DEFAULT nextval('ab_view_menu_id_seq'::regclass);

We have the same problem after upgrading from 2.3.4 to 2.4.1

Thanks @joshowen, these commands resolved the problem.

ashb · 2022-10-05T11:39:32Z

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.

ldacey · 2022-10-07T17:17:30Z

Ran into the same issue. We also started in 2017 which should be around version 1.8 something and have upgraded since then. I ran the commands @joshowen posted and now I can open the DAG again.

I am getting a airflow dags reserialize error though (DAGs already exist). Is it safe to delete from the serialize table and then reserialize again? It appears like everything is working fine as is, the only error is when I run that command in a container.

zachliu · 2022-10-18T16:47:22Z

not until a new user couldn't register did i realized i was having this issue and a ton of such error messages in the logs 😂

aabouelleil-seniorlink · 2022-11-17T14:28:28Z

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.

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.

joshowen · 2022-11-28T19:59:09Z

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.

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.

@ashb These must be run before running 0073_2_0_0_prefix_dag_permissions.py

ashb · 2022-11-28T20:00:21Z

Argh! Good catch

ManikandanUV added area:core kind:bug This is a clearly a bug labels Sep 19, 2022

potiuk added this to the Airflow 2.4.1 milestone Sep 20, 2022

uranusjr added this to To do in Data-aware Scheduling (AIP-48) via automation Sep 26, 2022

uranusjr added the AIP-48 Data-aware Scheduling label Sep 26, 2022

ashb removed this from To do in Data-aware Scheduling (AIP-48) Sep 27, 2022

jedcunningham modified the milestones: Airflow 2.4.1, Airflow 2.4.2 Sep 27, 2022

ashb mentioned this issue Oct 5, 2022

Add missing AUTOINC/SERIAL for FAB tables #26885

Merged

eladkal added the affected_version:2.4 Issues Reported for 2.4 label Oct 5, 2022

ashb closed this as completed in #26885 Oct 7, 2022

tirkarthi mentioned this issue Oct 20, 2022

Fail to view grid for new DAGs #27163

Closed

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Upgrading to airflow 2.4.0 from 2.3.4 causes NotNullViolation error #26497

Upgrading to airflow 2.4.0 from 2.3.4 causes NotNullViolation error #26497

ManikandanUV commented Sep 19, 2022 •

edited by uranusjr

Loading

boring-cyborg bot commented Sep 19, 2022

zachliu commented Sep 20, 2022 •

edited

Loading

daniel-andresen-jimdo commented Sep 26, 2022

uranusjr commented Sep 26, 2022

sterling-jackson commented Sep 27, 2022 •

edited

Loading

ashb commented Sep 27, 2022

ManikandanUV commented Sep 27, 2022

ashb commented Sep 27, 2022 •

edited

Loading

ashb commented Sep 27, 2022

ManikandanUV commented Sep 27, 2022

ManikandanUV commented Sep 27, 2022 •

edited

Loading

sfc-gh-mkmak commented Sep 27, 2022

ephraimbuddy commented Sep 27, 2022

sfc-gh-mkmak commented Sep 27, 2022 •

edited

Loading

sterling-jackson commented Sep 27, 2022

troyharvey commented Sep 29, 2022

ashb commented Sep 29, 2022

ManikandanUV commented Sep 29, 2022

troyharvey commented Sep 29, 2022

JCoder01 commented Sep 29, 2022 •

edited

Loading

joshowen commented Sep 29, 2022

ashb commented Sep 29, 2022

stepanof commented Oct 4, 2022

ashb commented Oct 5, 2022 •

edited

Loading

ldacey commented Oct 7, 2022

zachliu commented Oct 18, 2022

aabouelleil-seniorlink commented Nov 17, 2022

joshowen commented Nov 28, 2022

ashb commented Nov 28, 2022

Upgrading to airflow 2.4.0 from 2.3.4 causes NotNullViolation error #26497

Upgrading to airflow 2.4.0 from 2.3.4 causes NotNullViolation error #26497

Comments

ManikandanUV commented Sep 19, 2022 • edited by uranusjr Loading

Apache Airflow version

What happened

What you think should happen instead

How to reproduce

Operating System

Versions of Apache Airflow Providers

Deployment

Deployment details

Anything else

Are you willing to submit PR?

Code of Conduct

boring-cyborg bot commented Sep 19, 2022

zachliu commented Sep 20, 2022 • edited Loading

daniel-andresen-jimdo commented Sep 26, 2022

uranusjr commented Sep 26, 2022

sterling-jackson commented Sep 27, 2022 • edited Loading

ashb commented Sep 27, 2022

ManikandanUV commented Sep 27, 2022

ashb commented Sep 27, 2022 • edited Loading

ashb commented Sep 27, 2022

ManikandanUV commented Sep 27, 2022

ManikandanUV commented Sep 27, 2022 • edited Loading

sfc-gh-mkmak commented Sep 27, 2022

ephraimbuddy commented Sep 27, 2022

sfc-gh-mkmak commented Sep 27, 2022 • edited Loading

sterling-jackson commented Sep 27, 2022

troyharvey commented Sep 29, 2022

ashb commented Sep 29, 2022

ManikandanUV commented Sep 29, 2022

troyharvey commented Sep 29, 2022

JCoder01 commented Sep 29, 2022 • edited Loading

joshowen commented Sep 29, 2022

ashb commented Sep 29, 2022

stepanof commented Oct 4, 2022

ashb commented Oct 5, 2022 • edited Loading

ldacey commented Oct 7, 2022

zachliu commented Oct 18, 2022

aabouelleil-seniorlink commented Nov 17, 2022

joshowen commented Nov 28, 2022

ashb commented Nov 28, 2022

ManikandanUV commented Sep 19, 2022 •

edited by uranusjr

Loading

zachliu commented Sep 20, 2022 •

edited

Loading

sterling-jackson commented Sep 27, 2022 •

edited

Loading

ashb commented Sep 27, 2022 •

edited

Loading

ManikandanUV commented Sep 27, 2022 •

edited

Loading

sfc-gh-mkmak commented Sep 27, 2022 •

edited

Loading

JCoder01 commented Sep 29, 2022 •

edited

Loading

ashb commented Oct 5, 2022 •

edited

Loading