fix: prevent foreign key constraint error in tag UpdateCounts #4370
Conversation
Added JOIN clause with tag table in UpdateCounts SQL query to filter out tag IDs from JSON that don't exist in the tag table. This prevents 'FOREIGN KEY constraint failed' errors when the library_tag table tries to reference non-existent tag IDs during scanner operations. The fix ensures only valid tag references are counted while maintaining data integrity and preventing scanner failures during library updates.
There was a problem hiding this comment.
Pull Request Overview
This PR fixes a foreign key constraint error in the tag UpdateCounts functionality by adding a JOIN clause to filter out non-existent tag IDs from JSON data before inserting into the library_tag table.
- Added JOIN with tag table to validate tag IDs exist before counting
- Prevents foreign key constraint violations during scanner operations
- Maintains data integrity while allowing scanner to complete successfully
There was a problem hiding this comment.
Summary of Changes
Hello @deluan, I'm Gemini Code Assist1! I'm currently reviewing this pull request and will post my feedback shortly. In the meantime, here's a summary to help you and other reviewers quickly get up to speed!
This pull request resolves a critical data integrity issue that was causing foreign key constraint failures during the update of tag counts within the system's scanner operations. By refining the underlying SQL query, it ensures that only valid and existing tag references are processed, thereby preventing errors and enhancing the overall stability and reliability of data synchronization.
Highlights
- Bug Fix: Foreign Key Constraint Error: I've addressed a bug where the
UpdateCounts()method could cause 'FOREIGN KEY constraint failed' errors. This occurred when attempting to insert records into thelibrary_tagtable with tag IDs that did not exist in thetagtable, particularly during scanner operations. - SQL Query Enhancement: The fix involves modifying the SQL query within
persistence/tag_repository.goto include aJOINclause with thetagtable. Specifically,JOIN tag ON tag.id = jt.valuewas added to theINSERT INTO library_tagstatement. This ensures that only valid tag IDs (those present in thetagtable) are processed, effectively filtering out non-existent references at the database level. - Improved Data Integrity and Scanner Stability: This change prevents future foreign key violations, maintains data integrity by ensuring only valid tag associations are counted, and improves the robustness of scanner operations by allowing them to complete successfully even if some inconsistent tag data might be present.
Using Gemini Code Assist
The full guide for Gemini Code Assist can be found on our documentation page, here are some quick tips.
Invoking Gemini
You can request assistance from Gemini at any point in your pull request via creating an issue comment (i.e. comment on the pull request page) using either /gemini <command> or @gemini-code-assist <command>. Below is a summary of the supported commands.
| Feature | Command | Description |
|---|---|---|
| Code Review | /gemini review |
Performs a code review for the current pull request in its current state. |
| Pull Request Summary | /gemini summary |
Provides a summary of the current pull request in its current state. |
| Comment | @gemini-code-assist | Responds in comments when explicitly tagged, both in issue comments and review comments. |
| Help | /gemini help |
Displays a list of available commands. |
Customization
To customize Gemini Code Assist for GitHub experience, repository maintainers can create a configuration file and/or provide a custom code review style guide (such as PEP-8 for Python) by creating and adding files to a .gemini/ folder in the base of the repository. Detailed instructions can be found here.
Limitations & Feedback
Gemini Code Assist may make mistakes. Please leave feedback on any instances where its feedback is incorrect or counter productive. You can react with 👍 and 👎 on @gemini-code-assist comments or fill out our survey to provide feedback.
You can also get AI-powered code generation, chat, as well as code reviews directly in the IDE at no cost with the Gemini Code Assist IDE Extension.
Footnotes
-
Review the Privacy Notices, Generative AI Prohibited Use Policy, Terms of Service, and learn how to configure Gemini Code Assist in GitHub here. Gemini can make mistakes, so double check it and use code with caution. ↩
There was a problem hiding this comment.
Code Review
This pull request addresses a foreign key constraint error in tag.UpdateCounts by adding a JOIN to the SQL query. The change is correct and effectively filters out tag IDs from JSON data that don't exist in the tag table, preventing the error. My main feedback is to consider adding a regression test to ensure this bug does not reappear in the future.
Add comprehensive regression tests to prevent the foreign key constraint error when tag IDs in JSON data don't exist in the tag table. Tests cover both album and media file scenarios with non-existent tag IDs. - Test UpdateCounts() with albums containing non-existent tag IDs - Test UpdateCounts() with media files containing non-existent tag IDs - Verify operations complete without foreign key errors Signed-off-by: Deluan <deluan@navidrome.org>
5b529aa to
5bddbee
Compare
Description
Fixed foreign key constraint error in tag repository UpdateCounts method by adding JOIN clause to filter out non-existent tag IDs from JSON data before inserting into library_tag table.
Type of Change
Checklist
Please review and check all that apply:
How to Test
make testmake test PKG=./persistencego test -v ./persistence -ginkgo.focus "UpdateCounts"All tests pass with the added JOIN clause ensuring only valid tag references are processed.
Additional Notes
This is a minimal defensive fix that prevents scanner failures when tag IDs in JSON don't exist in the tag table. The solution uses SQL JOIN to filter invalid references at the database level, maintaining data integrity without breaking existing functionality.