Tag: schema change

MusicBrainz is announcing a new schema change release for May 15, 2023. The actual schema (database) changes we’ve detailed below shouldn’t have much perceivable impact on mirror servers, especially if you only use the web service; most are to remove unused tables/columns or tweak how certain tables are materialized.

The only breaking change to a replicated table is dropping the cdtoc.degraded column (MBS-12573). It’s unused in our codebase, so will have no effect on the web service. However, if you’re querying the cdtoc table directly, you’ll want to make sure your SQL queries don’t expect this column to exist.

One minor change affects first-release-date data for release groups and recordings in the web service (MBS-12800), but only where cancelled releases exist. As of this writing, there are currently only 133 releases with the “cancelled” status.

One change we consider significant is that, as part of this release, we plan to enable replication with dbmirror2 (MBS-12107) by default. This should require no changes on the part of mirror server operators and should be completely invisible/seamless during the upgrade if all goes according to plan. We consider it significant because it’s rewriting a critical part of our replication system and changing the packet format used underneath.

Tools that operate on the current (let’s call them version 1) replication packets, like mbdata, will continue to work; MusicBrainz will continue to generate the old  v1 packets for the foreseeable future while we work to move external tools to the new, v2 packet format.

Without further introduction, here’s the list of tickets for the Spring 2023 schema change:

Schema changes

The following tickets change the database schema in some way.

• MBS-4685: Allow removing own edit notes when there’s been no follow-up / MBS-11312: Allow original editors and admins to modify edit notes. In order to give editors and admins the option to modify or delete edit notes, this will add a new (non-replicated) table, edit_note_change, where the information about any changes made will be stored. No changes will be made to the structure of the existing edit_note table.
• MBS-12573: Drop unused degraded column from cdtoc. This was a column that was used many years ago to store whether CD TOCs were based on a less accurate algorithm. For a long time, it’s always contained FALSE (since we dropped the less accurate data at some point). It’s also completely unused. We are just dropping the column entirely.
• MBS-12704: Remove legacy “Watch artist” code. Over 10 years ago, we had code to follow artists and be notified of new releases and whatnot, but the whole system was never upgraded during the NGS migration. We have decided that if we re-implement this, it will be as part of ListenBrainz, not MusicBrainz. As such, we are dropping the relevant tables: editor_watch_artist, editor_watch_preferences, editor_watch_release_group_type, editor_watch_release_status.
• MBS-12794: Don’t use spammer tags/ratings when calculating tag counts / rating averages. We do not delete spammer accounts (we just hide them) in order to train our code to identify other potential spammers. An unfortunate side effect of this is that any ratings and tags left by spammers are currently still taken into account when calculating aggregate ratings and tags for MusicBrainz entities. We’ll be making a change to our triggers that update these aggregates to ignore any ratings and tags by editors marked as spammers. This change affects master/standalone databases, but should have no impact on mirror servers, where these triggers do not exist. Some tag and rating values will be changed by this though.
  Edit (Mar. 23): The team has removed MBS-12794 from this release after deciding that the implementation needs more discussion.
• MBS-12800: Exclude cancelled releases when calculating first recording / release group dates. We store data about releases which were announced, but then were cancelled before they were released. These are often stored with release dates, representing the date the release was meant to have been released. While this makes sense, it has the side effect that these dates affect the calculation of the first release date for any recordings linked to these releases, and the release groups they are in. Clearly, the recordings and release groups were not released on these dates, and we should use the date of their first actual release instead. This will change the triggers that update the release_first_release_date materialized table, which itself will affect the calculations of recording_first_release_date and the release date columns in release_group_meta. These triggers run on mirrors as well.

The remaining tickets below do not make any changes to the database schema.

Replication system changes

• MBS-12107: Replication with dbmirror2. See the introduction above and the linked ticket for more information. The prerequisite database changes required for this were already applied as part of the Spring 2022 schema change, so enabling the v2 system only requires code changes.

Upgrade script changes

• MBS-12370: Rename schema change upgrade script suffixes to clearly indicate which nodes they run on. This is an internal-only change which should have no impact on users.

We’ll post upgrade instructions for standalone/mirror servers on the day of the release. If you have any questions, feel free to comment below or on the relevant JIRA tickets.

Today we’re announcing a MusicBrainz database schema change release planned for May 16, 2022. The majority of these changes follow the theme of improving data integrity and consistency, performance, or just cleaning up old cruft. Others relate to new features for genres and artist credits. We’re also introducing a new entity based on tags, like genres that came before it: Mood. See below for more details, including information on how these changes affect the schema or existing data. We expect people will encounter zero breaking changes, but it doesn’t hurt to double check, especially if you have a specific or non-standard use of the database!

Here’s our list of tickets for the Spring 2022 schema change:

Schema changes

