Open Data food import fails on existing space

[JohnMoor]

Tandoor Version

1.5.14

Setup

Docker / Docker-Compose

Reverse Proxy

No reverse proxy

Other

via Home assistant plugin

Bug description

Importing NL Food items under "Space Settings" of existing space fails. With a new space it works fine. Also Unit, Category, Property, Store and Conversion import went well.

How can I troubleshoot on which item it gets stuck?

Relevant logs

comparing Extra harde tofu failed because field name is not equal (Extra harde tofu != Extra-Firm Tofu)
comparing Tomaat failed because field name is not equal (Tomaat != Tomato)
comparing Druif Tomaat failed because field name is not equal (Druif Tomaat != Grape Tomato)
comparing Cherry tomaat failed because field name is not equal (Cherry tomaat != Cherry Tomato)
comparing Koolraap failed because field name is not equal (Koolraap != Turnip)
comparing Amandel failed because field name is not equal (Amandel != Almond)
comparing Vanille-extract failed because field name is not equal (Vanille-extract != Vanilla extract)
comparing Vanille ijs failed because field name is not equal (Vanille ijs != Vanilla Ice Cream)
comparing Groente bouillon failed because field name is not equal (Groente bouillon != Vegetable Broth)
comparing Waterkers failed because field name is not equal (Waterkers != Watercress)
comparing Watermeloen failed because field name is not equal (Watermeloen != Watermelon)
comparing Witte Wijn failed because field name is not equal (Witte Wijn != White Wine)
comparing Rode Wijn failed because field name is not equal (Rode Wijn != Red Wine)
comparing Rijstwijn failed because field name is not equal (Rijstwijn != Rice Wine)
comparing Worcestershiresaus failed because field name is not equal (Worcestershiresaus != Worcestershire Sauce)
comparing hennepzaad failed because field name is not equal (hennepzaad != hemp seeds)
comparing Eigeel failed because field name is not equal (Eigeel != Egg Yolk)
comparing Eiwit failed because field name is not equal (Eiwit != Egg White)
comparing Voedingsgistvlokken failed because field name is not equal (Voedingsgistvlokken != Nutritional yeast flakes)
comparing Kappertjes in blik failed because field name is not equal (Kappertjes in blik != Capers canned)
comparing Kardemom failed because field name is not equal (Kardemom != Cardamom)
comparing Knoflookpoeder failed because field name is not equal (Knoflookpoeder != Garlic powder)
comparing Laurierblad failed because field name is not equal (Laurierblad != Bay leaf)
comparing Amandelen geblancheerd failed because field name is not equal (Amandelen geblancheerd != Almonds blanched)
comparing Mangonectar in blik failed because field name is not equal (Mangonectar in blik != Mango nectar canned)
comparing Gekiemde mungbonen failed because field name is not equal (Gekiemde mungbonen != Mung beans sprouted)
comparing Xanthaangom failed because field name is not equal (Xanthaangom != Xanthan Gum)
comparing Arachideolie (pindaolie) failed because field name is not equal (Arachideolie (pindaolie) != Peanut Oil)
ERROR:django.request:Internal Server Error: /api-import-open-data/
Traceback (most recent call last):
  File "/opt/recipes/venv/lib/python3.12/site-packages/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/recipes/venv/lib/python3.12/site-packages/django/db/backends/sqlite3/base.py", line 328, in execute
    return super().execute(query, params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
sqlite3.IntegrityError: UNIQUE constraint failed: cookbook_food.space_id, cookbook_food.name

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

Traceback (most recent call last):
  File "/opt/recipes/venv/lib/python3.12/site-packages/django/core/handlers/exception.py", line 55, in inner
    response = get_response(request)
               ^^^^^^^^^^^^^^^^^^^^^
  File "/opt/recipes/venv/lib/python3.12/site-packages/django/core/handlers/base.py", line 197, in _get_response
    response = wrapped_callback(request, *callback_args, **callback_kwargs)
               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/recipes/venv/lib/python3.12/site-packages/django/views/decorators/csrf.py", line 56, in wrapper_view
    return view_func(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/recipes/venv/lib/python3.12/site-packages/django/views/generic/base.py", line 104, in view
    return self.dispatch(request, *args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/recipes/venv/lib/python3.12/site-packages/rest_framework/views.py", line 509, in dispatch
    response = self.handle_exception(exc)
               ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/recipes/venv/lib/python3.12/site-packages/rest_framework/views.py", line 469, in handle_exception
    self.raise_uncaught_exception(exc)
  File "/opt/recipes/venv/lib/python3.12/site-packages/rest_framework/views.py", line 480, in raise_uncaught_exception
    raise exc
  File "/opt/recipes/venv/lib/python3.12/site-packages/rest_framework/views.py", line 506, in dispatch
    response = handler(request, *args, **kwargs)
               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/recipes/cookbook/views/api.py", line 1589, in post
    response_obj['food'] = data_importer.import_food().to_dict()
                           ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/recipes/cookbook/helper/open_data_importer.py", line 370, in import_food
    model_type.objects.bulk_update(update_list, field_list)
  File "/opt/recipes/venv/lib/python3.12/site-packages/django/db/models/manager.py", line 87, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/recipes/venv/lib/python3.12/site-packages/django/db/models/query.py", line 892, in bulk_update
    rows_updated += queryset.filter(pk__in=pks).update(**update_kwargs)
                    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/recipes/venv/lib/python3.12/site-packages/django/db/models/query.py", line 1206, in update
    rows = query.get_compiler(self.db).execute_sql(CURSOR)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/recipes/venv/lib/python3.12/site-packages/django/db/models/sql/compiler.py", line 1984, in execute_sql
    cursor = super().execute_sql(result_type)
::ffff:172.30.32.1 - - [09/Mar/2024:09:11:06 +0100] "POST /api-import-open-data/ HTTP/1.1" 500 145 "https://tandoor.mydomain.com/space-manage/1" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/122.0.0.0 Safari/537.36"
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/recipes/venv/lib/python3.12/site-packages/django/db/models/sql/compiler.py", line 1562, in execute_sql
    cursor.execute(sql, params)
  File "/opt/recipes/venv/lib/python3.12/site-packages/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/recipes/venv/lib/python3.12/site-packages/django/db/backends/utils.py", line 80, in _execute_with_wrappers
    return executor(sql, params, many, context)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/recipes/venv/lib/python3.12/site-packages/django/db/backends/utils.py", line 84, in _execute
    with self.db.wrap_database_errors:
  File "/opt/recipes/venv/lib/python3.12/site-packages/django/db/utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/opt/recipes/venv/lib/python3.12/site-packages/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/recipes/venv/lib/python3.12/site-packages/django/db/backends/sqlite3/base.py", line 328, in execute
    return super().execute(query, params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
django.db.utils.IntegrityError: UNIQUE constraint failed: cookbook_food.space_id, cookbook_food.name

[vabene1111]

strange, I though I had all of these edge cases worked out 🤔

can you try importing another language and then switching back to the one you want. Otherwise I have to add some debug code because I dont think there is any currently.

[vabene1111]

I added some debug output to the next version

[JohnMoor]

strange, I though I had all of these edge cases worked out 🤔

can you try importing another language and then switching back to the one you want. Otherwise I have to add some debug code because I dont think there is any currently.

I tried this, other languages go fine. When switching back to NL it fails again.

[JohnMoor]

I think one of my food items may be corrupted. I just tried to scroll down the food list and it gets stuck down Celeriac and won't go any further

Logs show. Could it be that Cherry is currupted?

::ffff:172.30.32.1 - - [09/Mar/2024:15:34:37 +0100] "GET /api/food/?query=&root=0&page=1&page_size=25&extended=1 HTTP/1.1" 200 20794 "https://tandoor.mydomain.com/list/food/" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/122.0.0.0 Safari/537.36"
::ffff:172.30.32.1 - - [09/Mar/2024:15:34:38 +0100] "GET /media/recipes/fab056db-5059-4762-80ed-51cd5cda4b3d_4.jpg HTTP/1.1" 200 0 "https://tandoor.mydomain.com/list/food/" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/122.0.0.0 Safari/537.36"
::ffff:172.30.32.1 - - [09/Mar/2024:15:34:57 +0100] "GET /api/food/?query=&root=0&page=2&page_size=25&extended=1 HTTP/1.1" 200 21456 "https://tandoor.mydomain.com/list/food/" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/122.0.0.0 Safari/537.36"
::ffff:172.30.32.1 - - [09/Mar/2024:15:34:58 +0100] "GET /media/recipes/c6cc22e7-9210-4554-b335-4c468b3bd18e_13.jpg HTTP/1.1" 200 0 "https://tandoor.mydomain.com/list/food/" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/122.0.0.0 Safari/537.36"
::ffff:172.30.32.1 - - [09/Mar/2024:15:35:01 +0100] "GET /api/food/?query=&root=0&page=3&page_size=25&extended=1 HTTP/1.1" 200 17298 "https://tandoor.mydomain.com/list/food/" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/122.0.0.0 Safari/537.36"
::ffff:172.30.32.1 - - [09/Mar/2024:15:35:02 +0100] "GET /media/recipes/d60a6e69-71e6-48f5-8dc1-dd72688da87d_101.jpg HTTP/1.1" 304 0 "https://tandoor.mydomain.com/list/food/" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/122.0.0.0 Safari/537.36"
::ffff:172.30.32.1 - - [09/Mar/2024:15:35:02 +0100] "GET /media/recipes/6b8019c3-e3a3-4413-8686-4f2ea848496b_20.jpeg HTTP/1.1" 200 0 "https://tandoor.mydomain.com/list/food/" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/122.0.0.0 Safari/537.36"
::ffff:172.30.32.1 - - [09/Mar/2024:15:35:04 +0100] "GET /media/recipes/2b21e1b8-ec4e-492e-9b40-ed11ddc032e6_76.jpg HTTP/1.1" 304 0 "https://tandoor.mydomain.com/list/food/" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/122.0.0.0 Safari/537.36"
::ffff:172.30.32.1 - - [09/Mar/2024:15:35:06 +0100] "GET /api/food/?query=&root=0&page=4&page_size=25&extended=1 HTTP/1.1" 200 24491 "https://tandoor.mydomain.com/list/food/" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/122.0.0.0 Safari/537.36"
ERROR:django.request:Internal Server Error: /api/food/
Traceback (most recent call last):
  File "/opt/recipes/venv/lib/python3.12/site-packages/django/core/handlers/exception.py", line 55, in inner
    response = get_response(request)
               ^^^^^^^^^^^^^^^^^^^^^
  File "/opt/recipes/venv/lib/python3.12/site-packages/django/core/handlers/base.py", line 197, in _get_response
    response = wrapped_callback(request, *callback_args, **callback_kwargs)
               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/recipes/venv/lib/python3.12/site-packages/django/views/decorators/csrf.py", line 56, in wrapper_view
    return view_func(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/recipes/venv/lib/python3.12/site-packages/rest_framework/viewsets.py", line 125, in view
    return self.dispatch(request, *args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/recipes/venv/lib/python3.12/site-packages/rest_framework/views.py", line 509, in dispatch
    response = self.handle_exception(exc)
               ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/recipes/venv/lib/python3.12/site-packages/rest_framework/views.py", line 469, in handle_exception
    self.raise_uncaught_exception(exc)
  File "/opt/recipes/venv/lib/python3.12/site-packages/rest_framework/views.py", line 480, in raise_uncaught_exception
    raise exc
  File "/opt/recipes/venv/lib/python3.12/site-packages/rest_framework/views.py", line 506, in dispatch
    response = handler(request, *args, **kwargs)
               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/recipes/venv/lib/python3.12/site-packages/rest_framework/mixins.py", line 43, in list
    return self.get_paginated_response(serializer.data)
                                       ^^^^^^^^^^^^^^^
  File "/opt/recipes/venv/lib/python3.12/site-packages/rest_framework/serializers.py", line 768, in data
    ret = super().data
          ^^^^^^^^^^^^
  File "/opt/recipes/venv/lib/python3.12/site-packages/rest_framework/serializers.py", line 253, in data
    self._data = self.to_representation(self.instance)
                 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/recipes/venv/lib/python3.12/site-packages/rest_framework/serializers.py", line 687, in to_representation
    self.child.to_representation(item) for item in iterable
    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/recipes/venv/lib/python3.12/site-packages/rest_framework/serializers.py", line 522, in to_representation
    ret[field.field_name] = field.to_representation(attribute)
                            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/recipes/venv/lib/python3.12/site-packages/rest_framework/fields.py", line 915, in to_representation
    return int(value)
           ^^^^^^^^^^
ValueError: invalid literal for int() with base 10: '2345232-cherry'
::ffff:172.30.32.1 - - [09/Mar/2024:15:35:11 +0100] "GET /api/food/?query=&root=0&page=5&page_size=25&extended=1 HTTP/1.1" 500 145 "https://tandoor.mydomain.com/list/food/" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/122.0.0.0 Safari/537.36"

[vabene1111]

Ah that is interesting. I have no idea how that got trough the migrations. Can you check the system page if all migrations were successfull?

[JohnMoor]

I don't see anything strange here:

[vabene1111]

ok thanks, maybe its during import, I will take a look but some other things are first in line.