FrederikBaerentsen/BrickTracker
5
9
Fork 6
Code Issues 7 Pull Requests 1 Actions Packages Projects 1 Releases 4 Wiki Activity

Bug: "FOREIGN KEY constraint failed." when upgrading database #64

New Issue
Closed
opened 2025-02-10 06:46:19 +01:00 by matthew · 3 comments
matthew commented 2025-02-10 06:46:19 +01:00
Contributor
Copy Link

I just upgraded from 1.1.0 to 1.2.0, and I'm unable to update my database. I get through migration #10 then get the error "FOREIGN KEY constraint failed."

I've attached two copies of my database in a ZIP file:

  • The version before I started my migration (v6)
  • The partially migrated version up through version 10

The docker logs don't have any further information aside from the error message.

I just upgraded from 1.1.0 to 1.2.0, and I'm unable to update my database. I get through migration #10 then get the error "FOREIGN KEY constraint failed." I've attached two copies of my database in a ZIP file: - The version before I started my migration (v6) - The partially migrated version up through version 10 The docker logs don't have any further information aside from the error message.
Archive.zip
160 KiB
👀 1
gregoo commented 2025-02-10 16:16:41 +01:00
Collaborator
Copy Link

Thanks a lot for providing the two copies of your database.
For some reason your inventory contains the parts from the Colosseum (https://rebrickable.com/sets/10276-1/colosseum/) even though it has been deleted.

There is probably a bug somewhere that doesn't delete properly the parts of a set which needs to be fixed, and then another extra step to ignore parts from sets that have been already deleted but have remnants.

I'm looking into it 🙂

Thanks a lot for providing the two copies of your database. For some reason your inventory contains the parts from the Colosseum (https://rebrickable.com/sets/10276-1/colosseum/) even though it has been deleted. There is probably a bug somewhere that doesn't delete properly the parts of a set which needs to be fixed, and then another extra step to ignore parts from sets that have been already deleted but have remnants. I'm looking into it 🙂
gregoo commented 2025-02-10 16:36:38 +01:00
Collaborator
Copy Link

The deletion bug does not seem to exist anymore in the latest code. I'll prepare a workaround for the migration step for version 11.

@matthew in the meantime if you do not wish to wait for the new release, you can manually delete the bogus entries in your database.

If you have direct access to your database file, with:

$ python3 -m sqlite3 <path_to_your_db_file> 'DELETE FROM "inventory" WHERE "inventory"."u_id" NOT IN (SELECT "bricktracker_sets"."id" FROM "bricktracker_sets")'
$ python3 -m sqlite3 <path_to_your_db_file> 'DELETE FROM "missing" WHERE "missing"."u_id" NOT IN (SELECT "bricktracker_sets"."id" FROM "bricktracker_sets")'

or through docker, with:

$ docker exec -i -t <container_name> python3 -m sqlite3 <path_to_your_db_file> 'DELETE FROM "inventory" WHERE "inventory"."u_id" NOT IN (SELECT "bricktracker_sets"."id" FROM "bricktracker_sets")'
$ docker exec -i -t <container_name> python3 -m sqlite3 <path_to_your_db_file> 'DELETE FROM "missing" WHERE "missing"."u_id" NOT IN (SELECT "bricktracker_sets"."id" FROM "bricktracker_sets")'

After that, the database upgrade should go through.

image

(I am obviously missing your sets images since I never added them 😂)

The deletion bug does not seem to exist anymore in the latest code. I'll prepare a workaround for the migration step for version 11. @matthew in the meantime if you do not wish to wait for the new release, you can manually delete the bogus entries in your database. If you have direct access to your database file, with: ```sh $ python3 -m sqlite3 <path_to_your_db_file> 'DELETE FROM "inventory" WHERE "inventory"."u_id" NOT IN (SELECT "bricktracker_sets"."id" FROM "bricktracker_sets")' $ python3 -m sqlite3 <path_to_your_db_file> 'DELETE FROM "missing" WHERE "missing"."u_id" NOT IN (SELECT "bricktracker_sets"."id" FROM "bricktracker_sets")' ``` or through docker, with: ```sh $ docker exec -i -t <container_name> python3 -m sqlite3 <path_to_your_db_file> 'DELETE FROM "inventory" WHERE "inventory"."u_id" NOT IN (SELECT "bricktracker_sets"."id" FROM "bricktracker_sets")' $ docker exec -i -t <container_name> python3 -m sqlite3 <path_to_your_db_file> 'DELETE FROM "missing" WHERE "missing"."u_id" NOT IN (SELECT "bricktracker_sets"."id" FROM "bricktracker_sets")' ``` After that, the database upgrade should go through. ![image](/attachments/1633f5cd-e13c-4456-9ca3-9eed2550431c) (I am obviously missing your sets images since I never added them 😂)
image.png
150 KiB
matthew commented 2025-02-12 04:09:27 +01:00
Author
Contributor
Copy Link

Thanks a lot for providing the two copies of your database.
For some reason your inventory contains the parts from the Colosseum (https://rebrickable.com/sets/10276-1/colosseum/) even though it has been deleted.

There is probably a bug somewhere that doesn't delete properly the parts of a set which needs to be fixed, and then another extra step to ignore parts from sets that have been already deleted but have remnants.

I'm looking into it 🙂

That would explain it! I've been using BrickTracker since 1.0 and I did accidentally add the Colosseum to my list rather than my wishlist.

$ python3 -m sqlite3 <path_to_your_db_file> 'DELETE FROM "inventory" WHERE "inventory"."u_id" NOT IN (SELECT "bricktracker_sets"."id" FROM "bricktracker_sets")'
$ python3 -m sqlite3 <path_to_your_db_file> 'DELETE FROM "missing" WHERE "missing"."u_id" NOT IN (SELECT "bricktracker_sets"."id" FROM "bricktracker_sets")'

That worked, thank you!

> Thanks a lot for providing the two copies of your database. > For some reason your inventory contains the parts from the Colosseum (https://rebrickable.com/sets/10276-1/colosseum/) even though it has been deleted. > > There is probably a bug somewhere that doesn't delete properly the parts of a set which needs to be fixed, and then another extra step to ignore parts from sets that have been already deleted but have remnants. > > I'm looking into it 🙂 That would explain it! I've been using BrickTracker since 1.0 and I did accidentally add the Colosseum to my list rather than my wishlist. > > ```sh > $ python3 -m sqlite3 <path_to_your_db_file> 'DELETE FROM "inventory" WHERE "inventory"."u_id" NOT IN (SELECT "bricktracker_sets"."id" FROM "bricktracker_sets")' > $ python3 -m sqlite3 <path_to_your_db_file> 'DELETE FROM "missing" WHERE "missing"."u_id" NOT IN (SELECT "bricktracker_sets"."id" FROM "bricktracker_sets")' > ``` That worked, thank you!
🎉 1
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
Compat/Breaking
Breaking change that won't be backward compatible
Kind/Bug
Something is not working
Kind/Documentation
Documentation changes
Kind/Enhancement
Improve existing functionality
Kind/Feature
New functionality
Kind/Security
This is security issue
Kind/Testing
Issue or pull request related to testing
Priority
Critical
The priority is critical
Priority
High
The priority is high
Priority
Low
The priority is low
Priority
Medium
The priority is medium
Reviewed
Confirmed
Issue has been confirmed
Reviewed
Duplicate
This issue or pull request already exists
Reviewed
Invalid
Invalid issue
Reviewed
Won't Fix
This issue won't be fixed
Status
Abandoned
Somebody has started to work on this but abandoned work
Status
Blocked
Something is blocking this issue or pull request
Status
Need More Info
Feedback is required to reproduce issue or to continue work
Unraid
Unsupported/Can't help
No Label
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
No Assignees
2 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: FrederikBaerentsen/BrickTracker#64
No description provided.
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?