• MBS-12256: Keep rating and rating_count column on *_meta tables up-to-date with triggers. An internal-only change to help us keep aggregate rating information (i.e. an average rating and count of ratings for each entity) up-to-date more easily, and help keep these values accurate. This change affects master/standalone databases, but should have no impact on mirror servers, where such triggers are not created. It’s possible that some existing aggregate ratings data was out of sync and will be updated with this change.
  
• MBS-12224: Keep tags’ ref_count and aggregate vote counts updated with triggers. Like the change above for ratings, this is primarily internal-only and intended to help us keep tag counts in sync, though an adjacent goal is to make features like MBS-960 easier to implement. This also revives the tag.ref_count column, which hasn’t been updated for years, in order to provide a faster way of sorting tags/genres by usage count. Like above, this change should have no impact on mirror servers schema-wise, but will fix some existing corrupt tag counts.
  
• MBS-12249: Add a materialized area_containment table kept up-to-date with triggers. Pages that make use of area containments, e.g. the list of artists from an area, which are expected to account for sub-areas, are currently quite slow and we’d like to improve upon this. The slowness is related to the recursive queries we use to get contained sub-areas – these queries are uncached and calculated on-the-fly. This ticket addresses these performance issues by caching area containment information in a new aptly-named area_containment table. Consistent with the tag and rating tickets above, this table will also be kept up-to-date with triggers. This change should have no impact on mirror servers except to make certain area requests faster; it does not affect existing data.
  
• MBS-12250: Create dbmirror2 schema on production and mirror servers. The dbmirror extension we use to generate our replication packets a.k.a Live Data Feed is a 20 year old tool. It has issues and limitations that are difficult to fix, and we aim to replace it with something more maintainable. We wrote dbmirror2 to do that, but still have the task of getting it deployed to mirrors seamlessly. This will happen invisibly without any changes needed on mirrors! The action for this ticket is to simply create the schema for dbmirror2; it’s not actually used for replication yet. We’ll first have a testing phase and make sure external projects like mbdata work with the new replication packet format.
  
• MBS-12200: Drop schema objects related to Amazon cover art support. For a long while, releases with Amazon URLs would be checked for cover art on Amazon, and if found, a link to the image would be cached for display. Unfortunately Amazon’s API to do this changed, and we haven’t synced artwork from them in years. We still have many old images cached and we still display those, but they aren’t guaranteed to be in sync. Last year we decided to drop support for displaying these, while giving time for users to upload any correct images to the Cover Art Archive. To help with this, we have a report of releases with Amazon cover art but no Cover Art Archive front cover.
  
  The schema change here involves dropping the release_coverart table (which was private and non-replicated) and the release_meta.amazon_store column (which was completely empty and unused). This change should have no impact on mirror servers, unless you were using this table or column for your own purposes, because they should be otherwise empty.
  
• MBS-12141: Block tag names that are empty or have uncontrolled whitespace with database constraints. Recently we discovered a bug where empty or blank tag names could be submitted in the web service. This has since been fixed, but we’d also like to prevent such empty tags by adding a database constraint. This change has no effect on mirror servers schema-wise, where such constraints are not created. A few blank tags have already been deleted from the production database, but otherwise existing data is not affected. If any such tags exist in your standalone database, they’ll be deleted.
  
• MBS-12252: Add edit_genre table. A requirement to start storing edit history for genres (right now changes leave no trail). This will add the empty table to mirror servers as well, but will not affect any existing data.
  
• MBS-12253: Add relationship tables for genres. A requirement to be able to relate genres to other entities (such as URLs for the equivalent Wikipedia or Rate Your Music pages). This will add the empty tables (and accompanying example tables in the documentation namespace) to mirror servers as well, but will not affect any existing data.
  
• MBS-12254: Add genre_annotation table. A requirement to make it possible to eventually add annotations to genres. This will add the empty table to mirror servers as well, but will not affect any existing data.
  
• MBS-12255: Add genre_alias_type table and make genre_alias consistent. Originally the (as yet unused) genre_alias table was designed as a heavily simplified version of the alias tables for other entities. In retrospect, this was not a good decision, since it would make it harder to just use the generic implementation of our alias code for genres. As such, we’re adding a genre_alias_type table (originally genre aliases had no types) and replacing the genre_alias table with one having the extra columns matching other entities’ alias tables. These changes will also happen to mirror servers, but since the genre_alias table was completely unused it should not cause any issues. In case any standalone (not mirrored) servers were using genre_alias, we will ensure any existing data is transferred to the new version of it.
  
• MBS-12241: Drop the whitespace_collapsed database constraint. We’ve had a constraint for years that tries to ensure that columns like entity names do not contain multiple consecutive spacing characters (disallowing names such as “This    Title”). In retrospect, this was overreaching, since there are several cases in which a specific number of spaces in a title can be shown to be artist intent. Additionally, we recently discovered that we had some very old data in the database that actually violated this constraint (causing issues when importing data to a standalone server). The data seemed to actually be correct (i.e. some of the aforementioned edge cases) so rather than amending it, we’re removing the constraint. This will have no effect on mirrors since they don’t run constraints.
  
