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)

[lalinsky]

This could be also related to MBID merges, so yeah, I'll fix the problem so that track_mbid.mbid is never changed, track_mbid records are never deleted, and then rebuild all the data files.

[emkey08]

Thanks, lalinsky.

I guess it's not necessary to recreate the fingerprint data files though, since these shouldn't have changed?

[emkey08]

Also, as an additional note, the text escaping of the meta-update.jsonl.gz files changed on (or around) 2024-12-05. Before that date, double quotes within strings were (incorrectly) escaped as \\" within the jsonl files. After that date, they are now correctly escaped as \" as required by the JSON specification.

Steps to reproduce:

wget -q -O - https://data.acoustid.org/2023/2023-09/2023-09-23-meta-update.jsonl.gz | gunzip | grep 375694131
{"id":375694131,"track":"The Destruction of Psará","artist":"Manolis Kalomiris","album":"Triptych / Symphony no. 3 \\"Palamian\\" / Three Greek Dances / Psará","album_artist":"Manolis Kalomiris; Nikitas Tsakiroglou, Athens State Orchestra, Byron Fidetzis","track_no":11,"created":"2023-09-23T00:08:42.365855+00:00"}

wget -q -O - https://data.acoustid.org/2024/2024-12/2024-12-05-meta-update.jsonl.gz | gunzip | grep 380902705
{"id":380902705,"track":"Prologue \"Wake, Quivera\"","artist":"Henry Purcell","album":"The Indian Queen","album_artist":"Henry Purcell","track_no":8,"created":"2024-12-05T00:37:01.04719+00:00"}

As you can see, the 2023-09-23 file contains \\"Palamian\\" while the 2024-12-05 file contains \"Wake, Quivera\". It's not clear to me what could have caused this change in text escaping, but rebuilding all the meta files should probably fix this additional consistency issue as well.