Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

1.5.0 - Alembic - erreur SQL #185

Closed
gildeluermoz opened this issue Jun 30, 2023 · 8 comments
Closed

1.5.0 - Alembic - erreur SQL #185

gildeluermoz opened this issue Jun 30, 2023 · 8 comments

Comments

@gildeluermoz
Copy link
Contributor

Lors de la mise à jour de geonature 2.11.2 vers 2.12.3 avec le module d'export 1.3.0 vers 1.5.0 une erreur survient dans le migration.sh à l'étape de mise à jour de la base :

psycopg2.errors.NotNullViolation: ERREUR:  une valeur NULL viole la contrainte NOT NULL de la colonne « id_role » dans la relation « t_notifications_rules »
DETAIL:  La ligne en échec contient (1, null, DB, EXPORT-DONE).

le champ id_role est effectivement en not null dans la table t_notifications_rules .
Ca se passe ici : https://github.com/PnX-SI/gn_module_export/blob/master/backend/gn_module_export/migrations/4cac712a2ce6_create_export_notifications.py#L95-L99C2

J'ai contourné en mettant ceci (avec un utilisateur 'test' existant)

        INSERT INTO
            gn_notifications.t_notifications_rules (id_role, code_category, code_method)
        VALUES
            (25, '{CATEGORY_CODE}', 'DB'),
            (25, '{CATEGORY_CODE}', 'EMAIL')

J'ai relancé le migration.sh qui a pu se terminer.
Je n'ai pas encore identifié à quel utilisateur il faudrait se rapporter pour cette notification. Un ou des groupes peut-être ?
@camillemonchicourt
Copy link
Member

Non, c'est pas normal et il ne faut pas faire comme ça.
C'est volontaire de créer une règle sans id_role par défaut pour chaque nouveau type de notification pour que tous les utilisateurs soient abonnés par défaut à cette notification, sans devoir explicitement s'y abonner.

C'est mis en place ici et c'est ça qui doit te manquer (je sais pas pourquoi) : https://github.com/PnX-SI/GeoNature/pull/2267/files#diff-3ec1149290fae475ca9679cc78181450ddff13eb6dd5fdc16584c6427c047fac

@camillemonchicourt camillemonchicourt changed the title Alembic - erreur SQL 1.5.0 - Alembic - erreur SQL Jun 30, 2023
@gildeluermoz
Copy link
Contributor Author

suite à l'erreur, j'ai relancé la commande geonature db autoupgrade et j'ai ceci comme retour :