• MBS-12225: Rename “slave” to “mirror” (inclusive language update). We recently got a request from a long-time supporting organization to pick a different term for what we’ve historically called “slave server”. Since we were already sometimes using “mirror server” to mean the exact same thing, we are just changing the official name and will use “mirror server” in the future. RT_SLAVE will still work in DBDefs.pm, at least for now, but we’d suggest changing to the new (and equivalent) RT_MIRROR in your mirror servers’ DBDefs.pm. We’re likely to eventually drop support for RT_SLAVE in a future schema change, so we’ll remind you about changing it in future upgrade instructions.
  
• MBS-12190: Add Mood support. Music mood was originally meant to be automatically calculated by the now-discontinued AcousticBrainz project. That’s obviously no longer in the cards, but this data would still be quite useful for ListenBrainz. As such, we’re planning to add basic support for mood tags in MusicBrainz in the same way we currently do genres. This can then be leveraged by ListenBrainz to collect the information directly from users playing music through their BrainzPlayer, and it can also of course be entered directly from MusicBrainz in the same way other tags (including genre tags) can. This will add new mood tables to mirrors and will detect some previously generic tags as mood tags, but it won’t cause any changes to the underlying data.
  
• MBS-11760: Expand the database triggers which remove empty tags to all entities. When the last use of a tag (up- or downvote) is removed, we use triggers to completely remove the tag from the database. Some of the relevant triggers (for events, places, recordings and releases) were never created though, so if the last use was on an entity of one of those types the empty tag would not get purged. This doesn’t affect mirrors directly since the triggers don’t run on mirrors (but they should no longer get unused tags coming through replication).
  
• MBS-11457: Drop series ordering_attribute. This column was added back when we were expecting to have different types of ordering attributes for series, but we have never used it. We planned to remove it last year already, but that required equivalent changes on the search server that couldn’t be made at the time. We are planning to just drop the column.
  
• MBS-11456: Add MBIDs and redirect tables for artist credits. Adds a gid column to the artist_credit table, and a new artist_credit_gid_redirect table. It generates MBIDs for existing artist credits that will be replicated to mirrors.
  
  Artist credit MBIDs will be mainly exposed through the web service at first. The MBIDs will allow public identification of artist credits outside of MusicBrainz, and open the possibility of some more features in the future.

• MBS-12208: Show withdrawn release groups in the official artist overview. The Withdrawn release status was added recently. Release groups containing only releases with this status were meant to be shown in the main (official) artist overview, but the way this is implemented means a small edit to the get_artist_release_group_rows function was needed. Mirrors using the materialized tables will need to update the data; more info will be provided as part of the upgrade instructions.

We’ll post upgrade instructions for standalone/mirror servers on the day of the release. If you have any questions, feel free to comment below, or on the linked JIRA tickets if relevant there!

NB: This post was updated on 21 March to include a ticket (MBS-12208) that we forgot to list originally

We’re having a schema change release on May 17, mostly to make small changes that will make our queries more efficient, ensure better constraints, and make some hardcoded options editable without schema changes in the future. We are also upping the required versions of both Perl and Node.js to 5.30 and 16.0 respectively (see the “Minimum version requirements” section below.)

Here’s our list of tickets for the Spring 2021 schema change, with descriptions of what’s being changed:

Schema changes

