search

PnX-SI / gn_module_ZH

Module de GeoNature d'inventaire des zones humides
GNU General Public License v3.0
4 stars 6 forks source link

Timeout generate patrimonales species #41

Closed cen-cgeier closed 1 week ago

cen-cgeier commented 9 months ago

Bonjour à tous,

GN version : 2.13.3 gn_ZH version : 1.2.0

Description du bug : En souhaitant générer une liste d'espèce patrimoniale lors d'une démo du module, image

Log : En regardant du côté du geonature.log

[2023-11-24 15:41:36 +0100] [2275] [INFO] Handling signal: hup
[2023-11-24 15:41:36 +0100] [2275] [INFO] Hang up: Master
[2023-11-24 15:41:36 +0100] [12161] [INFO] Worker exiting (pid: 12161)
[2023-11-24 15:41:36 +0100] [32598] [INFO] Booting worker with pid: 32598
[2023-11-24 15:41:36 +0100] [12163] [INFO] Worker exiting (pid: 12163)
[2023-11-24 15:41:36 +0100] [12165] [INFO] Worker exiting (pid: 12165)
[2023-11-24 15:41:36 +0100] [12164] [INFO] Worker exiting (pid: 12164)
[2023-11-24 15:41:36 +0100] [32600] [INFO] Booting worker with pid: 32600
[2023-11-24 15:41:36 +0100] [32601] [INFO] Booting worker with pid: 32601
[2023-11-24 15:41:36 +0100] [32599] [INFO] Booting worker with pid: 32599
[2023-11-24 15:41:36 +0100] [2275] [ERROR] Worker (pid:12165) was sent SIGTERM!
[2023-11-24 15:41:36 +0100] [2275] [ERROR] Worker (pid:12163) was sent SIGTERM!
[2023-11-24 15:41:36 +0100] [2275] [ERROR] Worker (pid:12161) was sent SIGTERM!
[2023-11-24 15:41:36 +0100] [2275] [ERROR] Worker (pid:12164) was sent SIGTERM!
[2023-11-24 15:42:21 +0100] [2275] [CRITICAL] WORKER TIMEOUT (pid:32598)
[2023-11-24 15:42:22 +0100] [2275] [ERROR] Worker (pid:32598) was sent SIGKILL! Perhaps out of memory?
[2023-11-24 15:42:22 +0100] [33015] [INFO] Booting worker with pid: 33015
[2023-11-24 15:42:53 +0100] [2275] [CRITICAL] WORKER TIMEOUT (pid:32599)
[2023-11-24 15:42:54 +0100] [2275] [ERROR] Worker (pid:32599) was sent SIGKILL! Perhaps out of memory?
[2023-11-24 15:42:54 +0100] [33151] [INFO] Booting worker with pid: 33151
cen-cgeier commented 9 months ago

J'ai regardé du côté des 3 vues vertebrate invertebrate flora. Elles sont extrêmement lente à charger. Plus de 3 minutes. J'ai tenter quelques améliorations pour voir si cela change quelques chose.. 8c344766e55c5564da734fa11be4c3c988916233 8805eab23472774cedcc55b07864b9e65c89918a 971602506278e3cf48da41f19b49fd059923bbd2 6ec9cc32fd5e43ed88f7cb2110094878be816a65

Nouveau log obtenu : 

