[v1.0.0b] - Unable to create new or update receipes - Recipe already exists

[alexrasmussendk]

First Check

• This is not a feature request
• I added a very descriptive title to this issue.
• I used the GitHub search to find a similar issue and didn't find it.
• I searched the Mealie documentation, with the integrated search.
• I already read the docs and didn't find an answer.

What is the issue you are experiencing?

I have added 114 recipes in Mealie and now I can no longer add new ones or change my existing recipes.

It fails with the error message: Recipe already exists in Mealie Frontend even if neither the recipe nor the recipe name already exists.

In Docker logs for Mealie API it fails with the error message: SQL Integrity Error on recipe controller action

I have tried to restart the Docker containers and download the Docker images again without any luck.

Deployment

Docker (Linux)

Deployment Details

Deployed with Docker Compose file:

mealie-frontend:
image: hkotel/mealie:frontend-nightly
mealie-api:
image: hkotel/mealie:api-nightly
postgres:
image: postgres

[reverendj1]

Did you disable superuser for the mealie pg user after enabling it? #1500 I just ran into the same issue after a fresh import, and it seems that disabling superuser fixed it.

Nevermind. Looks like it was a fluke that one worked...

[ZeroGravitas23]

I am having the same issue and it's a completely breaking bug for 1.0.0 beta 4 as well as 1.0.0 beta 5 when using postgres as a db. I've tested this on both and are running into the issue with both.

Along with "SQL Integrity Error on recipe controller action" coming from the api, looking into the postgres database it is finding a duplicate recipe ingredient when creating or importing a recipe:

2023-02-09 21:00:00.345 UTC [36] ERROR: duplicate key value violates unique constraint "recipes_ingredients_pkey" 2023-02-09 21:00:00.345 UTC [36] DETAIL: Key (id)=(162) already exists. 2023-02-09 21:00:00.345 UTC [36] STATEMENT: INSERT INTO recipes_ingredients (created_at, update_at, position, recipe_id, title, note, unit_id, food_id, quantity, original_text, reference_id) VALUES ('2023-02-09T15:00:00.345255'::timestamp, '2023-02-09T15:00:00.345265'::timestamp, 0, '744f734b-f7f9-44a6-b0e3-5c843514469d'::uuid, NULL, '1 Cup Flour', NULL, NULL, 1, NULL, 'ba0126f7-5167-410f-a0be-5623f961a7d8'::uuid) RETURNING recipes_ingredients.id

@alexrasmussendk - can you look into the postgres logs and see if you are having the same error?

[hay-kot]

Possibly related to?

• [v1.0.0b] - Restoring a Backup in Postgres does not correctly set DB sequence counters #2051

[ZeroGravitas23]

Thanks @hay-kot for that relation.

I attempted the migrations listed by nkringle and those did fix the issue in my v1.0.0b4. I am pretty sure it will work for Beta 5/nightly as well.

A couple of those SETVAL commands didn't work on certain tables (ingredient_food_extras, shopping_list_extras, and shopping_list_item_extras) due to my instance not utilizing those parts of the app yet.

Is this something that can be corrected on the frontend or api? Otherwise I'm not sure if this is a migration issue from one version to the next using Postgres as a backend.

[hay-kot]

Otherwise I'm not sure if this is a migration issue from one version to the next using Postgres as a backend.

This should only happen for backup/restore and not regular updates to the container. I believe we could automatically perform the SETVAL operations during the migration process so this would happen without user intervention.

[ZeroGravitas23]

I do think it would be a good idea to have the operations run automatically during the migration to ensure there are no issues from the backup and restore. At least for Postrgres backends. I don't know if this issue would exist or need those same migrations with sqlite.

[alexrasmussendk]

@alexrasmussendk - can you look into the postgres logs and see if you are having the same error?

@ZeroGravitas23 - Yes, I have the exactly the same error messages in the PostgreSQL Database container logs.

2023-02-13 18:38:06.566 UTC [2341] ERROR: duplicate key value violates unique constraint "recipes_ingredients_pkey" 2023-02-13 18:38:06.566 UTC [2341] DETAIL: Key (id)=(164) already exists. 2023-02-13 18:38:06.566 UTC [2341] STATEMENT: INSERT INTO recipes_ingredients (position, recipe_id, title, note, unit_id, food_id, quantity, original_text, reference_id, created_at, update_at) VALUES (0, 'bf925679-ff02-4e4f-9fc0-ea325fbc7c1d'::UUID, NULL, '1 Cup Flour', NULL::UUID, NULL::UUID, 1, NULL, 'b76f8fca-ef32-4b4c-b0ff-a09e53d6c0f0'::UUID, '2023-02-13T19:38:06.565696'::timestamp, '2023-02-13T19:38:06.565703'::timestamp) RETURNING recipes_ingredients.id 2023-02-13 18:38:06.571 UTC [2336] ERROR: duplicate key value violates unique constraint "recipes_ingredients_pkey"

[ZeroGravitas23]

@alexrasmussendk If you run the database commands that nkringle lists in the related issue below, it will solve solve your problems for now. These commands need to be run in the database container.

Possibly related to?

• [v1.0.0b] - Restoring a Backup in Postgres does not correctly set DB sequence counters #2051

[alexrasmussendk]

@alexrasmussendk If you run the database commands that nkringle lists in the related issue below, it will solve your problems for now. These commands need to be run in the database container.

Possibly related to?

• [v1.0.0b] - Restoring a Backup in Postgres does not correctly set DB sequence counters #2051

Thanks @ZeroGravitas23

The workaround from @nkringle worked!

I can now create new and update existing receipes.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.