• MBS-1424: Add a “first release date” field to recordings. A very popular request for years, this allows requesting the date of the first ever release a recording appeared on. This adds materialized tables recording_first_release_date and release_first_release_date which are updated via triggers whenever the earliest date changes. The change was released as an optional extension to the main MusicBrainz server schema on Dec 16, 2020, but it will be added to the main schema during this schema change.
• MBS-10208: Allow merging collections. Users who decide two of their collections should be joined into a larger one should be able to do so without having to move all the entities in the collection manually. This requires adding a editor_collection_gid_redirect table (equivalent to other existing x_gid_redirect tables) to ensure the old collection links redirect to the one they have been merged into.
• MBS-10566: Convert allowed_series_entity_type and allowed_collection_entity_type to tables to allow for additions without schema changes. The constraints allowed_series_entity_type and allowed_collection_entity_type specify which types of entity can be used in series and collections, respectively. As such, if we want to add the possibility to create series of artists, we need to modify the constraint during a schema change. For ease of use, we are moving the constraints to be their own tables instead, allowing us to update them as needed in the future outside of a schema change release.
• MBS-10647: Add [no label] to b_del_label_special trigger for labels. The b_del_label_special trigger ensures that any attempt to remove a special purpose label fails. Currently it only checks the special case “Deleted label”, but since “[no label]” is also a special purpose label that should never be deleted, we will add its ID to the trigger check.
• MBS-10821: Remove orphaned recordings from collections for deletion. Replaces a single function, delete_orphaned_recordings(), to add a new clause that makes it so that recordings referenced only in collections (but not linked to anything else in the database) can be deleted as orphans. This was released on the main MusicBrainz servers on June 15, 2020, but it will be added to the main schema during this schema change.
• MBS-10962 / MBS-11460 – Add materialized tables and indexes to fetch releases and release groups by artist or track artist. These tickets will address performance issues on our current artist pages. They do not modify any existing tables, but as mentioned, add some new tables (to be updated via triggers) and indexes.
• MBS-11097: Support PKCE (Proof Key for Code Exchange) by OAuth clients. Adds two new columns to the editor_oauth_token table. This feature is opt-in, but allows public OAuth2 clients to mitigate auth code interception attacks. The change was released as an optional extension to the main MusicBrainz server schema on Sep 21, 2020, but it will be added to the main schema during this schema change.
• MBS-11431: Speed up /ws/js/check_duplicates. Adds new indexes only (on the artist, label, place, and series tables, plus their respective alias tables). Improves some slow queries in the editing interface related to duplicate checking, i.e. finding other entities with the same name. Since this is a non-breaking change, it was released on the main MusicBrainz servers on Mar 15, 2021, but it will be added to the main schema during this schema change.
• MBS-11451: Support ratings for places. Places can be reviewed in CritiqueBrainz yet cannot be rated in MusicBrainz. This is a strange state of affairs: clearly, if they are worth reviewing in depth, they also deserve the option to rate them. As such, we will add place_rating_raw and place_meta tables, in the same way we have for other ratable entities.
• MBS-11453: Change entity0_cardinality, entity1_cardinality to SMALLINT. The cardinality columns of the link_type table are used to indicate whether the entity on each side of the relationship is expected to have only a few uses of the relationship type in question, or many (too many to comfortably display/edit). This is what stops every single recording of a work showing up in an edit work page, for example. At the moment we allow only two values for cardinality, 0/1. While it is possible that we will want to allow a few other values in between what is effectively “do not show anywhere ever” and “show all the time”, it is clear we will not need more than 32.000 values. As such, we are moving these columns from INTEGER to SMALLINT to reduce table size.
• MBS-11456: Add MBIDs and redirect tables for artist credits. Adds a gid column to the artist_credit table, and a new artist_credit_gid_redirect table. The MBIDs will allow public identification of artist credits outside of MusicBrainz, and open the door to useful features in the future.
• MBS-11457: Drop series ordering_attribute. This column was added back when we were expecting to have different types of ordering attributes for series, but we have never used it. We are planning to just drop the column.
• MBS-11459: Add a script to create edit_data_type_info. edit_data_type_info is a function used for development that we added in 2020 and we will now add to mirrors as well, both for consistency and for anyone who uses their mirror for development and just wants to use it.
• MBS-11463: Add view to easily access medium track lengths. Our current way of finding the length of a medium is to either load all its tracks, then sum the durations, or to use the duration of its disc ID when present. The first of these requires a lot more processing than just getting the durations straight from the database, while the second ignores any data tracks not on the CD TOC. This adds a medium_track_durations view that allows easy access to the duration of the track lengths for any medium.
• MBS-11464: Drop statistics.log_statistic. We added the statistics.log_statistic table for a Google Summer of Code project back during a 2012 schema change, but the work never got finished and implemented. We are not planning to implement it anymore, so this table is useless and we will be dropping it.
• MBS-11466: Change language.frequency and script.frequency to SMALLINT. The frequency column of a language or script indicates how often it’s used and lets us sort the most frequently used entries at the top of our lists. But we don’t store an exact count, just a number from 0-4 indicating “frequently used,” “hidden,” or “other.” Like MBS-11453 above, we don’t expect to need a full INTEGER type to store these columns at any point in the future, so can safely move them to SMALLINT.

Minimum version requirements

We’re raising the minimum required version for Node.js to v16 (which is the next LTS release coming in April 2021). Our current required version, v10, is hitting the end of its life cycle in April, and given there shouldn’t be a particular difficulty installing the current Node.js LTS on any system, it makes sense to just upgrade to the most recent LTS by the time of the schema change day.

We’re also raising the minimum required version for Perl to 5.30. The latest version is 5.32, but the current Ubuntu LTS (20.04), which is likely to be the next base image for our Docker containers, only provides 5.30. Our current required version, 5.18, was released all the way back in 2013, so moving to 5.30 is already a fairly significant improvement.

We’ll post final details about this release just prior to the release and shortly after we complete it, including instructions on how you can update your own copy. If you have any questions, please do leave a comment below or on the linked JIRA tickets!