[2023-11-27 12:50:42 +0100] [298012] [ERROR] Error handling request /geonature/api/zones_humides/283/taxa
Traceback (most recent call last):
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
    self.dialect.do_execute(
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 608, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.InvalidRowCountInLimitClause: ERREUR:  LIMIT ne doit pas être négative

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.8/site-packages/gunicorn/workers/sync.py", line 135, in handle
    self.handle_request(listener, req, client, addr)
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.8/site-packages/gunicorn/workers/sync.py", line 178, in handle_request
    respiter = self.wsgi(environ, resp.start_response)
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.8/site-packages/flask/app.py", line 2552, in __call__
    return self.wsgi_app(environ, start_response)
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.8/site-packages/werkzeug/middleware/dispatcher.py", line 78, in __call__
    return app(environ, start_response)
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.8/site-packages/werkzeug/middleware/shared_data.py", line 247, in __call__
    return self.app(environ, start_response)
  File "/home/geonatureadmin/geonature/backend/geonature/middlewares/__init__.py", line 16, in __call__
    return self.app(environ, new_start_response)
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.8/site-packages/werkzeug/middleware/proxy_fix.py", line 187, in __call__
    return self.app(environ, start_response)
  File "/home/geonatureadmin/geonature/backend/geonature/middlewares/__init__.py", line 27, in __call__
    return self.app(environ, start_response)
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.8/site-packages/flask/app.py", line 2532, in wsgi_app
    response = self.handle_exception(e)
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.8/site-packages/flask_cors/extension.py", line 176, in wrapped_function
    return cors_after_request(app.make_response(f(*args, **kwargs)))
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.8/site-packages/flask/app.py", line 2529, in wsgi_app
    response = self.full_dispatch_request()
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.8/site-packages/flask/app.py", line 1825, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.8/site-packages/flask_cors/extension.py", line 176, in wrapped_function
    return cors_after_request(app.make_response(f(*args, **kwargs)))
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.8/site-packages/flask/app.py", line 1676, in handle_user_exception
    return self.ensure_sync(handler)(e)
  File "/home/geonatureadmin/geonature/backend/geonature/core/errors.py", line 99, in handle_exception
    raise e
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.8/site-packages/flask/app.py", line 1823, in full_dispatch_request
    rv = self.dispatch_request()
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.8/site-packages/flask/app.py", line 1799, in dispatch_request
    return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)
  File "/home/geonatureadmin/geonature/backend/geonature/core/gn_permissions/decorators.py", line 62, in decorated_view
    return view_func(*args, **kwargs)
  File "/home/geonatureadmin/gn_module_ZH/backend/gn_module_zh/blueprint.py", line 822, in write_csv
    results = query.return_query().get("items", [])
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.8/site-packages/utils_flask_sqla/generic.py", line 284, in return_query
    data, nb_result_without_filter, nb_results = self.query()
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.8/site-packages/utils_flask_sqla/generic.py", line 273, in query
    data = self.set_limit(q).all()
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 3373, in all
    return list(self)
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 3535, in __iter__
    return self._execute_and_instances(context)
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 3560, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1011, in execute
    return meth(self, multiparams, params)
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 298, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1124, in _execute_clauseelement
    ret = self._execute_context(
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1316, in _execute_context
    self._handle_dbapi_exception(
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1510, in _handle_dbapi_exception
    util.raise_(
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
    self.dialect.do_execute(
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 608, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.DataError: (psycopg2.errors.InvalidRowCountInLimitClause) ERREUR:  LIMIT ne doit pas être négative

[SQL: SELECT pr_zh.vertebrates.id_zh AS pr_zh_vertebrates_id_zh, pr_zh.vertebrates.cd_nom AS pr_zh_vertebrates_cd_nom, pr_zh.vertebrates.group_class AS pr_zh_vertebrates_group_class, pr_zh.vertebrates.group_order AS pr_zh_vertebrates_group_order, pr_zh.vertebrates.scientific_name AS pr_zh_vertebrates_scientific_name, pr_zh.vertebrates.vernac_name AS pr_zh_vertebrates_vernac_name, pr_zh.vertebrates.statut_type AS pr_zh_vertebrates_statut_type, pr_zh.vertebrates.statut AS pr_zh_vertebrates_statut, pr_zh.vertebrates.article AS pr_zh_vertebrates_article, pr_zh.vertebrates.doc_url AS pr_zh_vertebrates_doc_url, pr_zh.vertebrates.last_date AS pr_zh_vertebrates_last_date, pr_zh.vertebrates.observer AS pr_zh_vertebrates_observer, pr_zh.vertebrates.organisme AS pr_zh_vertebrates_organisme, pr_zh.vertebrates.obs_nb AS pr_zh_vertebrates_obs_nb 
FROM pr_zh.vertebrates 
WHERE pr_zh.vertebrates.id_zh = %(id_zh_1)s ORDER BY pr_zh.vertebrates.id_zh 
 LIMIT %(param_1)s OFFSET %(param_2)s]
[parameters: {'id_zh_1': 283, 'param_1': -1, 'param_2': 0}]
(Background on this error at: http://sqlalche.me/e/13/9h9h)

Il semble que dans la fonction gn_module_ZH > backend > gn_module_zh > blueprint.py > write_csv, la variable limit=-1 est transmise à la requête alors qu'elle ne devrait pas : 

    # gn_module_ZH > backend > gn_module_zh > blueprint.py > write_csv
    # l.814
    ...
    for i in ["vertebrates_view_name", "invertebrates_view_name", "flora_view_name"]:
        query = GenericQuery(
            DB=DB,
            tableName=blueprint.config[i]["table_name"],
            schemaName=blueprint.config[i]["schema_name"],
            filters={"id_zh": id_zh, "orderby": "id_zh"},
            limit=-1,
        )
        results = query.return_query().get("items", [])
        ....
cen-cgeier commented 9 months ago

En définissant limit=10000, je retrouve un log similaire au premier message : 

[2023-11-27 16:50:47 +0100] [2275] [CRITICAL] WORKER TIMEOUT (pid:298510)
[2023-11-27 16:50:48 +0100] [2275] [ERROR] Worker (pid:298510) was sent SIGKILL! Perhaps out of memory?
[2023-11-27 16:50:48 +0100] [354062] [INFO] Booting worker with pid: 354062
camillemonchicourt commented 9 months ago

Oui, c'est peut-être optimisable en l'état ou simplifiable. Car renvoyer une liste d'observations de la synthèse en faisant une intersection avec la géométrie des ZH ne devrait pas finir en timeout.

cen-cgeier commented 9 months ago

La fonction était un peu longue avant mais elle fonctionnait dans la version 1.0.0 du module. Je ne suis pas certain du fonctionnement de la fonction lors de la version 1.1.0.

Il est possible que l'évolution de la classe utils_flask_sqla > genéric.py > GenericQuery y soit pour quelque chose. Malheureusement pour moi, je ne parviens pas à configurer correctement mon vscode afin de faire fonctionner correctement le debugger... sniff

edelclaux commented 1 week ago

Résolue dans la release 1.3.0