You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@airflow.apache.org by GitBox <gi...@apache.org> on 2022/06/13 16:08:13 UTC
[GitHub] [airflow] zachliu opened a new issue, #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
zachliu opened a new issue, #23512:
URL: https://github.com/apache/airflow/issues/23512
### Apache Airflow version
2.3.0 (latest released)
### What happened
while testing airflow 2.3.0 locally (using postgresql 12.4), the webserver container shows random errors:
```
webserver_1 | + airflow db init
...
webserver_1 | + exec airflow webserver
...
webserver_1 | [2022-05-04 18:58:46,011] {{manager.py:568}} INFO - Added Permission menu access on Permissions to role Admin
postgres_1 | 2022-05-04 18:58:46.013 UTC [41] ERROR: duplicate key value violates unique constraint "ab_permission_view_role_permission_view_id_role_id_key"
postgres_1 | 2022-05-04 18:58:46.013 UTC [41] DETAIL: Key (permission_view_id, role_id)=(204, 1) already exists.
postgres_1 | 2022-05-04 18:58:46.013 UTC [41] STATEMENT: INSERT INTO ab_permission_view_role (id, permission_view_id, role_id) VALUES (nextval('ab_permission_view_role_id_seq'), 204, 1) RETURNING ab_permission_view_role.id
webserver_1 | [2022-05-04 18:58:46,015] {{manager.py:570}} ERROR - Add Permission to Role Error: (psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "ab_permission_view_role_permission_view_id_role_id_key"
webserver_1 | DETAIL: Key (permission_view_id, role_id)=(204, 1) already exists.
webserver_1 |
webserver_1 | [SQL: INSERT INTO ab_permission_view_role (id, permission_view_id, role_id) VALUES (nextval('ab_permission_view_role_id_seq'), %(permission_view_id)s, %(role_id)s) RETURNING ab_permission_view_role.id]
webserver_1 | [parameters: {'permission_view_id': 204, 'role_id': 1}]
```
notes:
1. when the db is first initialized, i have ~40 errors like this (with ~40 different `permission_view_id` but always the same `'role_id': 1`)
2. when it's not the first time initializing db, i always have 1 error like this but it shows different `permission_view_id` each time
3. all these errors don't seem to have any real negative effects, the webserver is still running and airflow is still running and scheduling tasks
4. "occasionally" i do get real exceptions which render the webserver workers all dead:
```
postgres_1 | 2022-05-05 20:03:30.580 UTC [44] ERROR: duplicate key value violates unique constraint "ab_permission_view_role_permission_view_id_role_id_key"
postgres_1 | 2022-05-05 20:03:30.580 UTC [44] DETAIL: Key (permission_view_id, role_id)=(214, 1) already exists.
postgres_1 | 2022-05-05 20:03:30.580 UTC [44] STATEMENT: INSERT INTO ab_permission_view_role (id, permission_view_id, role_id) VALUES (nextval('ab_permission_view_role_id_seq'), 214, 1) RETURNING ab_permission_view_role.id
webserver_1 | [2022-05-05 20:03:30 +0000] [121] [ERROR] Exception in worker process
webserver_1 | Traceback (most recent call last):
webserver_1 | File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1705, in _execute_context
webserver_1 | self.dialect.do_execute(
webserver_1 | File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 716, in do_execute
webserver_1 | cursor.execute(statement, parameters)
webserver_1 | psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "ab_permission_view_role_permission_view_id_role_id_key"
webserver_1 | DETAIL: Key (permission_view_id, role_id)=(214, 1) already exists.
webserver_1 |
webserver_1 |
webserver_1 | The above exception was the direct cause of the following exception:
webserver_1 |
webserver_1 | Traceback (most recent call last):
webserver_1 | File "/usr/local/lib/python3.8/site-packages/gunicorn/arbiter.py", line 589, in spawn_worker
webserver_1 | worker.init_process()
webserver_1 | File "/usr/local/lib/python3.8/site-packages/gunicorn/workers/base.py", line 134, in init_process
webserver_1 | self.load_wsgi()
webserver_1 | File "/usr/local/lib/python3.8/site-packages/gunicorn/workers/base.py", line 146, in load_wsgi
webserver_1 | self.wsgi = self.app.wsgi()
webserver_1 | File "/usr/local/lib/python3.8/site-packages/gunicorn/app/base.py", line 67, in wsgi
webserver_1 | self.callable = self.load()
webserver_1 | File "/usr/local/lib/python3.8/site-packages/gunicorn/app/wsgiapp.py", line 58, in load
webserver_1 | return self.load_wsgiapp()
webserver_1 | File "/usr/local/lib/python3.8/site-packages/gunicorn/app/wsgiapp.py", line 48, in load_wsgiapp
webserver_1 | return util.import_app(self.app_uri)
webserver_1 | File "/usr/local/lib/python3.8/site-packages/gunicorn/util.py", line 412, in import_app
webserver_1 | app = app(*args, **kwargs)
webserver_1 | File "/usr/local/lib/python3.8/site-packages/airflow/www/app.py", line 158, in cached_app
webserver_1 | app = create_app(config=config, testing=testing)
webserver_1 | File "/usr/local/lib/python3.8/site-packages/airflow/www/app.py", line 146, in create_app
webserver_1 | sync_appbuilder_roles(flask_app)
webserver_1 | File "/usr/local/lib/python3.8/site-packages/airflow/www/app.py", line 68, in sync_appbuilder_roles
webserver_1 | flask_app.appbuilder.sm.sync_roles()
webserver_1 | File "/usr/local/lib/python3.8/site-packages/airflow/www/security.py", line 580, in sync_roles
webserver_1 | self.update_admin_permission()
webserver_1 | File "/usr/local/lib/python3.8/site-packages/airflow/www/security.py", line 562, in update_admin_permission
webserver_1 | self.get_session.commit()
webserver_1 | File "<string>", line 2, in commit
webserver_1 | File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 1423, in commit
webserver_1 | self._transaction.commit(_to_root=self.future)
webserver_1 | File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 829, in commit
webserver_1 | self._prepare_impl()
webserver_1 | File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 808, in _prepare_impl
webserver_1 | self.session.flush()
webserver_1 | File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 3255, in flush
webserver_1 | self._flush(objects)
webserver_1 | File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 3395, in _flush
webserver_1 | transaction.rollback(_capture_exception=True)
webserver_1 | File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
webserver_1 | compat.raise_(
webserver_1 | File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
webserver_1 | raise exception
webserver_1 | File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 3355, in _flush
webserver_1 | flush_context.execute()
webserver_1 | File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py", line 453, in execute
webserver_1 | rec.execute(self)
webserver_1 | File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py", line 576, in execute
webserver_1 | self.dependency_processor.process_saves(uow, states)
webserver_1 | File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/dependency.py", line 1182, in process_saves
webserver_1 | self._run_crud(
webserver_1 | File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/dependency.py", line 1245, in _run_crud
webserver_1 | connection.execute(statement, secondary_insert)
webserver_1 | File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1200, in execute
webserver_1 | return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
webserver_1 | File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 313, in _execute_on_connection
webserver_1 | return connection._execute_clauseelement(
webserver_1 | File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1389, in _execute_clauseelement
webserver_1 | ret = self._execute_context(
webserver_1 | File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1748, in _execute_context
webserver_1 | self._handle_dbapi_exception(
webserver_1 | File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1929, in _handle_dbapi_exception
webserver_1 | util.raise_(
webserver_1 | File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
webserver_1 | raise exception
webserver_1 | File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1705, in _execute_context
webserver_1 | self.dialect.do_execute(
webserver_1 | File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 716, in do_execute
webserver_1 | cursor.execute(statement, parameters)
webserver_1 | sqlalchemy.exc.IntegrityError: (psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "ab_permission_view_role_permission_view_id_role_id_key"
webserver_1 | DETAIL: Key (permission_view_id, role_id)=(214, 1) already exists.
webserver_1 |
webserver_1 | [SQL: INSERT INTO ab_permission_view_role (id, permission_view_id, role_id) VALUES (nextval('ab_permission_view_role_id_seq'), %(permission_view_id)s, %(role_id)s) RETURNING ab_permission_view_role.id]
webserver_1 | [parameters: {'permission_view_id': 214, 'role_id': 1}]
webserver_1 | (Background on this error at: http://sqlalche.me/e/14/gkpj)
webserver_1 | [2022-05-05 20:03:30 +0000] [121] [INFO] Worker exiting (pid: 121)
flower_1 | + exec airflow celery flower
scheduler_1 | + exec airflow scheduler
webserver_1 | [2022-05-05 20:03:31 +0000] [118] [INFO] Worker exiting (pid: 118)
webserver_1 | [2022-05-05 20:03:31 +0000] [119] [INFO] Worker exiting (pid: 119)
webserver_1 | [2022-05-05 20:03:31 +0000] [120] [INFO] Worker exiting (pid: 120)
worker_1 | + exec airflow celery worker
```
However such exceptions are rare and pure random, i can't find a way to reproduce them consistently.
### What you think should happen instead
prior to 2.3.0 there were no such errors
### How to reproduce
_No response_
### Operating System
Linux Mint 20.3
### Versions of Apache Airflow Providers
_No response_
### Deployment
Docker-Compose
### Deployment details
_No response_
### Anything else
_No response_
### Are you willing to submit PR?
- [X] Yes I am willing to submit a PR!
### Code of Conduct
- [X] I agree to follow this project's [Code of Conduct](https://github.com/apache/airflow/blob/main/CODE_OF_CONDUCT.md)
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] zachliu commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
zachliu commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1212025794
> Hi all, I´m running currently into the same issue. Because the last comment is 20 days ago. Any new info regarding the issue available?
nope, i monitor this issue on a daily basis :grin:
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] potiuk commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
potiuk commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1192650668
> User @potiuk mentioned is me. We are using docker deployment. After db upgrade, I run `docker-compose down` and recreated all containers ,included postgresql and redis. Somehow it resolved issue for me.
The "Somehow" part is what worries me a bit :)
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] zachliu commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
zachliu commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1270340760
@hterik sadly sometimes we do need that config to be `True`, i've proposed a [workaround](https://github.com/apache/airflow/issues/23512#issuecomment-1140160062), it ain't pretty
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] zachliu commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
zachliu commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1228763090
@cloventt the silver-lining is k8s auto-restarts the failed pod right? :grin:
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] uranusjr closed issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
uranusjr closed issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
URL: https://github.com/apache/airflow/issues/23512
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] zachliu commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
zachliu commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1297166786
@jens-scheffler-bosch also you'd have to patch 2.4.2 manually
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] luebbert42 commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
luebbert42 commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1231198493
I had the problem with 2.3.4 as well (coming from 2.3.3, 2.3.4rc1). I got rid of it by reducing the workers variable to 1, starting the webserver and then going back to 4 workers. Environment: AWS ECS, Postgresql.
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] matteosdocsity commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
matteosdocsity commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1255689807
I honestly have to admit to not have any code-bases clues less than pushing over a unique key on a table is a matter of general wisdom...the problem appears to be clear from some couple of months so far, but now blocking 2.4.0 and maybe new releases is quite huge....is there anyone in the loop that could investigate or propose solutions to this?
btw thanks for allowing airflow being so complete, my message is only a metter on what to do or **change** in the scope of the bug
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] ajbosco commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
ajbosco commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1184703255
How do you get past the error? Every time our webserver pod spins up it fails on this, kills the gunicorn works, and then restarts.
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] jhtimmins commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
jhtimmins commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1154803491
@zachliu ok I checked it with four webservers and postgres, and those errors only showed up the first time I ran it, and even then only once. Are you experiencing it more than that?
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] luebbert42 commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
luebbert42 commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1231884728
@zachliu yes, you are right, I forget to mention that.
Unfortunately we are seeing the error again in the logs.
It seems that something is still trying to remove and add some permissions permanently which does not work when more than one worker is active.
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] potiuk commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
potiuk commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1329825007
Can you please open a new issue for 2.4.3 @zachliu with more details? I have a feeling this is somehow different issue.
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] arroadie commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
arroadie commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1155678232
> @arroadie I semi-agree. Off hand, here are the basic reasons why it works this way.
>
Thanks for taking the time to address my comment! Appreciate the explanation
> 1. In part it's that way bc it's a holdover from using Flask and Flask AppBuilder within Airflow. Modifying that would take a decent amount of work.
Yeah, I thought it would come up to some component acting on its own. While I'm not proposing that this is the right path, I'd say diminishing the amount of "decisions" those components make would definitely improve usability
> 2. Airflow generally tries to decouple pieces as much as possible. Your proposal would create a situation where the webserver permissions being up to date would depend on the scheduler running.
I admit I haven't read/investigate on the reasons airflow needs to perform those checks and changes each time the webserver starts and I do agree that making those dependencies between the webserver and the scheduler seems like a counter intuitive way to act. Guess my suggestion was guided by the idea that only one component of the service as a whole should be taking the responsibility of making database changes (like migrations) and that component should deal with race conditions and so
> 3. If permissions load properly, the current design should guarantee that they're always up to date as soon as the webserver loads, and DB reads/writes should only occur once. Obviously this broke down here.
>
And I really appreciate people coming to the rescue of the community and fixing them! <3
> So while I'm not aware of your proposed approach being explicitly considered, these are some of the design considerations that support the current design. While it's imperfect, when it works, this approach gives good enough performance and enough flexibility.
I don't think it is/would/should... More like a randomly rambling about my thoughts on how tasks (specially internal ones) should be performed.
>
> That said, there's no reason that it _has_ to work this way, and there are downsides to this approach, such as the slower startup time for the webserver. If you encounter significant problems with the design, especially with multiple webservers, or have suggestions about better architectures, I encourage you to reach out to the community via the mailing list.
Agree. I'd say if this should come to become a real issue instead, I'll try to investigate better and reach with a proposal!
Again, thanks a bunch for your contributions and for taking the time to respond to my comment.
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] arroadie commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
arroadie commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1154784112
out of ignorance: why is it the webserver the actor that executes those? It feels counter intuitive to have an actor that should be stateless to enforce state. If it's a matter that these roles need to be written over and over each time the webserver restarts, why not pass that as a task to the execution context (celery or whatever is there), so we have guarantees that the task will only be executed once (even if you have several webservers, schedulers, or whatever)?
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] jens-scheffler-bosch commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
jens-scheffler-bosch commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1297099635
Thanks for the good news. I tried to test in our Azure deployment (using the official Helm chart) and Airflow 2.4.2 and it seems in our deployment it is still showing the same constraint errors. I am a bit confused because the milestoone was now modfied to be Airflow 2.4.3 but above the success is reported on 2.4.2... which release it it assumed to be in?
Our logs still look like attached (don't want to spoil the message thread with hundrets of lines...,):
```
(...)
[2022-10-31 12:59:33 +0000] [41] [INFO] Worker exiting (pid: 41)
[2022-10-31 12:59:33 +0000] [42] [ERROR] Exception in worker process
Traceback (most recent call last):
File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1802, in _execute_context
self.dialect.do_execute(
File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 719, in do_execute
cursor.execute(statement, parameters)
psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "ab_permission_view_role_permission_view_id_role_id_key"
DETAIL: Key (permission_view_id, role_id)=(3097, 1) already exists.
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/home/airflow/.local/lib/python3.8/site-packages/gunicorn/arbiter.py", line 589, in spawn_worker
worker.init_process()
File "/home/airflow/.local/lib/python3.8/site-packages/gunicorn/workers/base.py", line 134, in init_process
self.load_wsgi()
File "/home/airflow/.local/lib/python3.8/site-packages/gunicorn/workers/base.py", line 146, in load_wsgi
self.wsgi = self.app.wsgi()
File "/home/airflow/.local/lib/python3.8/site-packages/gunicorn/app/base.py", line 67, in wsgi
self.callable = self.load()
File "/home/airflow/.local/lib/python3.8/site-packages/gunicorn/app/wsgiapp.py", line 58, in load
return self.load_wsgiapp()
File "/home/airflow/.local/lib/python3.8/site-packages/gunicorn/app/wsgiapp.py", line 48, in load_wsgiapp
return util.import_app(self.app_uri)
File "/home/airflow/.local/lib/python3.8/site-packages/gunicorn/util.py", line 412, in import_app
app = app(*args, **kwargs)
File "/home/airflow/.local/lib/python3.8/site-packages/airflow/www/app.py", line 167, in cached_app
app = create_app(config=config, testing=testing)
File "/home/airflow/.local/lib/python3.8/site-packages/airflow/www/app.py", line 154, in create_app
sync_appbuilder_roles(flask_app)
File "/home/airflow/.local/lib/python3.8/site-packages/airflow/www/app.py", line 73, in sync_appbuilder_roles
flask_app.appbuilder.sm.sync_roles()
File "/home/airflow/.local/lib/python3.8/site-packages/airflow/www/security.py", line 600, in sync_roles
self.update_admin_permission()
File "/home/airflow/.local/lib/python3.8/site-packages/airflow/www/security.py", line 582, in update_admin_permission
self.get_session.commit()
File "<string>", line 2, in commit
File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 1428, in commit
self._transaction.commit(_to_root=self.future)
File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 829, in commit
self._prepare_impl()
File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 808, in _prepare_impl
self.session.flush()
File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 3345, in flush
self._flush(objects)
File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 3485, in _flush
transaction.rollback(_capture_exception=True)
File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
compat.raise_(
File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
raise exception
File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 3445, in _flush
flush_context.execute()
File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py", line 456, in execute
rec.execute(self)
File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py", line 579, in execute
self.dependency_processor.process_saves(uow, states)
File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/orm/dependency.py", line 1182, in process_saves
self._run_crud(
File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/orm/dependency.py", line 1245, in _run_crud
connection.execute(statement, secondary_insert)
File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1289, in execute
return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 325, in _execute_on_connection
return connection._execute_clauseelement(
File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1481, in _execute_clauseelement
ret = self._execute_context(
File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1845, in _execute_context
self._handle_dbapi_exception(
File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2026, in _handle_dbapi_exception
util.raise_(
File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
raise exception
File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1802, in _execute_context
self.dialect.do_execute(
File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 719, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.IntegrityError: (psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "ab_permission_view_role_permission_view_id_role_id_key"
DETAIL: Key (permission_view_id, role_id)=(3097, 1) already exists.
[SQL: INSERT INTO ab_permission_view_role (permission_view_id, role_id) VALUES (%(permission_view_id)s, %(role_id)s) RETURNING ab_permission_view_role.id]
[parameters: {'permission_view_id': 3097, 'role_id': 1}]
(Background on this error at: https://sqlalche.me/e/14/gkpj)
[2022-10-31 12:59:33 +0000] [40] [ERROR] Exception in worker process
Traceback (most recent call last):
File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1802, in _execute_context
self.dialect.do_execute(
File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 719, in do_execute
cursor.execute(statement, parameters)
psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "ab_permission_view_role_permission_view_id_role_id_key"
DETAIL: Key (permission_view_id, role_id)=(3097, 1) already exists.
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/home/airflow/.local/lib/python3.8/site-packages/gunicorn/arbiter.py", line 589, in spawn_worker
worker.init_process()
File "/home/airflow/.local/lib/python3.8/site-packages/gunicorn/workers/base.py", line 134, in init_process
self.load_wsgi()
File "/home/airflow/.local/lib/python3.8/site-packages/gunicorn/workers/base.py", line 146, in load_wsgi
self.wsgi = self.app.wsgi()
File "/home/airflow/.local/lib/python3.8/site-packages/gunicorn/app/base.py", line 67, in wsgi
self.callable = self.load()
File "/home/airflow/.local/lib/python3.8/site-packages/gunicorn/app/wsgiapp.py", line 58, in load
return self.load_wsgiapp()
File "/home/airflow/.local/lib/python3.8/site-packages/gunicorn/app/wsgiapp.py", line 48, in load_wsgiapp
return util.import_app(self.app_uri)
File "/home/airflow/.local/lib/python3.8/site-packages/gunicorn/util.py", line 412, in import_app
app = app(*args, **kwargs)
File "/home/airflow/.local/lib/python3.8/site-packages/airflow/www/app.py", line 167, in cached_app
app = create_app(config=config, testing=testing)
File "/home/airflow/.local/lib/python3.8/site-packages/airflow/www/app.py", line 154, in create_app
sync_appbuilder_roles(flask_app)
File "/home/airflow/.local/lib/python3.8/site-packages/airflow/www/app.py", line 73, in sync_appbuilder_roles
flask_app.appbuilder.sm.sync_roles()
File "/home/airflow/.local/lib/python3.8/site-packages/airflow/www/security.py", line 600, in sync_roles
self.update_admin_permission()
File "/home/airflow/.local/lib/python3.8/site-packages/airflow/www/security.py", line 582, in update_admin_permission
self.get_session.commit()
File "<string>", line 2, in commit
File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 1428, in commit
self._transaction.commit(_to_root=self.future)
File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 829, in commit
self._prepare_impl()
File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 808, in _prepare_impl
self.session.flush()
File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 3345, in flush
self._flush(objects)
File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 3485, in _flush
transaction.rollback(_capture_exception=True)
File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
compat.raise_(
File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
raise exception
File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 3445, in _flush
flush_context.execute()
File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py", line 456, in execute
rec.execute(self)
File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py", line 579, in execute
self.dependency_processor.process_saves(uow, states)
File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/orm/dependency.py", line 1182, in process_saves
self._run_crud(
File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/orm/dependency.py", line 1245, in _run_crud
connection.execute(statement, secondary_insert)
File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1289, in execute
return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 325, in _execute_on_connection
return connection._execute_clauseelement(
File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1481, in _execute_clauseelement
ret = self._execute_context(
File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1845, in _execute_context
self._handle_dbapi_exception(
File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2026, in _handle_dbapi_exception
util.raise_(
File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
raise exception
File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1802, in _execute_context
self.dialect.do_execute(
File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 719, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.IntegrityError: (psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "ab_permission_view_role_permission_view_id_role_id_key"
DETAIL: Key (permission_view_id, role_id)=(3097, 1) already exists.
[SQL: INSERT INTO ab_permission_view_role (permission_view_id, role_id) VALUES (%(permission_view_id)s, %(role_id)s) RETURNING ab_permission_view_role.id]
[parameters: {'permission_view_id': 3097, 'role_id': 1}]
(Background on this error at: https://sqlalche.me/e/14/gkpj)
[2022-10-31 12:59:33 +0000] [42] [INFO] Worker exiting (pid: 42)
[2022-10-31 12:59:33 +0000] [40] [INFO] Worker exiting (pid: 40)
10.10.16.4 - - [31/Oct/2022:12:59:33 +0000] "GET /dev-**readacted**/airflow/health HTTP/1.1" 200 141 "-" "-"
10.10.16.7 - - [31/Oct/2022:12:59:33 +0000] "GET /dev-**readacted**/airflow/health HTTP/1.1" 200 141 "-" "-"
10.10.0.241 - - [31/Oct/2022:12:59:34 +0000] "GET /dev-**readacted**/airflow/health HTTP/1.1" 200 141 "-" "kube-probe/1.23"
10.10.0.241 - - [31/Oct/2022:12:59:34 +0000] "GET /dev-**readacted**/airflow/health HTTP/1.1" 200 141 "-" "kube-probe/1.23"
10.10.0.241 - - [31/Oct/2022:12:59:34 +0000] "GET /dev-**readacted**/airflow/health HTTP/1.1" 200 141 "-" "kube-probe/1.23"
10.10.0.241 - - [31/Oct/2022:12:59:34 +0000] "GET /dev-**readacted**/airflow/health HTTP/1.1" 200 141 "-" "kube-probe/1.23"
10.10.16.4 - - [31/Oct/2022:12:59:35 +0000] "GET /dev-**readacted**/airflow/health HTTP/1.1" 200 141 "-" "-"
[2022-10-31 12:59:35 +0000] [43] [INFO] Worker exiting (pid: 43)
Traceback (most recent call last):
File "/home/airflow/.local/lib/python3.8/site-packages/gunicorn/arbiter.py", line 209, in run
self.sleep()
File "/home/airflow/.local/lib/python3.8/site-packages/gunicorn/arbiter.py", line 357, in sleep
ready = select.select([self.PIPE[0]], [], [], 1.0)
File "/home/airflow/.local/lib/python3.8/site-packages/gunicorn/arbiter.py", line 242, in handle_chld
self.reap_workers()
File "/home/airflow/.local/lib/python3.8/site-packages/gunicorn/arbiter.py", line 525, in reap_workers
raise HaltServer(reason, self.WORKER_BOOT_ERROR)
gunicorn.errors.HaltServer: <HaltServer 'Worker failed to boot.' 3>
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "/usr/local/lib/python3.8/runpy.py", line 194, in _run_module_as_main
return _run_code(code, main_globals, None,
File "/usr/local/lib/python3.8/runpy.py", line 87, in _run_code
exec(code, run_globals)
File "/home/airflow/.local/lib/python3.8/site-packages/gunicorn/__main__.py", line 7, in <module>
run()
File "/home/airflow/.local/lib/python3.8/site-packages/gunicorn/app/wsgiapp.py", line 67, in run
WSGIApplication("%(prog)s [OPTIONS] [APP_MODULE]").run()
File "/home/airflow/.local/lib/python3.8/site-packages/gunicorn/app/base.py", line 231, in run
super().run()
File "/home/airflow/.local/lib/python3.8/site-packages/gunicorn/app/base.py", line 72, in run
Arbiter(self).run()
File "/home/airflow/.local/lib/python3.8/site-packages/gunicorn/arbiter.py", line 229, in run
self.halt(reason=inst.reason, exit_status=inst.exit_status)
File "/home/airflow/.local/lib/python3.8/site-packages/gunicorn/arbiter.py", line 342, in halt
self.stop()
File "/home/airflow/.local/lib/python3.8/site-packages/gunicorn/arbiter.py", line 393, in stop
time.sleep(0.1)
File "/home/airflow/.local/lib/python3.8/site-packages/gunicorn/arbiter.py", line 242, in handle_chld
self.reap_workers()
File "/home/airflow/.local/lib/python3.8/site-packages/gunicorn/arbiter.py", line 525, in reap_workers
raise HaltServer(reason, self.WORKER_BOOT_ERROR)
gunicorn.errors.HaltServer: <HaltServer 'Worker failed to boot.' 3>
____________ _____________
____ |__( )_________ __/__ /________ __
____ /| |_ /__ ___/_ /_ __ /_ __ \_ | /| / /
___ ___ | / _ / _ __/ _ / / /_/ /_ |/ |/ /
_/_/ |_/_/ /_/ /_/ /_/ \____/____/|__/
Running the Gunicorn Server with:
Workers: 4 sync
Host: 0.0.0.0:8080
Timeout: 120
Logfiles: - -
Access Logformat:
=================================================================
[2022-10-31T12:59:37.555+0000] {webserver_command.py:252} ERROR - [0 / 0] Some workers seem to have died and gunicorn did not restart them as expected
[2022-10-31T12:59:47.567+0000] {webserver_command.py:264} INFO - [0 / 0] Spawning 1 workers
[2022-10-31T13:01:14.490+0000] {webserver_command.py:420} INFO - Received signal: 15. Closing gunicorn.
```
[webserver-logs.txt](https://github.com/apache/airflow/files/9901473/webserver-logs.txt)
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] dylanturn commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
dylanturn commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1237597713
Not sure if this helps speed up the troubleshooting process but I'm seeing the same `duplicate key value violates unique constraint` error when running airflow in standalone mode with one gunicorn worker.
interestingly, I only started getting this error message when I switched `AUTH_TYPE` from `AUTH_DB` to `AUTH_OAUTH`
<img width="1679" alt="image" src="https://user-images.githubusercontent.com/5224788/188535381-9f9c0ab8-5f9b-4554-89b4-6b9b723404ea.png">
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] stephenonethree commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
stephenonethree commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1276644397
Saw this after upgrading from 2.2.5 to 2.3.2. Dropping `[webserver].workers` to 1 seems to work as a temporary workaround.
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] potiuk commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
potiuk commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1329853497
Not sure why - but yes. I ping the wrong person :)
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] gitruhul commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
gitruhul commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1125051096
Which library I should downgrade to get my upgrade going. Any suggestions, please?
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] zachliu commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
zachliu commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1150072129
still seeing
```
webserver_1 | [2022-06-08 15:28:57,507] {{manager.py:585}} INFO - Removed Permission can create on Users to role Admin
```
and
```
webserver_1 | [2022-06-08 15:28:57,536] {{manager.py:543}} INFO - Removed Permission View: can_create on Users
webserver_1 | [2022-06-08 15:28:59,994] {{manager.py:508}} INFO - Created Permission View: can create on Users
```
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] betocolon23 commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
betocolon23 commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1184836577
> How do you get past the error? Every time our webserver pod spins up it fails on this, kills the gunicorn works, and then restarts.
Hi, I was having this issue. I'm using airflow community helm chart:
`## configs for the web Pods' readiness probe
##
readinessProbe:
enabled: true
initialDelaySeconds: 180
periodSeconds: 10
timeoutSeconds: 30
failureThreshold: 6
## configs for the web Pods' liveness probe
##
livenessProbe:
enabled: true
initialDelaySeconds: 180
periodSeconds: 10
timeoutSeconds: 30
failureThreshold: 6`
This is on the web section of the Helm chart.
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] AutomationDev85 commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
AutomationDev85 commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1211872905
Hi all, I´m running currently into the same issue. Because the last comment is 20 days ago. Any new info regarding the issue available?
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] zachliu commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
zachliu commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1228889443
both of my webserver pods in the screenshot have 4 gunicorn works, they all work fine - after the 1 restart of course
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] ephraimbuddy commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
ephraimbuddy commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1119843495
Oh, I assigned it to you because you checked that you are willing to submit a PR. I'll unassign you now :)
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] matteosdocsity commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
matteosdocsity commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1271540944
so `update_fab_perms = False` and AIRFLOW__WEBSERVER__UPDATE_FAB_PERMS does not change anything also in relation with AUTH_OAUTH where is required to have fab permissions to be updated 😞..any other clue?
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] ashb commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
ashb commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1270478021
I've seen this from time to time and this is a separate issue to the null constraints violation.
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] potiuk commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
potiuk commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1270465617
I believe This is going to be fixed in 2.4.2 https://github.com/apache/airflow/pull/26885
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] zachliu commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
zachliu commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1121491418
```
webserver_1 | [2022-05-07 02:50:25 +0000] [107] [INFO] Using worker: sync
webserver_1 | [2022-05-07 02:50:25 +0000] [117] [INFO] Booting worker with pid: 117
webserver_1 | [2022-05-07 02:50:25 +0000] [118] [INFO] Booting worker with pid: 118
webserver_1 | [2022-05-07 02:50:25 +0000] [119] [INFO] Booting worker with pid: 119
webserver_1 | [2022-05-07 02:50:25 +0000] [120] [INFO] Booting worker with pid: 120
webserver_1 | [2022-05-07 02:50:25,341] {{logging_config.py:46}} INFO - Successfully imported user-defined logging config from log_config.LOGGING_CONFIG
webserver_1 | [2022-05-07 02:50:25,400] {{logging_config.py:46}} INFO - Successfully imported user-defined logging config from log_config.LOGGING_CONFIG
webserver_1 | [2022-05-07 02:50:25,532] {{logging_config.py:46}} INFO - Successfully imported user-defined logging config from log_config.LOGGING_CONFIG
webserver_1 | [2022-05-07 02:50:25,600] {{logging_config.py:46}} INFO - Successfully imported user-defined logging config from log_config.LOGGING_CONFIG
webserver_1 | [2022-05-07 02:50:25,692] {{manager.py:585}} INFO - Removed Permission can create on Users to role Admin
```
i see those repeated log messages right after booting up 4 worker sub-processes, are these workers trying to update the database at the same time using the same `DELETE` or `INSERT` statements?
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] shawnngtq commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
shawnngtq commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1126843822
I don't think downgrade library will work? Seems like we have to wait for v2.3.1.
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] zachliu commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
zachliu commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1156682542
@jhtimmins thank you for looking into this :pray: to make sure we are on the same page:
* i don't have 4 webservers, i have 1 webserver with 4 gunicorn workers. the # of workers is controlled by
```
# Number of workers to run the Gunicorn web server
workers = 4
```
in my `airflow.cfg`
* the `ERROR: duplicate key value violates unique constraint` message showed up `24` times when I first ran it
* there are no errors after the initial run but these logs keep showing up every time
```
webserver_1 | [2022-06-15 16:02:47,740] {{manager.py:585}} INFO - Removed Permission can create on Users to role Admin
webserver_1 | [2022-06-15 16:02:47,763] {{manager.py:543}} INFO - Removed Permission View: can_create on Users
webserver_1 | [2022-06-15 16:02:50,223] {{manager.py:508}} INFO - Created Permission View: can create on Users
```
the last entry of the `ab_permission_view` table and the `ab_permission_view_role` table are being removed and added back every time. so their IDs keep increasing (before the fix it was the last 2 entries, now it's just the last 1 entry)
* when i set `workers = 1` in my `airflow.cfg`, i got no errors in the first run but of course i still get these three log messages every time
so i guess if we could fix the mechanism that generates the above log messages, this matter will be solved 100%
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] zachliu commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
zachliu commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1150428558
well, to be fair, https://github.com/apache/airflow/pull/24065 did fix half of this issue. the repeating/conflicting actions around `menu access` are gone now, but the repeating/conflicting actions around `can_create` are still here
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] zachliu commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
zachliu commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1150469780
@arroadie only a layman solution here: https://github.com/apache/airflow/issues/23512#issuecomment-1140160062. it's just calling the `create_app` before spinning up all the workers and setting a env flag st the workers don't touch the fab permissions
i did dig into the source code but still only have limited understanding on this. afaik, permission related setups should be done at the database init stage `airflow db init`, and yes most of them did occur during that stage:
```
webserver_1 | INFO [alembic.runtime.migration] Running upgrade 45ba3f1493b9 -> 849da589634d, Prefix DAG permissions.
webserver_1 | INFO [alembic.runtime.migration] Running upgrade 849da589634d -> 2c6edca13270, Resource based permissions.
webserver_1 | [2022-06-08 21:57:56,743] {{manager.py:244}} INFO - Inserted Role: Admin
webserver_1 | [2022-06-08 21:57:56,748] {{manager.py:244}} INFO - Inserted Role: Public
webserver_1 | [2022-06-08 21:57:56,750] {{manager.py:805}} WARNING - No user yet created, use flask fab command to do it.
webserver_1 | [2022-06-08 21:57:57,705] {{providers_manager.py:215}} INFO - Optional provider feature disabled when importing 'airflow.providers.google.leveldb.hooks.leveldb.LevelDBHook' from 'apache-airflow-providers-google' package
webserver_1 | [2022-06-08 21:57:57,779] {{providers_manager.py:215}} INFO - Optional provider feature disabled when importing 'airflow.providers.google.leveldb.hooks.leveldb.LevelDBHook' from 'apache-airflow-providers-google' package
webserver_1 | [2022-06-08 21:57:58,715] {{manager.py:508}} INFO - Created Permission View: can delete on Connections
webserver_1 | [2022-06-08 21:57:58,722] {{manager.py:508}} INFO - Created Permission View: can read on Connections
webserver_1 | [2022-06-08 21:57:58,732] {{manager.py:508}} INFO - Created Permission View: can edit on Connections
...
webserver_1 | [2022-06-08 21:58:00,121] {{manager.py:508}} INFO - Created Permission View: can edit on Passwords
webserver_1 | [2022-06-08 21:58:00,124] {{manager.py:568}} INFO - Added Permission can edit on Passwords to role Admin
webserver_1 | [2022-06-08 21:58:00,126] {{manager.py:568}} INFO - Added Permission can read on Roles to role Admin
webserver_1 | [2022-06-08 21:58:00,128] {{manager.py:568}} INFO - Added Permission can edit on Roles to role Admin
webserver_1 | INFO [alembic.runtime.migration] Running upgrade 2c6edca13270 -> 61ec73d9401f, Add description field to ``connection`` table
webserver_1 | INFO [alembic.runtime.migration] Running upgrade 61ec73d9401f -> 64a7d6477aae, Fix description field in ``connection`` to be ``text``
```
however, as i commented here https://github.com/apache/airflow/issues/23512#issuecomment-1140160062, certain methods are able to do it **after** the gunicorn workers are spun up. so the workers are doing the same actions on the same database causing a ton of conflicts.
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] zachliu commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
zachliu commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1184684780
> This issue is still happening in 2.3.3. Is the expectation that you run a single gunicorn worker?
i guess that would have negative impact on the performance. this is a "non-blocking" error only occurs during startup anyway, so i sort of just go with it right now
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] zachliu commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
zachliu commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1192726810
> > User @potiuk mentioned is me. We are using docker deployment. After db upgrade, I run `docker-compose down` and recreated all containers ,included postgresql and redis. Somehow it resolved issue for me.
>
> The "Somehow" part is what worries me a bit :)
exactly, after seeing the `worker existing (pid: xxx)` error message (as shown in the screenshot here https://github.com/apache/airflow/issues/23512#issuecomment-1156682542), the webserver is usually stalled, but "somehow" it recovered. maybe the pod was restarted by k8s
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] zachliu commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
zachliu commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1140160062
both
https://github.com/apache/airflow/blob/ee100a592e07761f3b32294f3ad82c4a6c7cf74d/airflow/www/app.py#L136
and
https://github.com/apache/airflow/blob/ee100a592e07761f3b32294f3ad82c4a6c7cf74d/airflow/www/app.py#L146
are doing redundant reads/writes to the database once this line
https://github.com/apache/airflow/blob/ee100a592e07761f3b32294f3ad82c4a6c7cf74d/airflow/cli/commands/webserver_command.py#L475
spins up 4 (by default) gunicorn workers. so i guess if i do this
```python
os.environ['SKIP_DAGS_PARSING'] = 'True'
app = create_app(None)
os.environ.pop('SKIP_DAGS_PARSING')
# set webserver.update_fab_perms to "False" here
# would cause the workers not to do the clashing reads/writes
with subprocess.Popen(run_args, close_fds=True) as gunicorn_master_proc:
monitor_gunicorn(gunicorn_master_proc.pid)
```
i know it's weird to alter it on the fly. an alternative is to set an env var such as
```python
os.environ['UPDATE_FAB_PERMS_DONE'] = 'True'
```
and then check it along side with `webserver.update_fab_perms` in https://github.com/apache/airflow/blob/ee100a592e07761f3b32294f3ad82c4a6c7cf74d/airflow/www/app.py#L67 and https://github.com/apache/airflow/blob/ee100a592e07761f3b32294f3ad82c4a6c7cf74d/airflow/www/extensions/init_appbuilder.py#L643
i tried the env var solution, it worked. but it's janky. it requires an extra env var :thinking:
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] zachliu commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
zachliu commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1119717151
@ephraimbuddy sorry man, i actually have no idea what could cause the issue. i have only wild guesses :sweat_smile:
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] jens-scheffler-bosch commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
jens-scheffler-bosch commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1297537353
Thanks for the clarification, looking forward for 2.4.3 :-D
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] potiuk commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
potiuk commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1297336822
Great point @mdering !
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] iercan commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
iercan commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1192626349
User @potiuk mentioned is me. We are using docker deployment. After db upgrade, I run `docker-compose down` and recreated all containers ,included postgresql and redis. Somehow it resolved issue for me.
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] ahipp13 commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
ahipp13 commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1329812257
We are still having this issue in 2.4.3. We set [webserver].workers to 1 for a workaround. Here were the logs we were seeing:
airflow-web 127.0.0.1 - - [23/Nov/2022:15:35:59 +0000] "GET /login/ HTTP/1.1" 200 16330 "https://login.microsoftonline.com/" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/106.0.0.0 Safari/537.36 Edg/106.0.1370.52"
airflow-web 127.0.0.1 - - [23/Nov/2022:15:35:59 +0000] "GET /static/appbuilder/css/font-awesome.min.css HTTP/1.1" 200 0 "https://dev-edw-airflow.hcck8s-ctc-np1.optum.com/login/" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/106.0.0.0 Safari/537.36 Edg/106.0.1370.52"
airflow-web [2022-11-23 15:35:59,423] {app.py:1741} ERROR - Exception on /static/appbuilder/css/bootstrap.min.css [GET]
airflow-web Traceback (most recent call last):
airflow-web File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1802, in _execute_context
airflow-web self.dialect.do_execute(
airflow-web File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 719, in do_execute
airflow-web cursor.execute(statement, parameters)
airflow-web psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "session_session_id_key"
airflow-web DETAIL: Key (session_id)=(7582aee0-4289-4152-99d2-918c98ff64d6) already exists.
airflow-web
airflow-web return super().save_session(*args, **kwargs)
airflow-web File "/home/airflow/.local/lib/python3.8/site-packages/flask_session/sessions.py", line 578, in save_session
airflow-web self.db.session.commit()
airflow-web File "", line 2, in commit
airflow-web File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 1428, in commit
airflow-web self._transaction.commit(_to_root=self.future)
airflow-web File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 829, in commit
airflow-web self._prepare_impl()
airflow-web File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 808, in _prepare_impl
airflow-web self.session.flush()
airflow-web File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 3345, in flush
airflow-web self._flush(objects)
airflow-web File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 3485, in flush
airflow-web transaction.rollback(capture_exception=True)
airflow-web File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 70, in exit
airflow-web compat.raise(
airflow-web File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 207, in raise
airflow-web raise exception
airflow-web File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 3445, in _flush
airflow-web flush_context.execute()
airflow-web File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py", line 456, in execute
airflow-web rec.execute(self)
airflow-web File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py", line 630, in execute
airflow-web util.preloaded.orm_persistence.save_obj(
airflow-web File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/orm/persistence.py", line 244, in save_obj
airflow-web _emit_insert_statements(
airflow-web File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/orm/persistence.py", line 1221, in _emit_insert_statements
airflow-web result = connection._execute_20(
airflow-web File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1614, in _execute_20
airflow-web return meth(self, args_10style, kwargs_10style, execution_options)
airflow-web File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 325, in _execute_on_connection
airflow-web return connection._execute_clauseelement(
airflow-web File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1481, in _execute_clauseelement
airflow-web ret = self._execute_context(
airflow-web File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1845, in _execute_context
airflow-web self.handle_dbapi_exception(
airflow-web File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2026, in handle_dbapi_exception
airflow-web util.raise(
airflow-web File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 207, in raise
airflow-web raise exception
airflow-web File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1802, in _execute_context
airflow-web self.dialect.do_execute(
airflow-web File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 719, in do_execute
airflow-web cursor.execute(statement, parameters)
airflow-web sqlalchemy.exc.IntegrityError: (psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "session_session_id_key"
airflow-web DETAIL: Key (session_id)=(7582aee0-4289-4152-99d2-918c98ff64d6) already exists.
airflow-web
airflow-web [SQL: INSERT INTO session (session_id, data, expiry) VALUES (%(session_id)s, %(data)s, %(expiry)s) RETURNING session.id]
airflow-web [parameters: {'session_id': '7582aee0-4289-4152-99d2-918c98ff64d6', 'data': <psycopg2.extensions.Binary object at 0x7fbd53ad71b0>, 'expiry': datetime.datetime(2022, 11, 23, 16, 5, 59, 467562, tzinfo=datetime.timezone.utc)}]
airflow-web (Background on this error at: https://sqlalche.me/e/14/gkpj)
airflow-web
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] zachliu commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
zachliu commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1329847946
@potiuk you probably `@` the wrong guy :grin:
@ahipp13 Can you please open a new issue for 2.4.3 with more details? I also have a feeling this is a different issue. this issue is centered around the `ab_permission_view_role_permission_view_id_role_id_key` which is role management & permission related, yours is the `session_session_id_key`, my guess is somehow you have a duplicate session
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] zachliu commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
zachliu commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1120127422
further investigation shows that we are removing the same "permissions" and "permission views" repeatedly and trying to add them back repeatedly:
```
webserver_1 | [2022-05-07 02:50:25 +0000] [107] [INFO] Using worker: sync
webserver_1 | [2022-05-07 02:50:25 +0000] [117] [INFO] Booting worker with pid: 117
webserver_1 | [2022-05-07 02:50:25 +0000] [118] [INFO] Booting worker with pid: 118
webserver_1 | [2022-05-07 02:50:25 +0000] [119] [INFO] Booting worker with pid: 119
webserver_1 | [2022-05-07 02:50:25 +0000] [120] [INFO] Booting worker with pid: 120
webserver_1 | [2022-05-07 02:50:25,341] {{logging_config.py:46}} INFO - Successfully imported user-defined logging config from log_config.LOGGING_CONFIG
webserver_1 | [2022-05-07 02:50:25,400] {{logging_config.py:46}} INFO - Successfully imported user-defined logging config from log_config.LOGGING_CONFIG
webserver_1 | [2022-05-07 02:50:25,532] {{logging_config.py:46}} INFO - Successfully imported user-defined logging config from log_config.LOGGING_CONFIG
webserver_1 | [2022-05-07 02:50:25,600] {{logging_config.py:46}} INFO - Successfully imported user-defined logging config from log_config.LOGGING_CONFIG
webserver_1 | [2022-05-07 02:50:25,692] {{manager.py:585}} INFO - Removed Permission can create on Users to role Admin
webserver_1 | [2022-05-07 02:50:25,705] {{manager.py:585}} INFO - Removed Permission can create on Users to role Admin
webserver_1 | [2022-05-07 02:50:25,724] {{manager.py:543}} INFO - Removed Permission View: can_create on Users
webserver_1 | /usr/local/lib/python3.8/site-packages/sqlalchemy/orm/persistence.py:1461 SAWarning: DELETE statement on table 'ab_permission_view' expected to delete 1 row(s); 0 were matched. Please set confirm_deleted_rows=False within the mapper configuration to prevent this warning.
webserver_1 | [2022-05-07 02:50:25,736] {{manager.py:543}} INFO - Removed Permission View: can_create on Users
webserver_1 | [2022-05-07 02:50:25,912] {{manager.py:585}} INFO - Removed Permission menu access on Permissions to role Admin
webserver_1 | [2022-05-07 02:50:25,912] {{manager.py:585}} INFO - Removed Permission menu access on Permissions to role Admin
webserver_1 | [2022-05-07 02:50:25,923] {{manager.py:543}} INFO - Removed Permission View: menu_access on Permissions
webserver_1 | /usr/local/lib/python3.8/site-packages/sqlalchemy/orm/persistence.py:1461 SAWarning: DELETE statement on table 'ab_permission_view' expected to delete 1 row(s); 0 were matched. Please set confirm_deleted_rows=False within the mapper configuration to prevent this warning.
webserver_1 | [2022-05-07 02:50:25,935] {{manager.py:543}} INFO - Removed Permission View: menu_access on Permissions
webserver_1 | [2022-05-07 02:50:26,016] {{manager.py:508}} INFO - Created Permission View: menu access on Permissions
webserver_1 | [2022-05-07 02:50:26,027] {{manager.py:568}} INFO - Added Permission menu access on Permissions to role Admin
webserver_1 | [2022-05-07 02:50:26,067] {{manager.py:585}} INFO - Removed Permission menu access on Permissions to role Admin
webserver_1 | [2022-05-07 02:50:26,091] {{manager.py:543}} INFO - Removed Permission View: menu_access on Permissions
postgres_1 | 2022-05-07 02:50:26.093 UTC [43] ERROR: insert or update on table "ab_permission_view_role" violates foreign key constraint "ab_permission_view_role_permission_view_id_fkey"
postgres_1 | 2022-05-07 02:50:26.093 UTC [43] DETAIL: Key (permission_view_id)=(220) is not present in table "ab_permission_view".
postgres_1 | 2022-05-07 02:50:26.093 UTC [43] STATEMENT: INSERT INTO ab_permission_view_role (id, permission_view_id, role_id) VALUES (nextval('ab_permission_view_role_id_seq'), 220, 1) RETURNING ab_permission_view_role.id
webserver_1 | [2022-05-07 02:50:26,096] {{manager.py:570}} ERROR - Add Permission to Role Error: (psycopg2.errors.ForeignKeyViolation) insert or update on table "ab_permission_view_role" violates foreign key constraint "ab_permission_view_role_permission_view_id_fkey"
webserver_1 | DETAIL: Key (permission_view_id)=(220) is not present in table "ab_permission_view".
webserver_1 |
webserver_1 | [SQL: INSERT INTO ab_permission_view_role (id, permission_view_id, role_id) VALUES (nextval('ab_permission_view_role_id_seq'), %(permission_view_id)s, %(role_id)s) RETURNING ab_permission_view_role.id]
webserver_1 | [parameters: {'permission_view_id': 220, 'role_id': 1}]
webserver_1 | (Background on this error at: http://sqlalche.me/e/14/gkpj)
```
notice the 2 `SAWarning: ...` when we tried to `Removed Permission View: can_create on Users`. Apparently the same `DELETE` was executed twice.
I don't understand why we `Removed Permission menu access on Permissions from role Admin` and then immediately `Added Permission menu access on Permissions to role Admin` (the same with `can create on Users` permission) :thinking:
The database screenshots which shows the permissions and permission views are being deleted and added back hence their IDs are increasing
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] potiuk commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
potiuk commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1121536312
Yeah. I think the problem is that the webserver spawns 4 parallel webserver processes (workers) and each process tries to update the permissions at the same time)
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] hterik commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
hterik commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1270258517
Seeing this in **2.4.1** also.
Workaround is to set config `update_fab_perms = False`
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] uranusjr commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
uranusjr commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1140363029
This seems somewhat related to #23926. cc @jhtimmins
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] jhtimmins closed issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
jhtimmins closed issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
URL: https://github.com/apache/airflow/issues/23512
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] jhtimmins commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
jhtimmins commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1154822554
@arroadie I semi-agree. Off hand, here are the basic reasons why it works this way.
1. In part it's that way bc it's a holdover from using Flask and Flask AppBuilder within Airflow. Modifying that would take a decent amount of work.
2. Airflow generally tries to decouple pieces as much as possible. Your proposal would create a situation where the webserver permissions being up to date would depend on the scheduler running.
3. If permissions load properly, the current design should guarantee that they're always up to date as soon as the webserver loads, and DB reads/writes should only occur once. Obviously this broke down here.
So while I'm not aware of your proposed approach being explicitly considered, these are some of the design considerations that support the current design. While it's imperfect, when it works, this approach gives good enough performance and enough flexibility.
That said, there's no reason that it _has_ to work this way, and there are downsides to this approach, such as the slower startup time for the webserver. If you encounter significant problems with the design, especially with multiple webservers, or have suggestions about better architectures, I encourage you to reach out to the community via the mailing list.
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] potiuk commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
potiuk commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1297164939
No confusion. There were two similar issues - one of them (for some users) fixed in 2.4.2 and another similar issue for some other users that will be released in 2.4.3. The milestone is always "current" thinking and if it moves, it moves.
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] zachliu commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
zachliu commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1154001197
can someone reopen this issue? thanks :pray:
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] zachliu commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
zachliu commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1150056527
@jhtimmins the fix https://github.com/apache/airflow/pull/24065 didn't fix this issue:
```
webserver_1 | [2022-06-08 15:14:49 +0000] [108] [INFO] Listening at: http://0.0.0.0:5000 (108)
webserver_1 | [2022-06-08 15:14:49 +0000] [108] [INFO] Using worker: sync
webserver_1 | [2022-06-08 15:14:49 +0000] [118] [INFO] Booting worker with pid: 118
webserver_1 | [2022-06-08 15:14:49 +0000] [119] [INFO] Booting worker with pid: 119
webserver_1 | [2022-06-08 15:14:49 +0000] [120] [INFO] Booting worker with pid: 120
webserver_1 | [2022-06-08 15:14:49 +0000] [121] [INFO] Booting worker with pid: 121
webserver_1 | [2022-06-08 15:14:49,915] {{logging_config.py:46}} INFO - Successfully imported user-defined logging config from log_config.LOGGING_CONFIG
webserver_1 | [2022-06-08 15:14:49,993] {{logging_config.py:46}} INFO - Successfully imported user-defined logging config from log_config.LOGGING_CONFIG
webserver_1 | [2022-06-08 15:14:49,994] {{logging_config.py:46}} INFO - Successfully imported user-defined logging config from log_config.LOGGING_CONFIG
webserver_1 | [2022-06-08 15:14:50,172] {{logging_config.py:46}} INFO - Successfully imported user-defined logging config from log_config.LOGGING_CONFIG
webserver_1 | [2022-06-08 15:14:50,232] {{manager.py:585}} INFO - Removed Permission can create on Users to role Admin
webserver_1 | [2022-06-08 15:14:50,255] {{manager.py:543}} INFO - Removed Permission View: can_create on Users
webserver_1 | [2022-06-08 15:14:50,435] {{manager.py:585}} INFO - Removed Permission menu access on Permissions to role Admin
webserver_1 | [2022-06-08 15:14:50,448] {{manager.py:543}} INFO - Removed Permission View: menu_access on Permissions
postgres_1 | 2022-06-08 15:14:50.544 UTC [41] ERROR: duplicate key value violates unique constraint "ab_view_menu_name_key"
postgres_1 | 2022-06-08 15:14:50.544 UTC [41] DETAIL: Key (name)=(Permission Pairs) already exists.
postgres_1 | 2022-06-08 15:14:50.544 UTC [41] STATEMENT: INSERT INTO ab_view_menu (id, name) VALUES (nextval('ab_view_menu_id_seq'), 'Permission Pairs') RETURNING ab_view_menu.id
webserver_1 | [2022-06-08 15:14:50,545] {{manager.py:420}} ERROR - Add View Menu Error: (psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "ab_view_menu_name_key"
webserver_1 | DETAIL: Key (name)=(Permission Pairs) already exists.
webserver_1 |
webserver_1 | [SQL: INSERT INTO ab_view_menu (id, name) VALUES (nextval('ab_view_menu_id_seq'), %(name)s) RETURNING ab_view_menu.id]
webserver_1 | [parameters: {'name': 'Permission Pairs'}]
webserver_1 | (Background on this error at: http://sqlalche.me/e/14/gkpj)
postgres_1 | 2022-06-08 15:14:50.554 UTC [42] ERROR: duplicate key value violates unique constraint "ab_permission_view_permission_id_view_menu_id_key"
postgres_1 | 2022-06-08 15:14:50.554 UTC [42] DETAIL: Key (permission_id, view_menu_id)=(5, 84) already exists.
postgres_1 | 2022-06-08 15:14:50.554 UTC [42] STATEMENT: INSERT INTO ab_permission_view (id, permission_id, view_menu_id) VALUES (nextval('ab_permission_view_id_seq'), 5, 84) RETURNING ab_permission_view.id
webserver_1 | [2022-06-08 15:14:50,554] {{manager.py:508}} INFO - Created Permission View: menu access on Permission Pairs
webserver_1 | [2022-06-08 15:14:50,555] {{manager.py:511}} ERROR - Creation of Permission View Error: (psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "ab_permission_view_permission_id_view_menu_id_key"
webserver_1 | DETAIL: Key (permission_id, view_menu_id)=(5, 84) already exists.
webserver_1 |
webserver_1 | [SQL: INSERT INTO ab_permission_view (id, permission_id, view_menu_id) VALUES (nextval('ab_permission_view_id_seq'), %(permission_id)s, %(view_menu_id)s) RETURNING ab_permission_view.id]
webserver_1 | [parameters: {'permission_id': 5, 'view_menu_id': 84}]
webserver_1 | (Background on this error at: http://sqlalche.me/e/14/gkpj)
webserver_1 | [2022-06-08 15:14:50,564] {{manager.py:568}} INFO - Added Permission menu access on Permission Pairs to role Admin
```
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] arroadie commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
arroadie commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1150474923
Agree. Saw your post previously when I first faced this issue (kudos on the investigation by the way) and yeah, I agree that this kind of operation should be executed previously to the web server being fired; ideally those tasks should be executed only by one agent (if using high availability) with some kind of lock to prevent race conditions. I read somewhere else previously that this is an issue with flask, hence the reason it wasn't fixed at airflow's codebase but I'd say your flag solution seems like a fair way to prevent the service availability.
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] arroadie commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
arroadie commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1150413110
Plus one to this issue. Having issues with multiple webservers running and never being able to reach stability since they are all trying to run this migration and conflicting to each other.
Is there a configuration/flag that can be used to prevent the webserver from trying to perform those changes?
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] cloventt commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
cloventt commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1226669818
I see this same issue very frequently when the webserver starts up. Here are logs of a crashed webserver pod:
```
[2022-08-25 01:30:38 +0000] [28] [INFO] Starting gunicorn 20.1.0
[2022-08-25 01:30:42,401] {providers_manager.py:215} INFO - Optional provider feature disabled when importing 'airflow.providers.google.leveldb.hooks.leveldb.LevelDBHook' from 'apache-airflow-providers-google' package
/home/airflow/.local/lib/python3.9/site-packages/azure/storage/common/_connection.py:82 SyntaxWarning: "is" with a literal. Did you mean "=="?
[2022-08-25 01:30:43 +0000] [28] [INFO] Listening at: http://0.0.0.0:8080 (28)
[2022-08-25 01:30:43 +0000] [28] [INFO] Using worker: sync
[2022-08-25 01:30:43 +0000] [46] [INFO] Booting worker with pid: 46
[2022-08-25 01:30:43 +0000] [47] [INFO] Booting worker with pid: 47
[2022-08-25 01:30:44 +0000] [48] [INFO] Booting worker with pid: 48
[2022-08-25 01:30:44 +0000] [49] [INFO] Booting worker with pid: 49
/home/airflow/.local/lib/python3.9/site-packages/airflow/www/app.py:111 DeprecationWarning: 'app.json_encoder' is deprecated and will be removed in Flask 2.3. Customize 'app.json_provider_class' or 'app.json' instead.
/home/airflow/.local/lib/python3.9/site-packages/airflow/www/app.py:111 DeprecationWarning: 'app.json_encoder' is deprecated and will be removed in Flask 2.3. Customize 'app.json_provider_class' or 'app.json' instead.
/home/airflow/.local/lib/python3.9/site-packages/airflow/www/app.py:111 DeprecationWarning: 'app.json_encoder' is deprecated and will be removed in Flask 2.3. Customize 'app.json_provider_class' or 'app.json' instead.
/home/airflow/.local/lib/python3.9/site-packages/airflow/www/app.py:111 DeprecationWarning: 'app.json_encoder' is deprecated and will be removed in Flask 2.3. Customize 'app.json_provider_class' or 'app.json' instead.
[2022-08-25 01:30:44,548] {manager.py:819} WARNING - No user yet created, use flask fab command to do it.
[2022-08-25 01:30:44,596] {manager.py:819} WARNING - No user yet created, use flask fab command to do it.
[2022-08-25 01:30:44,600] {manager.py:819} WARNING - No user yet created, use flask fab command to do it.
[2022-08-25 01:30:44,611] {manager.py:819} WARNING - No user yet created, use flask fab command to do it.
[2022-08-25 01:30:44,632] {manager.py:585} INFO - Removed Permission can create on Users to role Admin
[2022-08-25 01:30:44,659] {manager.py:543} INFO - Removed Permission View: can_create on Users
[2022-08-25 01:30:45,965] {providers_manager.py:215} INFO - Optional provider feature disabled when importing 'airflow.providers.google.leveldb.hooks.leveldb.LevelDBHook' from 'apache-airflow-providers-google' package
[2022-08-25 01:30:46,044] {providers_manager.py:215} INFO - Optional provider feature disabled when importing 'airflow.providers.google.leveldb.hooks.leveldb.LevelDBHook' from 'apache-airflow-providers-google' package
[2022-08-25 01:30:46,052] {providers_manager.py:215} INFO - Optional provider feature disabled when importing 'airflow.providers.google.leveldb.hooks.leveldb.LevelDBHook' from 'apache-airflow-providers-google' package
[2022-08-25 01:30:46,105] {providers_manager.py:215} INFO - Optional provider feature disabled when importing 'airflow.providers.google.leveldb.hooks.leveldb.LevelDBHook' from 'apache-airflow-providers-google' package
[2022-08-25 01:30:46,141] {providers_manager.py:215} INFO - Optional provider feature disabled when importing 'airflow.providers.google.leveldb.hooks.leveldb.LevelDBHook' from 'apache-airflow-providers-google' package
[2022-08-25 01:30:46,220] {providers_manager.py:215} INFO - Optional provider feature disabled when importing 'airflow.providers.google.leveldb.hooks.leveldb.LevelDBHook' from 'apache-airflow-providers-google' package
[2022-08-25 01:30:46,236] {providers_manager.py:215} INFO - Optional provider feature disabled when importing 'airflow.providers.google.leveldb.hooks.leveldb.LevelDBHook' from 'apache-airflow-providers-google' package
[2022-08-25 01:30:46,278] {providers_manager.py:215} INFO - Optional provider feature disabled when importing 'airflow.providers.google.leveldb.hooks.leveldb.LevelDBHook' from 'apache-airflow-providers-google' package
[2022-08-25 01:30:47,298] {manager.py:508} INFO - Created Permission View: can create on Users
[2022-08-25 01:30:47 +0000] [48] [ERROR] Exception in worker process
Traceback (most recent call last):
File "/home/airflow/.local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1802, in _execute_context
self.dialect.do_execute(
File "/home/airflow/.local/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 719, in do_execute
cursor.execute(statement, parameters)
psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "ab_permission_view_role_permission_view_id_role_id_key"
DETAIL: Key (permission_view_id, role_id)=(27009, 1) already exists.
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/home/airflow/.local/lib/python3.9/site-packages/gunicorn/arbiter.py", line 589, in spawn_worker
worker.init_process()
File "/home/airflow/.local/lib/python3.9/site-packages/gunicorn/workers/base.py", line 134, in init_process
self.load_wsgi()
File "/home/airflow/.local/lib/python3.9/site-packages/gunicorn/workers/base.py", line 146, in load_wsgi
self.wsgi = self.app.wsgi()
File "/home/airflow/.local/lib/python3.9/site-packages/gunicorn/app/base.py", line 67, in wsgi
self.callable = self.load()
File "/home/airflow/.local/lib/python3.9/site-packages/gunicorn/app/wsgiapp.py", line 58, in load
return self.load_wsgiapp()
File "/home/airflow/.local/lib/python3.9/site-packages/gunicorn/app/wsgiapp.py", line 48, in load_wsgiapp
return util.import_app(self.app_uri)
File "/home/airflow/.local/lib/python3.9/site-packages/gunicorn/util.py", line 412, in import_app
app = app(*args, **kwargs)
File "/home/airflow/.local/lib/python3.9/site-packages/airflow/www/app.py", line 158, in cached_app
app = create_app(config=config, testing=testing)
File "/home/airflow/.local/lib/python3.9/site-packages/airflow/www/app.py", line 146, in create_app
sync_appbuilder_roles(flask_app)
File "/home/airflow/.local/lib/python3.9/site-packages/airflow/www/app.py", line 68, in sync_appbuilder_roles
flask_app.appbuilder.sm.sync_roles()
File "/home/airflow/.local/lib/python3.9/site-packages/airflow/www/security.py", line 596, in sync_roles
self.update_admin_permission()
File "/home/airflow/.local/lib/python3.9/site-packages/airflow/www/security.py", line 578, in update_admin_permission
self.get_session.commit()
File "<string>", line 2, in commit
File "/home/airflow/.local/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 1428, in commit
self._transaction.commit(_to_root=self.future)
File "/home/airflow/.local/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 829, in commit
self._prepare_impl()
File "/home/airflow/.local/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 808, in _prepare_impl
self.session.flush()
File "/home/airflow/.local/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 3345, in flush
self._flush(objects)
File "/home/airflow/.local/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 3485, in _flush
transaction.rollback(_capture_exception=True)
File "/home/airflow/.local/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
compat.raise_(
File "/home/airflow/.local/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
raise exception
File "/home/airflow/.local/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 3445, in _flush
flush_context.execute()
File "/home/airflow/.local/lib/python3.9/site-packages/sqlalchemy/orm/unitofwork.py", line 456, in execute
rec.execute(self)
File "/home/airflow/.local/lib/python3.9/site-packages/sqlalchemy/orm/unitofwork.py", line 579, in execute
self.dependency_processor.process_saves(uow, states)
File "/home/airflow/.local/lib/python3.9/site-packages/sqlalchemy/orm/dependency.py", line 1182, in process_saves
self._run_crud(
File "/home/airflow/.local/lib/python3.9/site-packages/sqlalchemy/orm/dependency.py", line 1245, in _run_crud
connection.execute(statement, secondary_insert)
File "/home/airflow/.local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1289, in execute
return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
File "/home/airflow/.local/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 325, in _execute_on_connection
return connection._execute_clauseelement(
File "/home/airflow/.local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1481, in _execute_clauseelement
ret = self._execute_context(
File "/home/airflow/.local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1845, in _execute_context
self._handle_dbapi_exception(
File "/home/airflow/.local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2026, in _handle_dbapi_exception
util.raise_(
File "/home/airflow/.local/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
raise exception
File "/home/airflow/.local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1802, in _execute_context
self.dialect.do_execute(
File "/home/airflow/.local/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 719, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.IntegrityError: (psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "ab_permission_view_role_permission_view_id_role_id_key"
DETAIL: Key (permission_view_id, role_id)=(27009, 1) already exists.
[SQL: INSERT INTO ab_permission_view_role (id, permission_view_id, role_id) VALUES (nextval('ab_permission_view_role_id_seq'), %(permission_view_id)s, %(role_id)s) RETURNING ab_permission_view_role.id]
[parameters: {'permission_view_id': 27009, 'role_id': 1}]
(Background on this error at: https://sqlalche.me/e/14/gkpj)
[2022-08-25 01:30:47 +0000] [48] [INFO] Worker exiting (pid: 48)
10.202.97.30 - - [25/Aug/2022:01:30:47 +0000] "GET /home HTTP/1.1" 200 341888 "-" "kube-probe/1.21+"
[2022-08-25 01:30:48 +0000] [46] [INFO] Worker exiting (pid: 46)
[2022-08-25 01:30:48 +0000] [47] [INFO] Worker exiting (pid: 47)
[2022-08-25 01:30:48 +0000] [49] [INFO] Worker exiting (pid: 49)
[2022-08-25 01:30:49 +0000] [28] [INFO] Shutting down: Master
[2022-08-25 01:30:49 +0000] [28] [INFO] Reason: Worker failed to boot.
____________ _____________
____ |__( )_________ __/__ /________ __
____ /| |_ /__ ___/_ /_ __ /_ __ \_ | /| / /
___ ___ | / _ / _ __/ _ / / /_/ /_ |/ |/ /
_/_/ |_/_/ /_/ /_/ /_/ \____/____/|__/
Running the Gunicorn Server with:
Workers: 4 sync
Host: 0.0.0.0:8080
Timeout: 120
Logfiles: - -
Access Logformat:
=================================================================
[2022-08-25 01:30:50,071] {webserver_command.py:252} ERROR - [0 / 0] Some workers seem to have died and gunicorn did not restart them as expected
[2022-08-25 01:31:00,078] {webserver_command.py:264} INFO - [0 / 0] Spawning 1 workers
[2022-08-25 01:33:00,177] {webserver_command.py:217} ERROR - No response from gunicorn master within 120 seconds
[2022-08-25 01:33:00,177] {webserver_command.py:218} ERROR - Shutting down webserver
```
I am running Airflow 2.3.4 on kubernetes. I have two webserver pods each with 4 workers.
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] potiuk commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
potiuk commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1270514780
Ah. My mistake.
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] zachliu commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
zachliu commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1270475619
how does #26885 solve the clashing reads/writes? :thinking:
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] ajbosco commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
ajbosco commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1184677022
This issue is still happening in 2.3.3. Is the expectation that you run a single gunicorn worker?
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] bmoon4 commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
bmoon4 commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1184774896
im seeing same error in webserver (Airflow 2.3.3 + Postgres 13)
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] mdering commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
mdering commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1297310890
if the preload fix is indeed the fix, you can suppress this for now by setting the `GUNICORN_CMD_ARGS` environment variable to `--preload`
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] zachliu commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
zachliu commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1293752936
i checked out `2.4.2` and did
```bash
wget -qO - https://github.com/apache/airflow/pull/27297.patch | git apply -v -3
```
then built my own airflow
```bash
breeze release-management prepare-airflow-package --package-format=wheel --verbose
```
then installed it
```bash
pip install apache_airflow-2.4.2-py3-none-any.whl[...] --constraint ...
```
no more "duplicate key value violates unique constraint" errors
:rocket: :rocket: :rocket:
:rocket: :rocket:
:rocket:
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] zachliu commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
zachliu commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1140371309
aha, i think it's exactly the same as https://github.com/apache/airflow/issues/23926: all gunicorn workers are executing the same code causing clashes on the database
maybe i'll move my comments there
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] potiuk commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
potiuk commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1192495364
Hey @jhtimmins - is there any solution to this one ? Seems that other users are experiencing similar problems and it prevents them from migration to 2.3.3. https://apache-airflow.slack.com/archives/CCQ7EGB1P/p1658489623406909 for example
I looked at the discussion and could not see a clear solution or workaround for our users. Any ideas what can be done there?
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] zachliu commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
zachliu commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1231648497
@luebbert42 don't you have to restart the webserver after you update the config to use 4 gunicorn workers? :thinking:
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] potiuk commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
potiuk commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1250406851
Just to repeat it @jhtimmins - I think you are the only one who somewhat knows what the problem is about and what the solution might be (and you attempted to fix it in #24065). Do you have a clue what it could be? I think I would be willing to help with that and maybe even be able to provide some proposals to improve, but I have no clue where to start and what the reason might be (and seems that this one is still plaguing some users).
Any please where we could start looking at/ideas/hypotheses what could be wrong?
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] ahipp13 commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
ahipp13 commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1329870020
Issue submitted https://github.com/apache/airflow/issues/27977
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] cloventt commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
cloventt commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1228852935
I was able to “resolve” this issue by setting my webserver pods to have only 1 gunicorn worker, and then increasing the number of pods to get back to the expected number of workers. However this results in a considerable increase in how much memory my webserver pods have reserved on the cluster, so it would be nice if this issue could be resolved properly.
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] zachliu commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
zachliu commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1184848516
> How do you get past the error? Every time our webserver pod spins up it fails on this, kills the gunicorn works, and then restarts.
mine just recovers itself after a restart :thinking:
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] mjkonarski-b commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
mjkonarski-b commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1273485040
I can see the same problem after enabling Okta. Airflow 2.3.3.
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] lockkie0 commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
lockkie0 commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1272845305
I also see this issue every time when the web server starts up. Env AUTH_TYPE = AUTH_OID. I use Airflow 2.3.3 with postgresql . AIRFLOW__WEBSERVER__UPDATE_FAB_PERMS = False also does not change anything.
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] potiuk commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
potiuk commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1121468467
This sounds like a bug in sync_appbuilder_roles implementation - when we have more than 1 webserver it seems that when they are crashing during first start they then subsequently fail on starting another webserver via wsgi. @jhtimmins - maybe you can take a look at that ?
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] jhtimmins commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
jhtimmins commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1154709227
@zachliu my expectation from #24065 was that on startup you'd see a single round of old permissions getting deleted and then new ones getting created (since that PR changed the permission name). It works when running a single webserver, but looks like I'll need to do another round to figure out what's going on with multiple webservers.
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] arroadie commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
arroadie commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1150449925
fair, providing I didn't test latest main branch. Did you find a way to run more than one webserver without this being an issue @zachliu ?
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] zachliu commented on issue #23512: Random "duplicate key value violates unique constraint" errors when initializing the postgres database
Posted by GitBox <gi...@apache.org>.
zachliu commented on issue #23512:
URL: https://github.com/apache/airflow/issues/23512#issuecomment-1119069819
might be related to [this post](https://stackoverflow.com/questions/71850842/sqlalchemy-defining-two-relationship-with-the-same-secondary-leads-to-uniquev)
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org