[
https://issues.apache.org/jira/browse/AIRFLOW-5050?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16895054#comment-16895054
]
ASF GitHub Bot commented on AIRFLOW-5050:
-----------------------------------------
ashb commented on pull request #5679: [AIRFLOW-5050] Correctly delete FAB
permission m2m objects in sync_perms
URL: https://github.com/apache/airflow/pull/5679
Make sure you have checked _all_ steps below.
### Jira
- [x] https://issues.apache.org/jira/browse/AIRFLOW-5050
### Description
- [x] Without this fix we can end up in the situation where trying to delete
throws an error:
> psycopg2.IntegrityError: update or delete on table
> "ab_permission_view" violates foreign key constraint
> "ab_permission_view_role_permission_view_id_fkey" on table
> "ab_permission_view_role"
> DETAIL: Key (id)=(57) is still referenced from table
"ab_permission_view_role".
### Tests
- [x] No unit tests added as it's hard to get the table in to this state. I
_could_ add one if we think it's worth it
### Commits
- [x] My commits all reference Jira issues in their subject lines, and I
have squashed multiple commits if they address the same issue. In addition, my
commits follow the guidelines from "[How to write a good git commit
message](http://chris.beams.io/posts/git-commit/)":
1. Subject is separated from body by a blank line
1. Subject is limited to 50 characters (not including Jira issue reference)
1. Subject does not end with a period
1. Subject uses the imperative mood ("add", not "adding")
1. Body wraps at 72 characters
1. Body explains "what" and "why", not "how"
### Documentation
- [x] In case of new functionality, my PR adds documentation that describes
how to use it.
- All the public functions and the classes in the PR contain docstrings
that explain what it does
- If you implement backwards incompatible changes, please leave a note in
the [Updating.md](https://github.com/apache/airflow/blob/master/UPDATING.md) so
we can assign it to a appropriate release
### Code Quality
- [x] Passes `flake8`
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
[email protected]
> `airflow sync_perm` failes with constraint error after upgrade
> --------------------------------------------------------------
>
> Key: AIRFLOW-5050
> URL: https://issues.apache.org/jira/browse/AIRFLOW-5050
> Project: Apache Airflow
> Issue Type: Bug
> Components: webserver
> Affects Versions: 1.10.4
> Reporter: Ash Berlin-Taylor
> Priority: Major
>
> Running {{airflow sync_perm}} after upgrading from 1.10.3 to 1.10.4rc3 yields
> this error:
> {noformat}
> [2019-07-26 14:05:08,160] {security.py:446} INFO - Start syncing user roles.
> [2019-07-26 14:05:08,740] {security.py:357} INFO - Fetching a set of all
> permission, view_menu from FAB meta-table
> [2019-07-26 14:05:09,495] {security.py:307} INFO - Cleaning faulty perms
> Traceback (most recent call last):
> File "/usr/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line
> 1244, in _execute_context
> cursor, statement, parameters, context
> File "/usr/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line
> 552, in do_execute
> cursor.execute(statement, parameters)
> psycopg2.IntegrityError: update or delete on table "ab_permission_view"
> violates foreign key constraint
> "ab_permission_view_role_permission_view_id_fkey" on table
> "ab_permission_view_role"
> DETAIL: Key (id)=(57) is still referenced from table
> "ab_permission_view_role".
> The above exception was the direct cause of the following exception:
> Traceback (most recent call last):
> File "/usr/bin/airflow", line 32, in <module>
> args.func(args)
> File "/usr/lib/python3.6/site-packages/airflow/utils/cli.py", line 74, in
> wrapper
> return f(*args, **kwargs)
> File "/usr/lib/python3.6/site-packages/airflow/bin/cli.py", line 1472, in
> sync_perm
> appbuilder = cached_appbuilder()
> File "/usr/lib/python3.6/site-packages/airflow/www_rbac/app.py", line 237,
> in cached_appbuilder
> cached_app(config=config, testing=testing)
> File "/usr/lib/python3.6/site-packages/airflow/www_rbac/app.py", line 230,
> in cached_app
> app, _ = create_app(config, session, testing)
> File "/usr/lib/python3.6/site-packages/airflow/www_rbac/app.py", line 190,
> in create_app
> security_manager.sync_roles()
> File
> "/usr/lib/python3.6/site-packages/astronomer/flask_appbuilder/security.py",
> line 278, in sync_roles
> super().sync_roles()
> File "/usr/lib/python3.6/site-packages/airflow/www_rbac/security.py", line
> 460, in sync_roles
> self.clean_perms()
> File "/usr/lib/python3.6/site-packages/airflow/www_rbac/security.py", line
> 316, in clean_perms
> deleted_count = pvms.delete()
> File "/usr/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3696,
> in delete
> delete_op.exec_()
> File "/usr/lib/python3.6/site-packages/sqlalchemy/orm/persistence.py", line
> 1691, in exec_
> self._do_exec()
> File "/usr/lib/python3.6/site-packages/sqlalchemy/orm/persistence.py", line
> 1919, in _do_exec
> self._execute_stmt(delete_stmt)
> File "/usr/lib/python3.6/site-packages/sqlalchemy/orm/persistence.py", line
> 1696, in _execute_stmt
> self.result = self.query._execute_crud(stmt, self.mapper)
> File "/usr/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3363,
> in _execute_crud
> return conn.execute(stmt, self._params)
> File "/usr/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line
> 988, in execute
> return meth(self, multiparams, params)
> File "/usr/lib/python3.6/site-packages/sqlalchemy/sql/elements.py", line
> 287, in _execute_on_connection
> return connection._execute_clauseelement(self, multiparams, params)
> File "/usr/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line
> 1107, in _execute_clauseelement
> distilled_params,
> File "/usr/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line
> 1248, in _execute_context
> e, statement, parameters, cursor, context
> File "/usr/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line
> 1466, in _handle_dbapi_exception
> util.raise_from_cause(sqlalchemy_exception, exc_info)
> File "/usr/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line
> 398, in raise_from_cause
> reraise(type(exception), exception, tb=exc_tb, cause=cause)
> File "/usr/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line
> 152, in reraise
> raise value.with_traceback(tb)
> File "/usr/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line
> 1244, in _execute_context
> cursor, statement, parameters, context
> File "/usr/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line
> 552, in do_execute
> cursor.execute(statement, parameters)
> sqlalchemy.exc.IntegrityError: (psycopg2.IntegrityError) update or delete on
> table "ab_permission_view" violates foreign key constraint
> "ab_permission_view_role_permission_view_id_fkey" on table
> "ab_permission_view_role"
> DETAIL: Key (id)=(57) is still referenced from table
> "ab_permission_view_role".
> [SQL: DELETE FROM ab_permission_view WHERE ab_permission_view.permission_id
> IS NULL OR ab_permission_view.view_menu_id IS NULL]
> (Background on this error at: http://sqlalche.me/e/gkpj)
> {noformat}
--
This message was sent by Atlassian JIRA
(v7.6.14#76016)