geonature db autoupgrade 
/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/sqlalchemy/orm/mapper.py:1841: SAWarning: Property TBaseVisits.dataset on mapped class TBaseVisits->t_base_visits being replaced with new property TBaseVisits.dataset; the old property will be discarded
  util.warn(
Context impl PostgresqlImpl.
Will assume transactional DDL.
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade 4cf3fd5d06f5 -> 09a637f06b96, Default notification rules
INFO  [alembic.runtime.migration] Running upgrade 09a637f06b96 -> 8888e5cce63b, add index on id_area for table cor_area_synthese
INFO  [alembic.runtime.migration] Running upgrade 8888e5cce63b -> 497f52d996dd, TAdditionalFields : remove useless columns and values
INFO  [alembic.runtime.migration] Running upgrade 497f52d996dd -> 0cae32a010ea, lstrip static/medias/ from t_medias.media_path
INFO  [alembic.runtime.migration] Running upgrade 0cae32a010ea -> 5d65f9c93a32, fix permissions view
INFO  [alembic.runtime.migration] Running upgrade 5d65f9c93a32 -> 8279ce74006b, set modules default type
INFO  [alembic.runtime.migration] Running upgrade 8279ce74006b -> 05a91edb6796, Improve the view gn_synthese.v_export_for_synthese
INFO  [alembic.runtime.migration] Running upgrade 05a91edb6796 -> cec41a6d3a15, revome v_roles_permissions
INFO  [alembic.runtime.migration] Running upgrade cec41a6d3a15 -> 9e9218653d6c, add synthese log history
INFO  [alembic.runtime.migration] Running upgrade 9e9218653d6c -> cf1c1fdbde77, correction-sql-on-delete-module
INFO  [alembic.runtime.migration] Running upgrade cf1c1fdbde77 -> e2a94808cf76, add notifications object
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade c2d02e345a06 -> 4cac712a2ce6, Create export notifications
INFO  [alembic.runtime.migration] Running upgrade 4cac712a2ce6 -> fdc2d823a8b9, Add primary key view column in t_exports
INFO  [alembic.runtime.migration] Running upgrade fdc2d823a8b9 -> 75edd92560d7, add token in gn_exports.cor_exports_roles
INFO  [alembic.runtime.migration] Running upgrade 75edd92560d7 -> fe1347f4805f, Fix and improve SINP DEE synthese view (see #105)
INFO  [alembic.runtime.migration] Running upgrade fe1347f4805f -> 7876d042e673, Delete shp export
INFO  [alembic.runtime.migration] Running upgrade 7876d042e673 -> bcee745e5647, drop table gn_exports.t_exports_logs
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade a11c9a2db7bb -> 485a659efdcd, add 'IMPORT-DONE' notification
INFO  [alembic.runtime.migration] Running upgrade 485a659efdcd -> 5158afe602d2, default notification

Première ligne, il y a bien la révision 09a637f06b96 qui est passée.
J'ai ensuite relancé le migration.sh

Au final dans la base, j'ai bien l'id_role qui est passé en nullable et j'ai ce contenu :
image

Je vais retirer l'id_role 25 mais est-ce qu'il n'y a pas un souci tout de même ? Du genre que le passage du champ id_role en nullable soit fait après l'insertion qui lève l'erreur ?
Je remets les logs de la console avec l'enchaînement des commandes et la trace complète de l'erreur . A noter qu'avant de lancer manuellement le geonature db autoupgrade, j'ai fait la modif SQL indiquée dans mon premier post sur le fichier de révision qui ne passait pas.:

Rebuild du frontend terminé.
Mise à jour de la base de données…
/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/sqlalchemy/orm/mapper.py:1841: SAWarning: Property TBaseVisits.dataset on mapped class TBaseVisits->t_base_visits being replaced with new property TBaseVisits.dataset; the old property will be discarded
  util.warn(
/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/sqlalchemy/orm/mapper.py:1841: SAWarning: Property TBaseVisits.dataset on mapped class TBaseVisits->t_base_visits being replaced with new property TBaseVisits.dataset; the old property will be discarded
  util.warn(
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/sqlalchemy/orm/mapper.py:1841: SAWarning: Property TBaseVisits.dataset on mapped class TBaseVisits->t_base_visits being replaced with new property TBaseVisits.dataset; the old property will be discarded
  util.warn(
Context impl PostgresqlImpl.
Will assume transactional DDL.
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade f2c36312b3de -> 27fd7e2b4b79, Add vm_taxref_list_forautocomplete index
INFO  [alembic.runtime.migration] Running upgrade 27fd7e2b4b79 -> 188bc535258a, Drop old status table
INFO  [alembic.runtime.migration] Running upgrade 188bc535258a -> 23c25552d707, Create bdc_status_table if not exists
INFO  [alembic.runtime.migration] Running upgrade 23c25552d707 -> 6607b25b2d66, Taxref : set null to empty string
INFO  [alembic.runtime.migration] Running upgrade 6607b25b2d66 -> 3bd542b72955, optimize_vm_taxref_for_autocomplete
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade 4c97453a2d1a -> 9668b861bdb6, remove v_releve_occtax
INFO  [alembic.runtime.migration] Running upgrade 9668b861bdb6 -> 0ff94776a962, t_releves triggers optimization
INFO  [alembic.runtime.migration] Running upgrade 0ff94776a962 -> 1b4f44762020, update cd_hab in fct_tri_synthese_update_releve
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade c2d02e345a06 -> 4cac712a2ce6, Create export notifications
Traceback (most recent call last):
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
    self.dialect.do_execute(
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 608, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.NotNullViolation: ERREUR:  une valeur NULL viole la contrainte NOT NULL de la colonne « id_role » dans la relation « t_notifications_rules »
DETAIL:  La ligne en échec contient (1, null, DB, EXPORT-DONE).


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

Traceback (most recent call last):
  File "/home/geonatureadmin/geonature/backend/venv/bin/geonature", line 8, in <module>
    sys.exit(main())
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/click/core.py", line 1130, in __call__
    return self.main(*args, **kwargs)
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/click/core.py", line 1055, in main
    rv = self.invoke(ctx)
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/click/core.py", line 1657, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/click/core.py", line 1657, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/click/core.py", line 1404, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/click/core.py", line 760, in invoke
    return __callback(*args, **kwargs)
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/click/decorators.py", line 26, in new_func
    return f(get_current_context(), *args, **kwargs)
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/flask/cli.py", line 357, in decorator
    return __ctx.invoke(f, *args, **kwargs)
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/click/core.py", line 760, in invoke
    return __callback(*args, **kwargs)
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/utils_flask_sqla/commands.py", line 100, in autoupgrade
    flask_migrate.upgrade(directory, revision, sql, tag, x_arg)
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/flask_migrate/__init__.py", line 98, in wrapped
    f(*args, **kwargs)
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/flask_migrate/__init__.py", line 185, in upgrade
    command.upgrade(config, revision, sql=sql, tag=tag)
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/alembic/command.py", line 322, in upgrade
    script.run_env()
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/alembic/script/base.py", line 569, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/alembic/util/pyfiles.py", line 94, in load_python_file
    module = load_module_py(module_id, path)
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/alembic/util/pyfiles.py", line 110, in load_module_py
    spec.loader.exec_module(module)  # type: ignore
  File "<frozen importlib._bootstrap_external>", line 790, in exec_module
  File "<frozen importlib._bootstrap>", line 228, in _call_with_frames_removed
  File "/home/geonatureadmin/geonature/backend/geonature/migrations/env.py", line 88, in <module>
    run_migrations_online()
  File "/home/geonatureadmin/geonature/backend/geonature/migrations/env.py", line 82, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/alembic/runtime/environment.py", line 853, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/alembic/runtime/migration.py", line 623, in run_migrations
    step.migration_fn(**kw)
  File "/home/geonatureadmin/gn_module_export/backend/gn_module_export/migrations/4cac712a2ce6_create_export_notifications.py", line 93, in upgrade
    op.execute(
  File "<string>", line 8, in execute
  File "<string>", line 3, in execute
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/alembic/operations/ops.py", line 2414, in execute
    return operations.invoke(op)
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/alembic/operations/base.py", line 399, in invoke
    return fn(self, operation)
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/alembic/operations/toimpl.py", line 207, in execute_sql
    operations.migration_context.impl.execute(
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/alembic/ddl/impl.py", line 202, in execute
    self._exec(sql, execution_options)
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/alembic/ddl/impl.py", line 195, in _exec
    return conn.execute(construct, multiparams)
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1011, in execute
    return meth(self, multiparams, params)
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.9/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.9/site-packages/sqlalchemy/engine/base.py", line 1124, in _execute_clauseelement
    ret = self._execute_context(
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1316, in _execute_context
    self._handle_dbapi_exception(
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1510, in _handle_dbapi_exception
    util.raise_(
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
    self.dialect.do_execute(
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 608, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.IntegrityError: (psycopg2.errors.NotNullViolation) ERREUR:  une valeur NULL viole la contrainte NOT NULL de la colonne « id_role » dans la relation « t_notifications_rules »
DETAIL:  La ligne en échec contient (1, null, DB, EXPORT-DONE).

[SQL: 
        INSERT INTO
            gn_notifications.t_notifications_rules (code_category, code_method)
        VALUES
            ('EXPORT-DONE', 'DB'),
            ('EXPORT-DONE', 'EMAIL')
        ]
(Background on this error at: http://sqlalche.me/e/13/gkpj)
geonatureadmin@vps-xxxx:~/geonature$ ^C
geonatureadmin@vps-xxxx:~/geonature$ ^C
geonatureadmin@vps-xxxxx:~/geonature$ ^C
geonatureadmin@vps-xxxxx:~/geonature$ source backend/venv/bin/activate
(venv) geonatureadmin@vps-xxxxx:~/geonature$ geonature db autoupgrade 
/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/sqlalchemy/orm/mapper.py:1841: SAWarning: Property TBaseVisits.dataset on mapped class TBaseVisits->t_base_visits being replaced with new property TBaseVisits.dataset; the old property will be discarded
  util.warn(
Context impl PostgresqlImpl.
Will assume transactional DDL.
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade 4cf3fd5d06f5 -> 09a637f06b96, Default notification rules
INFO  [alembic.runtime.migration] Running upgrade 09a637f06b96 -> 8888e5cce63b, add index on id_area for table cor_area_synthese
INFO  [alembic.runtime.migration] Running upgrade 8888e5cce63b -> 497f52d996dd, TAdditionalFields : remove useless columns and values
INFO  [alembic.runtime.migration] Running upgrade 497f52d996dd -> 0cae32a010ea, lstrip static/medias/ from t_medias.media_path
INFO  [alembic.runtime.migration] Running upgrade 0cae32a010ea -> 5d65f9c93a32, fix permissions view
INFO  [alembic.runtime.migration] Running upgrade 5d65f9c93a32 -> 8279ce74006b, set modules default type
INFO  [alembic.runtime.migration] Running upgrade 8279ce74006b -> 05a91edb6796, Improve the view gn_synthese.v_export_for_synthese
INFO  [alembic.runtime.migration] Running upgrade 05a91edb6796 -> cec41a6d3a15, revome v_roles_permissions
INFO  [alembic.runtime.migration] Running upgrade cec41a6d3a15 -> 9e9218653d6c, add synthese log history
INFO  [alembic.runtime.migration] Running upgrade 9e9218653d6c -> cf1c1fdbde77, correction-sql-on-delete-module
INFO  [alembic.runtime.migration] Running upgrade cf1c1fdbde77 -> e2a94808cf76, add notifications object
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade c2d02e345a06 -> 4cac712a2ce6, Create export notifications
INFO  [alembic.runtime.migration] Running upgrade 4cac712a2ce6 -> fdc2d823a8b9, Add primary key view column in t_exports
INFO  [alembic.runtime.migration] Running upgrade fdc2d823a8b9 -> 75edd92560d7, add token in gn_exports.cor_exports_roles
INFO  [alembic.runtime.migration] Running upgrade 75edd92560d7 -> fe1347f4805f, Fix and improve SINP DEE synthese view (see #105)
INFO  [alembic.runtime.migration] Running upgrade fe1347f4805f -> 7876d042e673, Delete shp export
INFO  [alembic.runtime.migration] Running upgrade 7876d042e673 -> bcee745e5647, drop table gn_exports.t_exports_logs
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade a11c9a2db7bb -> 485a659efdcd, add 'IMPORT-DONE' notification
INFO  [alembic.runtime.migration] Running upgrade 485a659efdcd -> 5158afe602d2, default notification
(venv) geonatureadmin@vps-xxxxx:~/geonature$ deactivate 
geonatureadmin@vps-xxxxx:~/geonature$ ./install/migration/migration.sh

@camillemonchicourt
Copy link
Member

Normalement le migration.sh met à jour GN, puis sa BDD, puis il met à jour après les modules et leur BDD, donc je capte pas pourquoi tu as eu ce soucis.

@gildeluermoz
Copy link
Contributor Author

Oui je suis d'accord avec toi. Je ne comprends pas non plus, mais si tu regardes la sequence de l'erreur ci-dessous, tu peux voir que la révision 09a637f06b96 qui passe le champ id_role en nullable n'est pas faite alors que quand je fais le geonature db autoupgrade manuellement elle est en premier... ???

Mise à jour de la base de données…
/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/sqlalchemy/orm/mapper.py:1841: SAWarning: Property TBaseVisits.dataset on mapped class TBaseVisits->t_base_visits being replaced with new property TBaseVisits.dataset; the old property will be discarded
  util.warn(
/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/sqlalchemy/orm/mapper.py:1841: SAWarning: Property TBaseVisits.dataset on mapped class TBaseVisits->t_base_visits being replaced with new property TBaseVisits.dataset; the old property will be discarded
  util.warn(
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/sqlalchemy/orm/mapper.py:1841: SAWarning: Property TBaseVisits.dataset on mapped class TBaseVisits->t_base_visits being replaced with new property TBaseVisits.dataset; the old property will be discarded
  util.warn(
Context impl PostgresqlImpl.
Will assume transactional DDL.
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade f2c36312b3de -> 27fd7e2b4b79, Add vm_taxref_list_forautocomplete index
INFO  [alembic.runtime.migration] Running upgrade 27fd7e2b4b79 -> 188bc535258a, Drop old status table
INFO  [alembic.runtime.migration] Running upgrade 188bc535258a -> 23c25552d707, Create bdc_status_table if not exists
INFO  [alembic.runtime.migration] Running upgrade 23c25552d707 -> 6607b25b2d66, Taxref : set null to empty string
INFO  [alembic.runtime.migration] Running upgrade 6607b25b2d66 -> 3bd542b72955, optimize_vm_taxref_for_autocomplete
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade 4c97453a2d1a -> 9668b861bdb6, remove v_releve_occtax
INFO  [alembic.runtime.migration] Running upgrade 9668b861bdb6 -> 0ff94776a962, t_releves triggers optimization
INFO  [alembic.runtime.migration] Running upgrade 0ff94776a962 -> 1b4f44762020, update cd_hab in fct_tri_synthese_update_releve
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade c2d02e345a06 -> 4cac712a2ce6, Create export notifications
Traceback (most recent call last):
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
    self.dialect.do_execute(
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 608, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.NotNullViolation: ERREUR:  une valeur NULL viole la contrainte NOT NULL de la colonne « id_role » dans la relation « t_notifications_rules »
DETAIL:  La ligne en échec contient (1, null, DB, EXPORT-DONE).


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

Traceback (most recent call last):
  File "/home/geonatureadmin/geonature/backend/venv/bin/geonature", line 8, in <module>
    sys.exit(main())
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/click/core.py", line 1130, in __call__
    return self.main(*args, **kwargs)
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/click/core.py", line 1055, in main
    rv = self.invoke(ctx)
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/click/core.py", line 1657, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/click/core.py", line 1657, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/click/core.py", line 1404, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/click/core.py", line 760, in invoke
    return __callback(*args, **kwargs)
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/click/decorators.py", line 26, in new_func
    return f(get_current_context(), *args, **kwargs)
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/flask/cli.py", line 357, in decorator
    return __ctx.invoke(f, *args, **kwargs)
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/click/core.py", line 760, in invoke
    return __callback(*args, **kwargs)
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/utils_flask_sqla/commands.py", line 100, in autoupgrade
    flask_migrate.upgrade(directory, revision, sql, tag, x_arg)
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/flask_migrate/__init__.py", line 98, in wrapped
    f(*args, **kwargs)
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/flask_migrate/__init__.py", line 185, in upgrade
    command.upgrade(config, revision, sql=sql, tag=tag)
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/alembic/command.py", line 322, in upgrade
    script.run_env()
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/alembic/script/base.py", line 569, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/alembic/util/pyfiles.py", line 94, in load_python_file
    module = load_module_py(module_id, path)
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/alembic/util/pyfiles.py", line 110, in load_module_py
    spec.loader.exec_module(module)  # type: ignore
  File "<frozen importlib._bootstrap_external>", line 790, in exec_module
  File "<frozen importlib._bootstrap>", line 228, in _call_with_frames_removed
  File "/home/geonatureadmin/geonature/backend/geonature/migrations/env.py", line 88, in <module>
    run_migrations_online()
  File "/home/geonatureadmin/geonature/backend/geonature/migrations/env.py", line 82, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/alembic/runtime/environment.py", line 853, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/alembic/runtime/migration.py", line 623, in run_migrations
    step.migration_fn(**kw)
  File "/home/geonatureadmin/gn_module_export/backend/gn_module_export/migrations/4cac712a2ce6_create_export_notifications.py", line 93, in upgrade
    op.execute(
  File "<string>", line 8, in execute
  File "<string>", line 3, in execute
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/alembic/operations/ops.py", line 2414, in execute
    return operations.invoke(op)
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/alembic/operations/base.py", line 399, in invoke
    return fn(self, operation)
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/alembic/operations/toimpl.py", line 207, in execute_sql
    operations.migration_context.impl.execute(
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/alembic/ddl/impl.py", line 202, in execute
    self._exec(sql, execution_options)
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/alembic/ddl/impl.py", line 195, in _exec
    return conn.execute(construct, multiparams)
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1011, in execute
    return meth(self, multiparams, params)
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.9/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.9/site-packages/sqlalchemy/engine/base.py", line 1124, in _execute_clauseelement
    ret = self._execute_context(
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1316, in _execute_context
    self._handle_dbapi_exception(
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1510, in _handle_dbapi_exception
    util.raise_(
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
    self.dialect.do_execute(
  File "/home/geonatureadmin/geonature/backend/venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 608, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.IntegrityError: (psycopg2.errors.NotNullViolation) ERREUR:  une valeur NULL viole la contrainte NOT NULL de la colonne « id_role » dans la relation « t_notifications_rules »
DETAIL:  La ligne en échec contient (1, null, DB, EXPORT-DONE).

[SQL: 
        INSERT INTO
            gn_notifications.t_notifications_rules (code_category, code_method)
        VALUES
            ('EXPORT-DONE', 'DB'),
            ('EXPORT-DONE', 'EMAIL')
        ]
(Background on this error at: http://sqlalche.me/e/13/gkpj)

@bouttier
Copy link
Contributor

bouttier commented Jul 8, 2023

Il serait pas mal de rajouter à la révision 4cac712a2ce6 d’export une dépendance vers la révision 09a637f06b96 de GeoNature pour être sûr de l’exécution dans le bon ordre !
Le migrate.sh fait un geonature db autoupgrade qui ne met pas forcément à jour la branche geonature en premier (c’est plus ou moins aléatoire car normalement sans importance), d’où l’intérêt de la dépendance car ici l’ordre importe.

@camillemonchicourt
Copy link
Member

Ah OK important en effet.
Je pensais que le migration.sh mettait d'abord à jour la BDD du cœur de GeoNature puis celle des modules.
Mais non, ce sont juste des branches et il ne fait pas de différences.
D'où l'importance des dépendances de branches à bien déclarer.
OK je vois. Pour les notifications il y en a peut-être d'autres dans ce cas.
Merci.

@Pierre-Narcisi Pierre-Narcisi mentioned this issue Jul 11, 2023
Merged
@gildeluermoz
Copy link
Contributor Author

👍
Merci pour la correction

@camillemonchicourt
Copy link
Member

Fait dans la 1.5.1

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
3 participants
@bouttier @camillemonchicourt @gildeluermoz