Fix 500 error when loading platforms with ROMs whose filenames start with region tags by Copilot · Pull Request #3091 · rommapp/romm

Copilot · 2026-03-08T22:45:16Z

ROMs with filenames starting with region/format tags (e.g., (Japan) Sonic Jam.iso) produce an empty fs_name_no_tags. Migration 0069 introduced fs_name_no_tags matching to the sibling_roms view without guarding against empty strings — causing "" = "" to match every such ROM against every other on the same platform, creating O(N²) sibling rows and query timeouts. The same empty string also poisoned the group_by_meta_id grouping key, collapsing all unmatched tag-prefixed ROMs into a single group.

Changes

0071_sibling_roms_nonempty_fs_name migration — adds AND r1.fs_name_no_tags != '' to the sibling_roms view join condition, so empty strings no longer produce a cross-product of false siblings:
```
-- before
OR (r1.fs_name_no_tags = r2.fs_name_no_tags)
-- after
OR (r1.fs_name_no_tags = r2.fs_name_no_tags AND r1.fs_name_no_tags != '')
```
filter_roms / group_by_meta_id — wraps fs_name_no_tags in NULLIF(..., '') when building the coalesce partition key, so empty-named ROMs fall through to their unique romm-<platform_id>-<id> fallback key instead of all sharing fs-<platform_id>-:
```
_create_metadata_id_case(
    "fs",
    func.nullif(base_subquery.c.fs_name_no_tags, ""),
    base_subquery.c.platform_id,
),
```
get_roms_scalar — passes group_by_meta_id through from **kwargs to filter_roms.
Tests — three new cases covering: empty fs_name_no_tags not producing siblings, non-empty matching names producing siblings, and group_by_meta_id returning all ROMs (not just 1) when all have empty fs_name_no_tags.

Original prompt

This section details on the original issue you should resolve

<issue_title>[Bug] 200: AxiosError: Request failed with status code 500</issue_title>
<issue_description>RomM version
4.6.1

Describe the bug
When editing the config.yaml I added both saturn and saturnjp folders to platform saturn. After the scan completed I now get "Couldn't fetch roms for platform ID 200: AxiosError: Request failed with status code 500" on the saturn platform.

To Reproduce
Steps to reproduce the behavior:

Add saturn and saturnjp and assigned saturn as a platform on both entries.

Restart or provision new pods if you're on kubernetes.

Click on Platforms > saturn

Watch the top right purple banner with error message "Couldn't fetch roms for platform ID 200: AxiosError: Request failed with status code 500"

Expected behavior
Be able to list/view all saturn games.

Screenshots

Server (please complete the following information):

kubeadm + argocd using image rommapp/romm:4.6.1

Client (please complete the following information):

Firefox and other blink engine based browsers.

Additional context
Want to make sure this is not the same as #2579
If it's the same fix, then I'll go ahead and run it on my end.
</issue_description>

Comments on the Issue (you are @copilot in this section)
@gantoine I need container logs plz

Fixes [Bug] 200: AxiosError: Request failed with status code 500 #3065

💡 You can make Copilot smarter by setting up custom instructions, customizing its development environment and configuring Model Context Protocol (MCP) servers. Learn more Copilot coding agent tips in the docs.

…g and incorrect ROM grouping - Add migration 0071 to fix sibling_roms view: add guard against empty string matching for fs_name_no_tags - Fix group_by_meta_id in filter_roms: use func.nullif to treat empty fs_name_no_tags as NULL in grouping key - Add group_by_meta_id support to get_roms_scalar - Add tests for sibling matching behavior with empty/non-empty fs_name_no_tags Co-authored-by: gantoine <[email protected]>

github-actions · 2026-03-09T00:05:14Z

Test Results (postgresql)

945 tests +3 944 ✅ +3 2m 18s ⏱️ -2s
1 suites ±0 1 💤 ±0
1 files ±0 0 ❌ ±0

Results for commit e3d9bfe. ± Comparison against base commit 38b311d.

♻️ This comment has been updated with latest results.

github-actions · 2026-03-09T00:05:14Z

