Inconsistent issues with restoring an environment #173

aleuffre · 2024-07-10T08:15:54Z

aleuffre
Jul 10, 2024

Hello everyone,

I have an issue with one of my bigger installations, though in the grand scheme it's not that big (a couple of GBs when compressed) and it definitely shouldn't cause problems.

In short, we backup the database and filestore using click-odoo-backupdb and we use click-odoo-restoredb to restore it in another environment for testing. Then, we run an update of all the installed modules.

It's at this point that, sometimes, we encounter the following error

psycopg2.errors.InvalidForeignKey: there is no unique constraint matching given keys for referenced table "mail_message"

Full traceback

Odoo Server Error

Traceback (most recent call last):
  File "/usr/lib/python3/dist-packages/odoo/addons/base/models/ir_http.py", line 237, in _dispatch
    result = request.dispatch()
  File "/usr/lib/python3/dist-packages/odoo/http.py", line 696, in dispatch
    result = self._call_function(**self.params)
  File "/usr/lib/python3/dist-packages/odoo/http.py", line 370, in _call_function
    return checked_call(self.db, *args, **kwargs)
  File "/usr/lib/python3/dist-packages/odoo/service/model.py", line 94, in wrapper
    return f(dbname, *args, **kwargs)
  File "/usr/lib/python3/dist-packages/odoo/http.py", line 358, in checked_call
    result = self.endpoint(*a, **kw)
  File "/usr/lib/python3/dist-packages/odoo/http.py", line 919, in call
    return self.method(*args, **kw)
  File "/usr/lib/python3/dist-packages/odoo/http.py", line 544, in response_wrap
    response = f(*args, **kw)
  File "/usr/lib/python3/dist-packages/odoo/addons/web/controllers/main.py", line 1374, in call_button
    action = self._call_kw(model, method, args, kwargs)
  File "/usr/lib/python3/dist-packages/odoo/addons/web/controllers/main.py", line 1362, in _call_kw
    return call_kw(request.env[model], method, args, kwargs)
  File "/usr/lib/python3/dist-packages/odoo/api.py", line 404, in call_kw
    result = _call_kw_multi(method, model, args, kwargs)
  File "/usr/lib/python3/dist-packages/odoo/api.py", line 391, in _call_kw_multi
    result = method(recs, *args, **kwargs)
  File "/opt/odoo/custom/oca/server-tools/module_auto_update/wizard/module_auto_update.py", line 26, in action_upgrade_module
    self.env["ir.module.module"].upgrade_changed_checksum()
  File "/opt/odoo/custom/oca/server-tools/module_auto_update/models/module.py", line 161, in upgrade_changed_checksum
    self.env["base.module.upgrade"].upgrade_module()
  File "/usr/lib/python3/dist-packages/odoo/addons/base/wizard/base_module_upgrade.py", line 74, in upgrade_module
    odoo.modules.registry.Registry.new(self._cr.dbname, update_module=True)
  File "/usr/lib/python3/dist-packages/odoo/modules/registry.py", line 89, in new
    odoo.modules.load_modules(registry._db, force_demo, status, update_module)
  File "/usr/lib/python3/dist-packages/odoo/modules/loading.py", line 455, in load_modules
    processed_modules += load_marked_modules(cr, graph,
  File "/usr/lib/python3/dist-packages/odoo/modules/loading.py", line 347, in load_marked_modules
    loaded, processed = load_module_graph(
  File "/usr/lib/python3/dist-packages/odoo/modules/loading.py", line 199, in load_module_graph
    registry.init_models(cr, model_names, {'module': package.name}, new_install)
  File "/usr/lib/python3/dist-packages/odoo/modules/registry.py", line 420, in init_models
    self.check_foreign_keys(cr)
  File "/usr/lib/python3/dist-packages/odoo/modules/registry.py", line 499, in check_foreign_keys
    sql.add_foreign_key(cr, table1, column1, table2, column2, ondelete)
  File "/usr/lib/python3/dist-packages/odoo/tools/sql.py", line 170, in add_foreign_key
    cr.execute(query.format(tablename1, columnname1, tablename2, columnname2, ondelete))
  File "<decorator-gen-5>", line 2, in execute
  File "/usr/lib/python3/dist-packages/odoo/sql_db.py", line 101, in check
    return f(self, *args, **kwargs)
  File "/usr/lib/python3/dist-packages/odoo/sql_db.py", line 301, in execute
    res = self._obj.execute(query, params)
Exception

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

Traceback (most recent call last):
  File "/usr/lib/python3/dist-packages/odoo/http.py", line 652, in _handle_exception
    return super(JsonRequest, self)._handle_exception(exception)
  File "/usr/lib/python3/dist-packages/odoo/http.py", line 317, in _handle_exception
    raise exception.with_traceback(None) from new_cause
psycopg2.errors.InvalidForeignKey: there is no unique constraint matching given keys for referenced table "mail_message"

The fact that it only happens occasionally, even when restoring from the same backup, is quite puzzling, and makes it hard to troubleshoot.

I've come across this forum thread with a person claiming that the root of the problem is Odoo's configuration, but our Odoo configuration has very high limits already, similar to the ones listed as a possible solution.

Has anyone encountered this problem already? How did you solve it?
Thank you for your time.

SirAionTech · 2024-07-10T08:39:57Z

SirAionTech
Jul 10, 2024

👋
Never encountered it, but it looks interesting and I hope I can help :)

What is the module being updated when the exception is raised?
What is the query being executed when it is trying to add a foreign key in

File "/usr/lib/python3/dist-packages/odoo/modules/registry.py", line 499, in check_foreign_keys
sql.add_foreign_key(cr, table1, column1, table2, column2, ondelete)
File "/usr/lib/python3/dist-packages/odoo/tools/sql.py", line 170, in add_foreign_key
cr.execute(query.format(tablename1, columnname1, tablename2, columnname2, ondelete))

?

5 replies

GSLabIt Jul 10, 2024

👋 Never encountered it, but it looks interesting and I hope I can help :)

