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.

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

[BUG] - Backup on SQLite, restore on postgres (same nightly version). restore hangs, and 500s. #2499

Closed
5 of 6 tasks
braddillon opened this issue Aug 14, 2023 · 1 comment · Fixed by #2986
Closed
5 of 6 tasks
Labels
bug Something isn't working triage

Comments

@braddillon
Copy link

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.
  • This issue can be replicated on the demo site (https://demo.mealie.io/)

What is the issue you are experiencing?

Trying out postgres docker-compose. Made sure i'm on latest nightly mealie for both sqlite and postgres. Did a backup on sqlite, and restore on postgres. Mealie complains about foreign key violations, and 500 hangs (see screenshot).

Based on advise from GameCrazy, I bypassed the issue by editing the backup file directly. I deleted entries in json file for:
cookbooks_to_categories
plan_rules_to_categories

For GameCrazy it was same issue, but different keys. His were shopping list items.

Once I deleted bad foreign key relationships, backup worked as expected.
image

Steps to Reproduce

  1. Export sqllite mealie backup database
  2. Restore backup into postgres mealie installation.

Please provide relevent logs

mealie-api | INFO: 172.20.0.4:34346 - "GET /api/admin/backups HTTP/1.1" 200 OK
mealie-api | INFO: 13-Aug-23 20:38:31 initially backup restore
mealie-api | INFO: 13-Aug-23 20:38:32 dropping all database tables
mealie-api | INFO: 13-Aug-23 20:38:32 importing database tables
mealie-api | INFO: 13-Aug-23 20:38:32 Context impl PostgresqlImpl.
mealie-api | INFO: 13-Aug-23 20:38:32 Will assume transactional DDL.
mealie-api | INFO: 13-Aug-23 20:38:32 Running upgrade -> 6b0f5f32d602, Initial tables
mealie-api | INFO: 13-Aug-23 20:38:32 Running upgrade 6b0f5f32d602 -> 263dd6707191, convert quantity from integer to float
mealie-api | INFO: 13-Aug-23 20:38:32 Running upgrade 263dd6707191 -> f1a2dbee5fe9, Add original_text column to recipes_ingredients
mealie-api | INFO: 13-Aug-23 20:38:32 Running upgrade f1a2dbee5fe9 -> 59eb59135381, add tags to cookbooks
mealie-api | INFO: 13-Aug-23 20:38:32 Running upgrade 59eb59135381 -> 09dfc897ad62, add require_all for cookbook filters
mealie-api | INFO: 13-Aug-23 20:38:32 Running upgrade 09dfc897ad62 -> ab0bae02578f, Add use_abbreviation column to ingredients
mealie-api | INFO: 13-Aug-23 20:38:32 Running upgrade ab0bae02578f -> f30cf048c228, add new webhook fields
mealie-api | INFO: 13-Aug-23 20:38:32 Running upgrade f30cf048c228 -> 188374910655, add login_attemps and locked_at field to user table
mealie-api | INFO: 13-Aug-23 20:38:32 Running upgrade 188374910655 -> 089bfa50d0ed, Add is_ocr_recipe column to recipes
mealie-api | INFO: 13-Aug-23 20:38:32 Running upgrade 089bfa50d0ed -> 44e8d670719d, add extras to shopping lists, list items, and ingredient foods
mealie-api | INFO: 13-Aug-23 20:38:32 Running upgrade 44e8d670719d -> 2ea7a807915c, add recipe_timeline_events table
mealie-api | INFO: 13-Aug-23 20:38:32 Running upgrade 2ea7a807915c -> 1923519381ad, added recipe last made timestamp
mealie-api | INFO: 13-Aug-23 20:38:32 Running upgrade 1923519381ad -> 167eb69066ad, add recipe_scale to shopping list item ref
mealie-api | INFO: 13-Aug-23 20:38:32 Running upgrade 167eb69066ad -> 165d943c64ee, add related user to mealplan
mealie-api | INFO: 13-Aug-23 20:38:32 Running upgrade 165d943c64ee -> ff5f73b01a7a, add missing foreign key and order indices
mealie-api | INFO: 13-Aug-23 20:38:33 Running upgrade ff5f73b01a7a -> 16160bf731a0, add more indices necessary for search
mealie-api | INFO: 13-Aug-23 20:38:33 Running upgrade 16160bf731a0 -> 5ab195a474eb, add normalized search properties
mealie-api | INFO: 13-Aug-23 20:38:33 Running upgrade 5ab195a474eb -> b04a08da2108, added shopping list label settings
mealie-api | INFO: 13-Aug-23 20:38:33 Running upgrade b04a08da2108 -> 38514b39a824, add auth_method to user table
mealie-api | INFO: 13-Aug-23 20:38:33 Running upgrade 38514b39a824 -> b3dbb554ba53, postgres fuzzy search
postgres | 2023-08-14 00:38:33.525 UTC [82] ERROR: insert or update on table "plan_rules_to_categories" violates foreign key constraint "plan_rules_to_categories_category_id_fkey"
postgres | 2023-08-14 00:38:33.525 UTC [82] DETAIL: Key (category_id)=(6eb032b0-b3c1-46ca-9a57-ccf06fbdc00d) is not present in table "categories".
postgres | 2023-08-14 00:38:33.525 UTC [82] STATEMENT: INSERT INTO plan_rules_to_categories (group_plan_rule_id, category_id) VALUES ('99f524cedc12478ba666845dbb329250'::UUID, '44223e2c1ddc49fb9e72d736d9ca3797'::UUID), ('6c7eb4e6e92b4bb1801c9aced51de617'::UUID, '6eb032b0b3c146ca9a57ccf06fbdc00d'::UUID)
mealie-api | INFO: 172.20.0.4:34362 - "POST /api/admin/backups/mealie_edit.zip/restore HTTP/1.1" 500 Internal Server Error
mealie-api | INFO: 127.0.0.1:48434 - "GET /api/app/about HTTP/1.1" 200 OK

Mealie Version

Nightly, build: e55258c

Deployment

Docker (Linux)

Additional Deployment Details

No response

@braddillon braddillon added bug Something isn't working triage labels Aug 14, 2023
@michael-genson
Copy link
Collaborator

Can confirm this happened to me too with shopping list items. I had extras and recipe refs that couldn't find the related shopping list item

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Something isn't working triage
Projects
None yet
Development

Successfully merging a pull request may close this issue.

2 participants