Over 2,000 Release Groups with no Releases

MusicBrainz
1

I was exploring the MusicBrainz data with direct queries of my mirror database, and I noticed something curious: there appear to be over 2,000 Release Groups which have no Releases.

Here are five:

Release Group MBID Title (linked)
4da05891-5073-4723-b78f-f3b007a72172 Coffee EP
7717e5b4-47cc-480d-b4c9-e627df34f0f1 I Like You EP
10b97063-3040-472d-8171-842a5829f26f Shock
484a4f44-2595-4d86-af01-4e429894fb65 Midnight -星を数える夜-
40dd2496-d483-4b35-a304-3ee29e91653e Max Payne (Re-Engineered Soundtrack)

From a quick look at the edit histories, it seems that there are a couple of ways they got added. “Coffee EP” and “I Like You EP” were added years ago, seemingly just as a Release Group with no Release. (I always add a Release and let MB add the corresponding Release Group, so avoid this outcome.) The Max Payne RG was added together with a Release, but the Release was later edited to connect with a second RG, leaving this first RG stranded.

I don’t particularly want to take on the project to clean this up, but perhaps someone else will. FYI.

Here is the SQL query which counts the affected Release Groups, and its result:

select count(*) from release_group as RG left join release as R 
on R.release_group = RG.id where R.release_group IS NULL;
 count 
-------
  2090

Here is the query which lists the affected Release Groups, and its result:

select RG.gid, RG.name from release_group as RG left join release as R 
on R.release_group = RG.id where R.release_group IS NULL LIMIT 5;
                 gid                  |                 name                 
--------------------------------------+--------------------------------------
 4da05891-5073-4723-b78f-f3b007a72172 | Coffee EP
 7717e5b4-47cc-480d-b4c9-e627df34f0f1 | I Like You EP
 10b97063-3040-472d-8171-842a5829f26f | Shock
 484a4f44-2595-4d86-af01-4e429894fb65 | Midnight -星を数える夜-
 40dd2496-d483-4b35-a304-3ee29e91653e | Max Payne (Re-Engineered Soundtrack)

I found zero cases of the complementary situation: Release entries without a corresponding Release Group.

1 Like
2

If there was no links attached, then they would automatically disappear. But due to the links, they are still here.

Often they are a result of when a duplicate release is added, and someone moves the release or merges it.

1 Like
3

Good analysis and write-up. I want to add that there is not necessarily a problem with empty release groups. Upcoming albums yet to be released are common, and if a known released album exists (e.g. with a link to the Discogs master) I prefer it exists with no releases than be removed.

I don’t have a local copy running at the moment to generate the list, but I’m interested in helping clean it up. The example you noted where a release is moved to a duplicate group rather than the two groups merging should probably be the first focus—obvious duplicates, usually easy to spot by looking at the artist and RG edit history. Afterwards they could be inspected for being ‘upcoming’, ‘known-existing’, or ‘questionable/fake’.

1 Like
4

If it’s helpful, I can run more queries. I have the local database on hand. The hard part is writing the query.

It’s easy to extend my query so that it makes a CSV file with all 2,090 Release Groups, including a link in the title column. Someone could open that CSV file in a spreadsheet or browser, click on each link in turn, and investigate.

What might be more targeted and useful, but also harder to craft, might be a query which searches for Release Groups RG1 which had an Add Release edit with a Release R, where R now is associated with a different Release Group RG2. Then the CSV file could have the MBIDs for and links to RG1, RG2, and R. If someone could identify what data is particularly helpful for coming up with a merge edit of RG1 into RG2 (just the hexadecimal MBID values? or links?) the query could include those columns.

I don’t promise to jump quickly to figure out that second query. But if someone figures it out, I’m happy to run it on my data, and pass back the resulting CSV file.

5

If you share a .csv file, I’m happy to begin looking through them and cleaning them up. The second query could isolate the list to ‘likely-duplicate’, but it’s not something I can do :slight_smile:

6

There’s a report for this - Release groups without any releases - MusicBrainz

3 Likes
7

Thank you for this reference! I guess I don’t need to share my simple list of these Release Groups. If I make the more sophisticated query, showing RGs which remain after their Releases were moved to a new RG, then I will update this thread.

How to replicate edit table on musicbrainz-docker?
8

It’s a real pity when this is done.

1 Like
9

This query appears difficult to run on my replica of the MusicBrainz database. The replication appears not to include the edit* tables, which would be a necessary pivot for this query. And, if I can’t run the query, I can’t be sure that I have written it correctly.

So, I don’t expect rapid progress on this aspect of the thread.

1 Like
10

I was able to load the edit* tables onto my replicated database. See details at How to replicate edit table on musicbrainz-docker?.

I am now trying to run queries which might come up with subsets of these 2,000 RG’s which might be easier to clean up.

Here is a work in progress for a query which I hope would list Release Groups which used to have a Release, but it was moved to a different Release Group when it might have been better to merge:

% psql -h localhost  -U musicbrainz -d musicbrainz_db --command '
select RG.id as "RG id", RG.gid as "RG mbid", RG.name as "RG title", 
R1.id as "R1.id(null)", E.id as "E id", E.type as "E type", 
E.editor as "E editor (id)", E.close_time as "E close", 
R2.id as "R2 id", R2.gid as "R2 mbid", R2.name as "R2 title", 
R2.release_group as "R2 release_group id"  
from release_group as RG left join release as R1 on R1.release_group = RG.id 
join edit_release_group as ERG on ERG.release_group = RG.id 
join edit as E on ERG.edit = E.id  join edit_release as ER on ER.edit = E.id 
join release as R2 on ER.release = R2.id 
where R1.release_group IS NULL ORDER BY RG.gid LIMIT 10'

(Line wraps added.)

This returns too many edits. I clearly don’t understand well enough how to filter the list of edits.

FYI.

2 Likes
11

It looks like an edit which moves release R1 from RG1 to RG2 will have an edit entry E, and edit_release entry (E, R1), and edit_release_group entries (E, RG1) and (E, RG2). There may also be an edit_data entry for E which contains a block of JSON data that shows which fields changed.

This query finds groups of RG1, RG2, E, R1 with the relationship above. It is more selective in finding edits where a Release moved from one Release Group to another. However, it also seems to find edits where RG1 was merged properly into RG2.

% psql -h localhost  -U musicbrainz -d musicbrainz_db --command '
select RG1.id as "RG1 id", RG1.gid as "RG1 mbid", RG1.name as "RG1 title", 
RG2.id as "RG2 id", RG2.gid as "RG2 mbid", RG2.name as "RG2 title", 
R1.id as "R1.id(null)", 
E.id as "E id", E.type as "E type", E.editor as "E editor (id)", 
R2.id as "R2 id", R2.gid as "R2 mbid", R2.name as "R2 title", 
R2.release_group as "R2 release_group id"  
FROM release_group as RG1 left join release as R1 on R1.release_group = RG1.id 
join edit_release_group as ERG1 on ERG1.release_group = RG1.id 
join edit_release_group as ERG2 on ERG2.edit = ERG1.edit 
AND ERG2.release_group <> ERG1.release_group 
join release_group as RG2 on RG2.id = ERG2.release_group 
join edit as E on ERG1.edit = E.id  join edit_release as ER on ER.edit = E.id 
join release as R2 on ER.release = R2.id 
WHERE R1.release_group IS  NULL 
ORDER BY RG1.gid ASC, E.id ASC, RG2.gid ASC, R2.gid ASC LIMIT 10;'

(Again, line wraps added.)

This finds about 621,991,228 rows in my database. So, not selective enough! lol

I’m going to stop my enquiry here. My primary goal is something other than cleaning up these 2,000 Release Groups. However, if these notes help someone else, that would be wonderful.

2 Likes