Test Results (mariadb)

945 tests +3 944 ✅ +3 2m 19s ⏱️ +3s
1 suites ±0 1 💤 ±0
1 files ±0 0 ❌ ±0

Results for commit e3d9bfe. ± Comparison against base commit 38b311d.

♻️ This comment has been updated with latest results.

greptile-apps · 2026-03-09T00:07:26Z

Greptile Summary

This PR fixes a 500 error triggered when loading platforms that contain ROMs whose filenames start with region/format tags (e.g., (Japan) Sonic Jam.iso), which causes fs_name_no_tags to be an empty string. The root cause was that migration 0069 introduced fs_name_no_tags matching into the sibling_roms view without guarding against empty strings — causing '' = '' to match every such ROM against every other on the same platform, producing an O(N²) cross-product of sibling rows and ultimately a query timeout. A second bug caused all these ROMs to collapse into a single group under group_by_meta_id because COALESCE resolved to "fs-<platform_id>-" instead of the unique "romm-<platform_id>-<id>" fallback.

Key changes:

0071_sibling_roms_fs_name migration: Adds AND r1.fs_name_no_tags != '' to the sibling_roms view join condition, preventing empty strings from producing a cross-product of false sibling matches.
roms_handler.py — group_by_meta_id: Wraps fs_name_no_tags in func.nullif(..., "") so empty-named ROMs fall through to their unique romm-<platform_id>-<id> partition key instead of being collapsed into one group.
roms_handler.py — get_roms_scalar: Fixes a missing group_by_meta_id kwarg forwarding — the parameter was accepted by the function signature but never passed through to filter_roms.
Type hint: KeyedColumnElement → ColumnElement is a necessary correction because func.nullif() returns a generic ColumnElement, not the more specific KeyedColumnElement.
Tests: Three new test cases validate the fix end-to-end for empty sibling exclusion, non-empty sibling matching, and group_by_meta_id deduplication.

Confidence Score: 5/5

This PR is safe to merge — it is a targeted, well-tested fix for a production-impacting 500 error with correct upgrade/downgrade paths.
All three fixes (SQL view guard, NULLIF in Python grouping, kwarg forwarding) are minimal and independently correct. The migration has a proper downgrade. NULL values are already handled by SQL's three-valued logic so no additional IS NOT NULL guard is needed. The only minor concern is a hard-coded row-count assertion in one test that could be fragile depending on fixture teardown, but this does not affect production correctness.
No files require special attention. The test assertion at line 278 of backend/tests/handler/test_db_handler.py is worth a second look for isolation robustness.

Important Files Changed

Filename	Overview
backend/alembic/versions/0071_sibling_roms_fs_name.py	Adds `AND r1.fs_name_no_tags != ''` guard to the `sibling_roms` view, preventing empty-string cross-product matches. Upgrade/downgrade both look correct; `NULL` values are already handled by SQL's three-valued logic so no extra IS NOT NULL guard is needed.
backend/handler/database/roms_handler.py	Two fixes: wraps `fs_name_no_tags` in `func.nullif(..., "")` so empty strings fall through to the unique `romm-<id>` partition key in `group_by_meta_id`, and forwards the missing `group_by_meta_id` kwarg in `get_roms_scalar`. The `KeyedColumnElement` → `ColumnElement` type update is required because `func.nullif()` returns a more generic `ColumnElement`.
backend/tests/handler/test_db_handler.py	Three new tests covering empty `fs_name_no_tags` not producing siblings, non-empty matching names producing siblings, and `group_by_meta_id` returning all ROMs instead of one when all have empty `fs_name_no_tags`. Test isolation depends on DB fixture teardown — `test_group_by_meta_id_with_empty_fs_name_no_tags` asserts exactly 3 ROMs which could be fragile if platform state leaks from preceding tests.

Flowchart

