The data provided at data.acoustid.org isn't consistent

[emkey08]

There is some kind of consistency issue with the data provided at https://data.acoustid.org

The column pair (track_id, mbid) of the table track_mbid should be unique. However, there are records where this isn't the case.

Example and steps to reproduce:

2016-12-25-track_mbid-update.jsonl.gz says that the (track_id=25853676, mbid=3a17f2f7-8f91-434d-a9cf-a9923a83d051) record has ID 14531123 and was created on 2016-12-25:

wget -q -O - https://data.acoustid.org/2016/2016-12/2016-12-25-track_mbid-update.jsonl.gz | gunzip | grep 3a17f2f7-8f91-434d-a9cf-a9923a83d051
{"id":14531123,"track_id":25853676,"mbid":"3a17f2f7-8f91-434d-a9cf-a9923a83d051","submission_count":6,"created":"2016-12-25T03:22:36.089666+00:00","updated":"2022-01-16T19:16:48.550622+00:00"}

However, 2020-02-05-track_mbid-update.jsonl.gz instead says that this exact record has ID 18527045 and was created on 2020-02-05:

wget -q -O - https://data.acoustid.org/2020/2020-02/2020-02-05-track_mbid-update.jsonl.gz | gunzip | grep 3a17f2f7-8f91-434d-a9cf-a9923a83d051
{"id":18527045,"track_id":25853676,"mbid":"3a17f2f7-8f91-434d-a9cf-a9923a83d051","submission_count":4,"created":"2020-02-05T05:01:20.486986+00:00"}

I first thought that the original record (with ID 14531123) might have been accidentally deleted somehow and that the new record (with ID 18527045) has then been re-inserted later on. However, this apparently isn't the case: the original record has still been updated on 2022-01-16, which is after the new record has been created (on 2020-02-05). Hence, both records must actually have existed at the same time.

This makes no sense to me. Is there an explanation for this?

Thank you!

[emkey08]

After checking again on this, it seems that this is just a minor issue. Luckily, there are just 458 tracks which are affected by this problem (as per database snapshot 2023-05-20). That number was found with the following query:

SELECT track_id, mbid, count(*) FROM track_mbid GROUP BY track_id, mbid HAVING count(*) > 1;

However, for those few tracks which are affected by this, the submission counts (and sometimes also the disabled status) of the linked MusicBrainz recordings are incorrect. This is also true for the main database on acoustid.org itself.

Here is an example of a track with an incorrect disabled status. The 2020-03-04-track_mbid-update.jsonl.gz says that the link from AcoustID track 16460536 to MusicBrainz recording 6f32542e-c6eb-45cc-a317-b4d8399cbbf6 has been disabled on 2020-03-04:

wget -q -O - https://data.acoustid.org/2020/2020-03/2020-03-04-track_mbid-update.jsonl.gz | gunzip | grep 6f32542e-c6eb-45cc-a317-b4d8399cbbf6
{"id":10901219,"track_id":16460536,"mbid":"6f32542e-c6eb-45cc-a317-b4d8399cbbf6","submission_count":1,"disabled":true,"created":"2014-06-22T22:25:12.650192+00:00","updated":"2020-03-04T23:31:56.633364+00:00"}

However, when looking at https://acoustid.org/track/16460536 we can see that this link is still incorrectly enabled as of right now.

The problem also becomes evident when looking at the output of the following query:

acoustid=# SELECT * FROM track_mbid WHERE track_id = 16460536 AND mbid = '6f32542e-c6eb-45cc-a317-b4d8399cbbf6';
    id    | track_id |                 mbid                 | submission_count | disabled |            created            |            updated            
----------+----------+--------------------------------------+------------------+----------+-------------------------------+-------------------------------
  8894757 | 16460536 | 6f32542e-c6eb-45cc-a317-b4d8399cbbf6 |                2 | f        | 2013-09-07 00:09:01.917261+02 | 2021-07-14 22:30:11.091301+02
 10901219 | 16460536 | 6f32542e-c6eb-45cc-a317-b4d8399cbbf6 |                1 | t        | 2014-06-23 00:25:12.650192+02 | 2020-03-05 00:31:56.633364+01
(2 rows)