Bug: ssl error with postgres and celery

[Thomas De Schampheleire]

Hello Valentin,

El mar., 3 nov. 2020 a las 17:08, Valentin Kleibel (<valentin at vrvis.at>)
escribió:

> Hi,
>
> We are using kallithea with a postgres database on another server
> connected via ssl.
> It seems that celery in version 4 uses prefork in its worker pool model
> now, which leads to issues with such a database connection:
>
> ```
> Task kallithea.lib.celerylib.whoosh_index[9c
> 4dbbcc-b9e9-48be-9455-0bbcd8d251a2] raised unexpected:
> OperationalError('(psycopg2.Operational
> Error) SSL SYSCALL error: EOF detected\n')
> Traceback (most recent call last):
>    File
>
> "/mnt/webservices/virtualenv/kallithea-0.6.2/lib/python3.7/site-packages/sqlalchemy/eng
> ine/base.py", line 1277, in _execute_context
>      cursor, statement, parameters, context
>    File
>
> "/mnt/webservices/virtualenv/kallithea-0.6.2/lib/python3.7/site-packages/sqlalchemy/eng
> ine/default.py", line 593, in do_execute
>      cursor.execute(statement, parameters)
> psycopg2.OperationalError: SSL SYSCALL error: EOF detected
>
> The above exception was the direct cause of the following exception:
> (...)
> ```
>
> We know that issues like this may arise from using prefork in uwsgi
> which we work around by setting lazy:true
> The reason for this is that when a database connection with SSL (as we
> use) is already established with a valid SSL context before a process is
> forked, the connection will cease to work as such a connection is
> stateful on both sides and cannot cope well with a fork.
>
> The only workaround to this we found is to setup celery to only use a
> single worker in production.ini:
>
> ```
> celery.worker_concurrency = 1
> celery.worker_max_tasks_per_child = 5
> ```
>
> But this has the drawback that long running tasks like indexing will
> block short running tasks like the creation of a new repository. Thus,
> this setting is undesired and worker_concurrency should be at least 2 on
> a production system.
>
> I tried to find an easy fix for this but it seems to need some changes
> to kallithea as long as celery and sqalchemy don't change to cope with
> this problem.
>
> I hope someone can help with this.
>


I don't have answers but several questions.

Can you say more about how you set up the database connection? Do you do
anything special here? Why is it different with SSL than without?
As far as I can see you would just need to give some extra parameters in
the sqlalchemy.url setting in your ini file.
But your mail seems to suggest that you have a connection created upfront
and would like this one to be reused?

Which are the uwsgi-related settings in your ini file? The 'lazy=true'
setting is not a choice for Kallithea and should be enabled in any case.
Our uwsgi template for ini files gives this comment as clarification for
'lazy=true': "App *must* be loaded in workers - db connections can't be
shared"

Did you use Kallithea in this mode successfully before, perhaps on an older
version? Your mail seems to suggest that but I'm not sure.

Note: I found this related question:
https://stackoverflow.com/questions/51466007/how-to-use-psycopg2-properly-in-a-prefork-celery-environment
but it does not involve sqlalchemy and does not really give immediate
solutions.


Best regards,
Thomas
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.sfconservancy.org/pipermail/kallithea-general/attachments/20201103/65b6ec82/attachment.html>