%%{init: {'theme': 'neutral'}}%%
flowchart TD
    A["ROM filename\ne.g. (Japan) Sonic Jam.iso"] --> B{"fs_name_no_tags\nvalue?"}
    B -- "empty string ''" --> C["NULLIF returns NULL"]
    B -- "non-empty\ne.g. 'Sonic Jam'" --> D["NULLIF returns value"]

    C --> E["_create_metadata_id_case: id_column IS NULL\n→ returns NULL (no key)"]
    D --> F["_create_metadata_id_case: id_column IS NOT NULL\n→ returns 'fs-platformId-Sonic Jam'"]

    E --> G["COALESCE falls through\nto romm-platformId-romId (unique)"]
    F --> H["All ROMs with same fs_name_no_tags\ngrouped together correctly"]

    G --> I["group_by_meta_id:\neach ROM gets its own group ✓"]
    H --> J["group_by_meta_id:\nregion variants grouped correctly ✓"]

    subgraph "sibling_roms VIEW fix"
        K["r1.fs_name_no_tags = r2.fs_name_no_tags\nAND r1.fs_name_no_tags != ''"]
        L["'' = '' was TRUE\n→ O(N²) false siblings"]
        M["Now guarded: '' skipped\n→ no cross-product match"]
        L --> M
    end

_{Last reviewed commit: e3d9bfe}

backend/tests/handler/test_db_handler.py

github-actions · 2026-03-09T00:17:48Z

☂️ Python Coverage

current status: ✅

Overall Coverage

Lines	Covered	Coverage	Threshold	Status
13807	9263	67%	0%	🟢

New Files

No new covered files...

Modified Files

File	Coverage	Status
backend/handler/database/roms_handler.py	55%	🟢
TOTAL	55%	🟢

updated for commit: e3d9bfe by action🐍

Initial plan

531d001

Copilot AI assigned Copilot and gantoine Mar 8, 2026

Copilot started work on behalf of gantoine March 8, 2026 22:45 View session

Copilot AI changed the title ~~[WIP] Fix AxiosError for Saturn platform game listing~~ Fix 500 error when loading platforms with ROMs whose filenames start with region tags Mar 8, 2026

Copilot finished work on behalf of gantoine March 8, 2026 23:18

gantoine approved these changes Mar 9, 2026

View reviewed changes

gantoine marked this pull request as ready for review March 9, 2026 00:04

fix migration

e3d9bfe

greptile-apps bot reviewed Mar 9, 2026

View reviewed changes

backend/tests/handler/test_db_handler.py Show resolved Hide resolved

gantoine merged commit 4d54673 into master Mar 9, 2026
13 checks passed

gantoine deleted the copilot/fix-saturn-platform-error branch March 9, 2026 00:26

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

Fix 500 error when loading platforms with ROMs whose filenames start with region tags#3091

Fix 500 error when loading platforms with ROMs whose filenames start with region tags#3091
gantoine merged 3 commits intomasterfrom
copilot/fix-saturn-platform-error

Copilot AI commented Mar 8, 2026 •

edited

Loading

Uh oh!

github-actions bot commented Mar 9, 2026 •

edited

Loading

Uh oh!

github-actions bot commented Mar 9, 2026 •

edited

Loading

Uh oh!

greptile-apps bot commented Mar 9, 2026 •

edited

Loading

Uh oh!

Uh oh!

github-actions bot commented Mar 9, 2026

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

2 participants

Uh oh!

Conversation

Copilot AI commented Mar 8, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Changes

Comments on the Issue (you are @copilot in this section)

Uh oh!

github-actions bot commented Mar 9, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Test Results (postgresql)

Uh oh!

github-actions bot commented Mar 9, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Test Results (mariadb)

Uh oh!

greptile-apps bot commented Mar 9, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Greptile Summary

Confidence Score: 5/5

Important Files Changed

Flowchart

Uh oh!

Uh oh!

github-actions bot commented Mar 9, 2026

☂️ Python Coverage

Overall Coverage

New Files

Modified Files

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

2 participants

Copilot AI commented Mar 8, 2026 •

edited

Loading

github-actions bot commented Mar 9, 2026 •

edited

Loading

github-actions bot commented Mar 9, 2026 •

edited

Loading

greptile-apps bot commented Mar 9, 2026 •

edited

Loading