Quick update for anyone else who has come across this problem and wants to repair albums with missing track / disc information. Songs in collections that have duplicated guids can be found using the following SQL query. For Linux the database is SQLite and for me was located at /var/lib/plexmediaserver/Library/Application Support/Plex Media Server/Plug-in Support/Databases/com.plexapp.plugins.library.db. The SQL query has a join included so the “parent” information (e.g. the album name) is readily available. Keep in mind that I am not sure how stable Plex’s DB schema is, or whether my assumptions about it are correct, but it seems to be doing the trick for me right now.
SELECT
t2.parent_title,
t1.guid_count,
*
FROM (
SELECT
*
FROM (
SELECT
*,
COUNT(*) OVER (PARTITION BY guid) guid_count
FROM metadata_items
)
WHERE guid_count > 1
ORDER BY guid
) AS t1
LEFT JOIN (
SELECT
id AS parent_table_id,
title AS parent_title
FROM metadata_items
) AS t2
ON t2.parent_table_id = t1.parent_id;