What is the module being updated when the exception is raised? What is the query being executed when it is trying to add a foreign key in

File "/usr/lib/python3/dist-packages/odoo/modules/registry.py", line 499, in check_foreign_keys
sql.add_foreign_key(cr, table1, column1, table2, column2, ondelete)
File "/usr/lib/python3/dist-packages/odoo/tools/sql.py", line 170, in add_foreign_key
cr.execute(query.format(tablename1, columnname1, tablename2, columnname2, ondelete))

?

that's not strictly dependent on updated module, it is raised even with 2 modules. Also retrying the update for same module, would end successfully.

SirAionTech Jul 10, 2024

I still think it would be helpful to have all the data for at least one time it happened: since a query is failing, it would help to know what the query was.

GSLabIt Jul 10, 2024

that's the query failing

2024-07-10 02:26:03,766 1408183 ERROR collaudo-new odoo.sql_db: bad query: ALTER TABLE "mail_tracking_value" ADD FOREIGN KEY ("mail_message_id") REFERENCES "mail_message"("id") ON DELETE cascade
ERROR: there is no unique constraint matching given keys for referenced table "mail_message"

SirAionTech Jul 10, 2024

that's the query failing

2024-07-10 02:26:03,766 1408183 ERROR collaudo-new odoo.sql_db: bad query: ALTER TABLE "mail_tracking_value" ADD FOREIGN KEY ("mail_message_id") REFERENCES "mail_message"("id") ON DELETE cascade
ERROR: there is no unique constraint matching given keys for referenced table "mail_message"

Thanks!
It looks like mail_message still does not have a unique constraint on id when this is executed, maybe are you using multiple jobs to restore the database?
That might lead to race condition where the constraint is not added yet when the above query is executed.

GSLabIt Jul 10, 2024

Thanks. Nope only one job is used to restore the database, we are restoring from a zip file.

legalsylvain · 2024-07-10T09:05:31Z

So the problem is not over here. sometime if you restore a db of a version X in a server of version Y, you can face some issue...

Agreed, it is some other issue, at the moment we're not able to find out the issue

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Odoo Community Association

Inconsistent issues with restoring an environment #173

{{title}}

{{editor}}'s edit

{{editor}}'s edit

Replies: 2 comments 8 replies

{{title}}

{{editor}}'s edit

{{editor}}'s edit

{{title}}

{{title}}

{{editor}}'s edit

{{editor}}'s edit

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{editor}}'s edit

{{editor}}'s edit

{{title}}

{{title}}

Select a reply

Inconsistent issues with restoring an environment #173

Replies: 2 comments · 8 replies

Replies: 2 comments 8 replies