AssertionError: Dependency rule tried to blank-out primary key column 'ab_permission.id' on instance ' #21407

pingzh · 2022-02-08T01:09:59Z

Apache Airflow version

main (development)

What happened

airflow webserver cannot start due to error

Traceback (most recent call last):
  File "/Users/ping_zhang/miniforge3/envs/apache-airflow/lib/python3.9/site-packages/gunicorn/arbiter.py", line 589, in spawn_worker
    worker.init_process()
  File "/Users/ping_zhang/miniforge3/envs/apache-airflow/lib/python3.9/site-packages/gunicorn/workers/base.py", line 134, in init_process
    self.load_wsgi()
  File "/Users/ping_zhang/miniforge3/envs/apache-airflow/lib/python3.9/site-packages/gunicorn/workers/base.py", line 146, in load_wsgi
    self.wsgi = self.app.wsgi()
  File "/Users/ping_zhang/miniforge3/envs/apache-airflow/lib/python3.9/site-packages/gunicorn/app/base.py", line 67, in wsgi
    self.callable = self.load()
  File "/Users/ping_zhang/miniforge3/envs/apache-airflow/lib/python3.9/site-packages/gunicorn/app/wsgiapp.py", line 58, in load
    return self.load_wsgiapp()
  File "/Users/ping_zhang/miniforge3/envs/apache-airflow/lib/python3.9/site-packages/gunicorn/app/wsgiapp.py", line 48, in load_wsgiapp
    return util.import_app(self.app_uri)
  File "/Users/ping_zhang/miniforge3/envs/apache-airflow/lib/python3.9/site-packages/gunicorn/util.py", line 412, in import_app
    app = app(*args, **kwargs)
  File "/Users/ping_zhang/airlab/repos/airflow/airflow/www/app.py", line 149, in cached_app
    app = create_app(config=config, testing=testing)
  File "/Users/ping_zhang/airlab/repos/airflow/airflow/www/app.py", line 136, in create_app
    sync_appbuilder_roles(flask_app)
  File "/Users/ping_zhang/airlab/repos/airflow/airflow/www/app.py", line 66, in sync_appbuilder_roles
    flask_app.appbuilder.sm.sync_roles()
  File "/Users/ping_zhang/airlab/repos/airflow/airflow/www/security.py", line 544, in sync_roles
    self.clean_perms()
  File "/Users/ping_zhang/airlab/repos/airflow/airflow/www/security.py", line 406, in clean_perms
    sesh.commit()
  File "<string>", line 2, in commit
  File "/Users/ping_zhang/miniforge3/envs/apache-airflow/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 1423, in commit
    self._transaction.commit(_to_root=self.future)
  File "/Users/ping_zhang/miniforge3/envs/apache-airflow/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 829, in commit
    self._prepare_impl()
  File "/Users/ping_zhang/miniforge3/envs/apache-airflow/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 808, in _prepare_impl
    self.session.flush()
  File "/Users/ping_zhang/miniforge3/envs/apache-airflow/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 3255, in flush
    self._flush(objects)
  File "/Users/ping_zhang/miniforge3/envs/apache-airflow/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 3395, in _flush
    transaction.rollback(_capture_exception=True)
  File "/Users/ping_zhang/miniforge3/envs/apache-airflow/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "/Users/ping_zhang/miniforge3/envs/apache-airflow/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/Users/ping_zhang/miniforge3/envs/apache-airflow/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 3355, in _flush
    flush_context.execute()
  File "/Users/ping_zhang/miniforge3/envs/apache-airflow/lib/python3.9/site-packages/sqlalchemy/orm/unitofwork.py", line 453, in execute
    rec.execute(self)
  File "/Users/ping_zhang/miniforge3/envs/apache-airflow/lib/python3.9/site-packages/sqlalchemy/orm/unitofwork.py", line 574, in execute
    self.dependency_processor.process_deletes(uow, states)
  File "/Users/ping_zhang/miniforge3/envs/apache-airflow/lib/python3.9/site-packages/sqlalchemy/orm/dependency.py", line 552, in process_deletes
    self._synchronize(
  File "/Users/ping_zhang/miniforge3/envs/apache-airflow/lib/python3.9/site-packages/sqlalchemy/orm/dependency.py", line 610, in _synchronize
    sync.clear(dest, self.mapper, self.prop.synchronize_pairs)
  File "/Users/ping_zhang/miniforge3/envs/apache-airflow/lib/python3.9/site-packages/sqlalchemy/orm/sync.py", line 86, in clear
    raise AssertionError(
AssertionError: Dependency rule tried to blank-out primary key column 'ab_view_menu.id' on instance '<Resource at 0x111da6b50>'

What you expected to happen

airflow webserver cannot start due to error

How to reproduce

Error in CI:
https://github.com/apache/airflow/runs/5101435727?check_suite_focus=true#step:7:2424

Error from my local:

i have the latest airflow main commit: 471e368eacbcae1eedf9b7e1cb4290c385396ea9

python version: 3.9.7
Flask related pkg:

❯ pip list | grep Flas
Flask                           1.1.4
Flask-AppBuilder                3.4.4
Flask-Babel                     2.0.0
Flask-Caching                   1.10.1
Flask-JWT-Extended              3.25.1
Flask-Login                     0.4.1
Flask-OpenID                    1.3.0
Flask-SQLAlchemy                2.5.1
Flask-WTF                       0.14.3

❯ pip list | grep SQ
Flask-SQLAlchemy                2.5.1
SQLAlchemy                      1.4.9
SQLAlchemy-JSONField            1.0.0
SQLAlchemy-Utils                0.38.2

database: mysql

mysql version:

❯ mysql -V
mysql  Ver 8.0.28 for macos12.0 on arm64 (Homebrew)

the aiflow.cfg and webserver conf are auto generated, i did not change except sql_alchemy_conn

Operating System

Apple M1 Max, version: 12.2

Versions of Apache Airflow Providers

No response

Deployment

Other

Deployment details

local dev

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:

pingzh · 2022-02-08T01:42:19Z

i think it should be related to this PR #21296

after I reseted to a commit before that PR. it worked.

How to reproduce:

drop my mysql database
pull latest main branch
airflow db init
airflow webserver --> will fail
git reset --hard e1a2717dae4614dbd3523bb1baacdecbdeadfcac # reset commit back to that pr.
airflow webserver --> fails again
git reset --hard 40d3a76a9bce2360b951f2e990cba571c5f51a76 # 1 commit before that pr
airflow webserver --> works

ashb · 2022-02-08T09:52:26Z

Looking.

ashb · 2022-02-08T09:55:45Z

I'm unable to reproduce this on commit 8f81b9a with a totally empty breeze + mysql 5.7 environment.

What version of FAB and SQLA do you have @pingzh ?

ashb · 2022-02-08T10:52:34Z

Steps I ran:

./breeze stop
./breeze -b mysql

Then in container I ran:

airflow db init
airflow users create -e admin@local -u admin -p admin -r Admin -f Admin -l Admin
airflow webserver

And that launched Without error.

I do see these in the log though

[2022-02-08 10:51:59,436] {manager.py:534} WARNING - Refused to delete permission view, assoc with role exists Permissions.menu_access Admin
[2022-02-08 10:51:59,461] {manager.py:534} WARNING - Refused to delete permission view, assoc with role exists Permissions.menu_access Admin
[2022-02-08 10:51:59,474] {manager.py:534} WARNING - Refused to delete permission view, assoc with role exists Permissions.menu_access Admin
[2022-02-08 10:51:59,529] {manager.py:534} WARNING - Refused to delete permission view, assoc with role exists Permissions.menu_access Admin
[2022-02-08 10:51:59,593] {manager.py:534} WARNING - Refused to delete permission view, assoc with role exists DAG Runs.can_create Admin
[2022-02-08 10:51:59,621] {manager.py:534} WARNING - Refused to delete permission view, assoc with role exists DAG Runs.can_create Admin
[2022-02-08 10:51:59,658] {manager.py:534} WARNING - Refused to delete permission view, assoc with role exists DAG Runs.can_create Admin
[2022-02-08 10:51:59,708] {manager.py:534} WARNING - Refused to delete permission view, assoc with role exists DAG Runs.can_create Admin
[2022-02-08 10:51:59,723] {manager.py:534} WARNING - Refused to delete permission view, assoc with role exists Task Instances.can_edit Admin
[2022-02-08 10:51:59,756] {manager.py:534} WARNING - Refused to delete permission view, assoc with role exists Task Instances.can_edit Admin
[2022-02-08 10:51:59,791] {manager.py:534} WARNING - Refused to delete permission view, assoc with role exists Task Instances.can_edit Admin
[2022-02-08 10:51:59,834] {manager.py:534} WARNING - Refused to delete permission view, assoc with role exists Task Instances.can_edit Admin
```)

pingzh · 2022-02-08T19:52:11Z

thanks for checking @ashb

i am not using breeze somehow my breeze is super slow on my Mac even i have allocate lots of CPU and memory.

Did you see Deleted %s faulty permissions in your websever log? since the error came up in the sesh.delete(perm) when it tries to clean_perms

❯ pip list | grep Flas
Flask                           1.1.4
Flask-AppBuilder                3.4.4
Flask-Babel                     2.0.0
Flask-Caching                   1.10.1
Flask-JWT-Extended              3.25.1
Flask-Login                     0.4.1
Flask-OpenID                    1.3.0
Flask-SQLAlchemy                2.5.1
Flask-WTF                       0.14.3

❯ pip list | grep SQ
Flask-SQLAlchemy                2.5.1
SQLAlchemy                      1.4.9
SQLAlchemy-JSONField            1.0.0
SQLAlchemy-Utils                0.38.2

potiuk · 2022-02-08T20:01:32Z

i am not using breeze somehow my breeze is super slow on my Mac even i have allocate lots of CPU and memory.

If you have M1 then yes. it is super slow (still) but ARM support is coming (it's emulated for now due to dependencies)- but we are closer and closer to get M1 native images.

pingzh · 2022-02-08T20:03:16Z

@ashb ah, i know how to reproduce it in the local.

If i don't do airflow users create -e admin@local -u admin -p admin -r Admin -f Admin -l Admin after I init the db, airflow webserver won't start, due to: AssertionError: Dependency rule tried to blank-out primary key column 'ab_permission.id' on instance '

but, if i created a user, it worked fine.

pingzh · 2022-02-08T20:03:30Z

i am not using breeze somehow my breeze is super slow on my Mac even i have allocate lots of CPU and memory.

If you have M1 then yes. it is super slow (still) but ARM support is coming (it's emulated for now due to dependencies)- but we are closer and closer to get M1 native images.

yes, mine is M1.

potiuk · 2022-02-08T20:05:05Z

yes, mine is M1.

I feel your pain on my new ARM Mac I bought in December :) . I bought it specifically to feel the pain and have more incentive to fix it :)

pingzh · 2022-02-09T04:36:06Z

@ashb @potiuk looks like lots of PR's CIs are blocked by this error. for example:

#21442
#21332
#21441

can you share some insights on how to fix this? (starting webserver without needing to create a user first)

or

~~can we update the CI to ensure a user is created before trying to start the webserver?~~

--- update

i saw the user was created https://github.com/apache/airflow/runs/5119349678?check_suite_focus=true#step:7:1491

thanks

ashb · 2022-02-09T10:43:27Z

@ashb ah, i know how to reproduce it in the local.

If i don't do airflow users create -e admin@local -u admin -p admin -r Admin -f Admin -l Admin after I init the db, airflow webserver won't start, due to: AssertionError: Dependency rule tried to blank-out primary key column 'ab_permission.id' on instance '

but, if i created a user, it worked fine.

No luck -- doesn't work for me :/ Webserver still launches. This is what I see in the output

/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/relationships.py:3463 SAWarning: relationship 'DagRun.serialized_dag' will copy column serialized_dag.dag_id to column dag_run.dag_id, which conflicts with relationship(s): 'BaseXCom.dag_run' (copies xcom.dag_id to dag_run.dag_id). If this is not the intention, consider if these relationships should be linked with back_populates, or if viewonly=True should be applied to one or more if they are read-only. For the less common case that foreign key constraints are partially overlapping, the orm.foreign() annotation can be used to isolate the columns that should be written towards.   The 'overlaps' parameter may be used to remove this warning. (Background on this error at: http://sqlalche.me/e/14/qzyx)
/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/relationships.py:3463 SAWarning: relationship 'SerializedDagModel.dag_runs' will copy column serialized_dag.dag_id to column dag_run.dag_id, which conflicts with relationship(s): 'BaseXCom.dag_run' (copies xcom.dag_id to dag_run.dag_id). If this is not the intention, consider if these relationships should be linked with back_populates, or if viewonly=True should be applied to one or more if they are read-only. For the less common case that foreign key constraints are partially overlapping, the orm.foreign() annotation can be used to isolate the columns that should be written towards.   The 'overlaps' parameter may be used to remove this warning. (Background on this error at: http://sqlalche.me/e/14/qzyx)
/opt/airflow/airflow/www/fab_security/sqla/manager.py:102 SADeprecationWarning: The from_engine() method on Inspector is deprecated and will be removed in a future release.  Please use the sqlalchemy.inspect() function on an Engine or Connection in order to acquire an Inspector. (deprecated since: 1.4)
[2022-02-09 10:42:51,209] {manager.py:792} WARNING - No user yet created, use flask fab command to do it.
[2022-02-09 10:42:51,339] {manager.py:534} WARNING - Refused to delete permission view, assoc with role exists Permissions.menu_access Admin
[2022-02-09 10:42:51,495] {manager.py:534} WARNING - Refused to delete permission view, assoc with role exists DAG Runs.can_create Admin
[2022-02-09 10:42:51,610] {manager.py:534} WARNING - Refused to delete permission view, assoc with role exists Task Instances.can_edit Admin

potiuk · 2022-02-09T13:47:09Z

Yes. I just started to look at it - a lot of docker-compose's is actually failing on that one. We also have a warning generated due to the sqlalchemy 1.4 when we run breeze start-ariflow which I have a feeling is related to the 1.4 + FAB migration + fact that we are using copied version of security manager from fab + that we have the workaround for SQLAlchemy 1.4 problems with MSSQL implemented #21303

I think all this compounds to the warnings/errors we have. and Dependency rule tried to blank-out primary key column 'ab_permission.id' is just one of the manifestations of the same problem:

potiuk · 2022-02-09T13:55:07Z

I guess it is also related to #21167 (comment) @dstandish @blag @ashb ? I have not been too involved in the FAB "extraction" but mybe those issue can be handled at the same time? Seems they are releated.

Apparently, sync_roles script might lead to leaving orphan rows during syncing the permission model. Since the permission model is only used as "static" model mostly, making sure that there are no orphans left via modifying the cascade method seems to be a good solution - it might lead to deletion and recration of rows so we cannot track/audit changes easily but in case of syncing it seems to be not really needed. Based on discussion in https://stackoverflow.com/questions/23699651/dependency-rule-tried-to-blank-out-primary-key-in-sqlalchemy-when-foreign-key-c Probably Fixes: apache#21407

potiuk · 2022-02-09T14:15:12Z

I looked up where it could come from, and I came up with likely fix to the "blank-out" #21463 - however this is more a "guesswork" based on what I "think" happens. I have not much experience with the Role Model of FAB and relations there, but it seems that the blank-out error might be fixed by updating cascade methods on relationships (and the root causes seems to be that sync_roles is likely removing some related rows in a different order (or not taking into account our relationships).

If the https://stackoverflow.com/questions/23699651/dependency-rule-tried-to-blank-out-primary-key-in-sqlalchemy-when-foreign-key-c is right - this might be solved by declaring cascade dependency including "delete-orphan" (however I just try it out it in #21463 without deeper understanding how it works, hoping that it will work, and only now started reading about it to try to understand it - but I think summoning those who possibly understand more about the FAB/Airlfow model inconsistencies and how they are different might actually provide some better fixes (or maybe just fixes - I am not even sure if what I proposed makes sense yet).

potiuk · 2022-02-09T14:21:41Z

Hmm. Actually I looked at the history, and I think it's nott the SQLAlchemy.

U think the root cause was this: #19294 - @jhtimmins @ashb - it was merged 4 days ago and those relationships have been changed into lazy relationships which seems to break sync, Should we revert this change and rework it?

potiuk · 2022-02-09T14:22:13Z

BTW. My "blind" attempt to fix it, did not work of course.

potiuk · 2022-02-09T14:26:52Z

Attempted to revert it and see it here #21464

@pingzh - maybe you can try to revert it as well and see if it helps in your case?

potiuk · 2022-02-09T14:58:05Z

It looks that it helped to revert it - I had to rebase as static tests were failing but it looks like reverting @19294 via #21464 solves at least the "blank-out" problem.

pingzh · 2022-02-09T23:05:58Z

@ashb @potiuk thanks for looking at this

jhtimmins · 2022-02-10T02:00:54Z

@ashb for some unclear reason, removing the primaryjoin argument fixed this problem 73cf882#diff-ca9167167af7221fcb832084b435603b76c7ccdf32dc36d822585853e7c3c98bL143

potiuk · 2022-02-10T11:21:33Z

I think the problem was indeed caused by the backref reference blanked out.

potiuk · 2022-02-10T11:21:48Z

Closing the issue then :)

potiuk · 2022-02-10T11:22:34Z

Many thanks @jhtimmins ! That was a bummer.

pingzh added area:core kind:bug This is a clearly a bug labels Feb 8, 2022

pingzh mentioned this issue Feb 8, 2022

Fix relationship join bug in FAB/SecurityManager with SQLA 1.4 #21296

Merged

potiuk mentioned this issue Feb 9, 2022

Addd deleting orphans on cascade for permission model #21463

Closed

potiuk mentioned this issue Feb 9, 2022

Revert "Simplify fab has access lookup (#19294)" #21464

Closed

jhtimmins mentioned this issue Feb 10, 2022

Fix bug incorrectly removing action from role, rather than permission. #21483

Merged

potiuk closed this as completed Feb 10, 2022

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

AssertionError: Dependency rule tried to blank-out primary key column 'ab_permission.id' on instance ' #21407

AssertionError: Dependency rule tried to blank-out primary key column 'ab_permission.id' on instance ' #21407

pingzh commented Feb 8, 2022 •

edited

Loading

pingzh commented Feb 8, 2022 •

edited

Loading

ashb commented Feb 8, 2022

ashb commented Feb 8, 2022

ashb commented Feb 8, 2022

pingzh commented Feb 8, 2022

potiuk commented Feb 8, 2022

pingzh commented Feb 8, 2022 •

edited

Loading

pingzh commented Feb 8, 2022

potiuk commented Feb 8, 2022

pingzh commented Feb 9, 2022 •

edited

Loading

ashb commented Feb 9, 2022

potiuk commented Feb 9, 2022

potiuk commented Feb 9, 2022

potiuk commented Feb 9, 2022

potiuk commented Feb 9, 2022

potiuk commented Feb 9, 2022

potiuk commented Feb 9, 2022 •

edited

Loading

potiuk commented Feb 9, 2022

pingzh commented Feb 9, 2022

jhtimmins commented Feb 10, 2022

potiuk commented Feb 10, 2022

potiuk commented Feb 10, 2022

potiuk commented Feb 10, 2022

AssertionError: Dependency rule tried to blank-out primary key column 'ab_permission.id' on instance ' #21407

AssertionError: Dependency rule tried to blank-out primary key column 'ab_permission.id' on instance ' #21407

Comments

pingzh commented Feb 8, 2022 • edited Loading

Apache Airflow version

What happened

What you expected to happen

How to reproduce

Operating System

Versions of Apache Airflow Providers

Deployment

Deployment details

Anything else

Are you willing to submit PR?

Code of Conduct

pingzh commented Feb 8, 2022 • edited Loading

ashb commented Feb 8, 2022

ashb commented Feb 8, 2022

ashb commented Feb 8, 2022

pingzh commented Feb 8, 2022

potiuk commented Feb 8, 2022

pingzh commented Feb 8, 2022 • edited Loading

pingzh commented Feb 8, 2022

potiuk commented Feb 8, 2022

pingzh commented Feb 9, 2022 • edited Loading

ashb commented Feb 9, 2022

potiuk commented Feb 9, 2022

potiuk commented Feb 9, 2022

potiuk commented Feb 9, 2022

potiuk commented Feb 9, 2022

potiuk commented Feb 9, 2022

potiuk commented Feb 9, 2022 • edited Loading

potiuk commented Feb 9, 2022

pingzh commented Feb 9, 2022

jhtimmins commented Feb 10, 2022

potiuk commented Feb 10, 2022

potiuk commented Feb 10, 2022

potiuk commented Feb 10, 2022

pingzh commented Feb 8, 2022 •

edited

Loading

pingzh commented Feb 8, 2022 •

edited

Loading

pingzh commented Feb 8, 2022 •

edited

Loading

pingzh commented Feb 9, 2022 •

edited

Loading

potiuk commented Feb 9, 2022 •

edited

Loading