Schema change release: May 17, 2021

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-11460Add 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!

MusicBrainz Server update, 2020-10-19

Today’s MusicBrainz Server brings a new data report, a continued conversion to React, some bugfixes and small improvements, but also tests refactoring.

Meanwhile, the search server has been updated twice in a row to fix bugs in JSON output mostly with MB Solr 3.2 (release notes) and MB Solr 3.3 (release notes), including the MusicBrainz API breaking change announced last month.

A new release of MusicBrainz Docker is also available that matches this update of MusicBrainz Server. See the release notes for update instructions.

Thanks to amCap1712 for fixing bugs in MB Solr, and to loujine for contributing code with yet a new data report. Thanks to Avamander, bonchiver_, chaban, draconx, eloise_freya, GTF1982, hawke, hibiscuskazeneko, jesus2099, jrv, kellnerd, Kid Devine, Psychoadept, selflessself, and wcw1966 for having reported bugs and suggested improvements. Thanks to kellnerd, mfmeulenbelt, and salorock for updating the translations. And thanks to all others who tested the beta version!

The git tag is v-2020-10-19.

Bug

  • [MBS-10221] – Track Parser not filling in artists
  • [MBS-11149] – Misspelling of the word “misspellings”
  • [MBS-11150] – Add CD-TOC wrongly defaults to last listed artist when none selected
  • [MBS-11156] – Track parser unsets “Various Artists” track artist credits
  • [MBS-11162] – Work type description bubble starts as default even if type is selected
  • [MBS-11174] – Editor profile added entities: Missing Add release edit type 216
  • [MBS-11176] – Heading of the release group section in the external links sidebar has disappeared

Improvement

  • [MBS-5225] – Allow showing tracklists everywhere when attaching/viewing discIDs
  • [MBS-7256] – Add “Expand all mediums” option to the release page
  • [MBS-8725] – Allow mediums to have an unknown tracklist
  • [MBS-11115] – Show detailed information when attaching disc IDs
  • [MBS-11139] – Use HTTPS for display on Library of Congress links
  • [MBS-11163] – Show type descriptions when editing entities
  • [MBS-11165] – Update the VK logo used in the sidebar
  • [MBS-11167] – Normalize vk.com links to HTTPS
  • [MBS-11173] – When deleting users, change their No and Yes votes on pending edits to Abstain

New Feature

  • [MBS-11117] – Report for mediums with very long durations from discID

React Conversion Task

  • [MBS-11141] – Convert Edit Relationship edit to React
  • [MBS-11152] – Convert entity/ratings page to React

Other Task

  • [MBS-11148] – Remove Google Play links from the sidebar

MusicBrainz Server update, 2020-09-21

React conversion tasks are conspicuously absent from today’s release, but that’s just because we needed to take some time to get it all working with the recent refactoring. This new server update mainly brings strong security improvements for the OAuth service. It also comes with a fair amount of smaller bugfixes and improvements. The most noticeable of these probably are the added details to the merge recordings’ form and the statistics by entity type on editors’ profile pages.

Announcement for MusicBrainz API users: A small but breaking change will be deployed on October 19th (in one month from now), to fix the JSON formatting of release packaging in search results (SEARCH-579).

A new release of MusicBrainz Docker is also available that matches this update of MusicBrainz Server. See the release notes for update instructions.

Thanks to kellnerd and loujine for contributing code. Thanks to calculator.ftvb, chaban, hibiscuskazeneko, jesus2099, kellnerd, lalinksy, psychoadept, rdswift, and spitzwegerich for having reported bugs and suggested improvements. Thanks to jesus2099, kellnerd, mfmeulenbelt, outsidecontext, and salorock for updating the translations. And thanks to all others who tested the beta version!

The git tag is v-2020-09-21.

Bug

  • [MBS-10880] – Series automatic ordering (without numbers) fails for new release group
  • [MBS-11065] – Smart link blocks affecting legitimate links
  • [MBS-11069] – Diff highlighting not visible for certain display resolutions
  • [MBS-11098] – Big Cartel URLs are denied for labels
  • [MBS-11101] – Series relationships not showing for work series

Improvement

  • [MBS-2768] – Display AcoustIDs, Annotation and any other useful info when merging recordings
  • [MBS-7473] – Adding a new discid: Allow to specify the target by its releaseid
  • [MBS-11017] – Normalize IMSLP URLs to HTTPS and add validation
  • [MBS-11058] – Tighten security of OAuth service
  • [MBS-11061] – Don’t allow MusicBrainz URLs in relationships
  • [MBS-11062] – Link basic how-tos from the front page
  • [MBS-11086] – Add icon for tabs with errors in release editor
  • [MBS-11109] – Block further more smart links
  • [MBS-11119] – Set a Content-Security-Policy header on account/admin related forms

New Feature

  • [MBS-7485] – OAuth token revokation through API
  • [MBS-9769] – Show entities added statistics on editor profile page
  • [MBS-10835] – Disallow creating new accounts with an e-mail already in use
  • [MBS-11097] – Support PKCE (Proof Key for Code Exchange) by OAuth clients

Task

  • [MBS-10921] – Clear editing history of unrelated recording-of relationship edits

Reminder: Upgrading to PostgreSQL 12 on May 18, 2020

As we announced in February, in two weeks time (May 18, 2020) we’ll be upgrading our production database server to PostgreSQL v12 (from v9.5). At the same time, v12 will become the minimum supported version for MusicBrainz Server, so we ask that you upgrade afterwards as soon as possible! If you’re still unsure, a Q&A is below.

When do I need to upgrade my postgres by?

As soon as possible after May 18 if you’d like to keep your musicbrainz-server code up to date.

How do I perform the upgrade?

We’ll provide instructions closer to May 18. It’s recommended that you don’t upgrade until then, since we’ll be providing scripts to resolve some issues.

Will the live data feed (replication packets) stop working right away if I don’t upgrade?

No, as long as you keep your musicbrainz-server code checkout on the v-2020-05-11 tag (which will be the final release before May 18) or earlier. Future releases may work for a while too.

This is not a schema change release, so replication will continue to work smoothly until you upgrade. No tables or views will change.

However, to make the upgrade process smoother we’ll be dropping the musicbrainz-collate and musicbrainz-unaccent extensions, instead using PG’s builtin collation support for the former and replacing the latter with the unaccent extension from postgresql-contrib. A few SQL functions are being added to enable this, and some indexes need to be rebuilt. This will all happen as part of upgrade scripts we provide (or you can import from scratch). Some features of musicbrainz-server that use these old extensions may cease to work if you don’t apply them.

The extension changes above don’t actually make use of any new PG 12 features. We’ll avoid using such features for at least 1 month.

If I’m already running PostgreSQL 12, do I need to do anything?

Yes, but things will be easier for you. As mentioned in the previous answer, we’ll be dropping the musicbrainz-collate and musicbrainz-unaccent extensions to make the upgrade process smoother for pre-v12 instances. So you’ll only have to run some upgrade scripts we provide to replace those extensions and rebuild some indexes.

My host/distribution doesn’t have PostgreSQL 12 yet!

If you’re running Debian or Ubuntu, the PGDG maintains an APT repository with the latest versions. These are the same packages MetaBrainz uses in production.

Amazon RDS supports PostgreSQL 12 since March 31.

I absolutely cannot upgrade yet! What should I do?

You can stay on the v-2020-05-11 release of musicbrainz-server or earlier until then. Replication packets (i.e. the live data feed) will continue to work until the next schema change on that tag, but you’ll have upgraded to v12 by then, right?

Instead of performing a pg_upgrade and running these upgrade scripts you mentioned, can I just import fresh data dumps into a new v12 cluster?

Of course. Just make sure your musicbrainz-server git checkout is on the v-2020-05-18 tag (once that’s released) or later before performing the import. And keep in mind it may be slower than a direct upgrade.

Upgrading Postgres instead of schema change: 18 May, 2020

Hello!

We’ve long procrastinated upgrading our production Postgres installation and we’ve decided to forego a schema change upgrade and instead upgrade Postgres to version 12.x. (We will migrate to whatever the latest stable version in the 12.x series will be).

This means that on 18 May we will not make any changes to the MusicBrainz schema, but  we will have some amount of down-time and/or read-only time while we upgrade Postgres on our production servers. We haven’t sorted out all of the exact details of how we will carry out this database upgrade, but the date is now confirmed.

If you operate a replicated instance of the MusicBrainz database we STRONGLY urge you to upgrade your installation shortly after we upgrade the production servers. After this release our team may start using Postgres features not available in Postgres 9.5.x, which is our current production version.

As usual for our releases that impact our downstream users, we will post many more details closer to the date and once the migration is complete, we will post detailed instructions on how you can upgrade your own installation.

Please post any questions you may have!

Thanks!

MusicBrainz schema change release, 2019-05-13 (with upgrade instructions)

We’re happy to announce the release of our May 2019 schema change today! Thanks to all who were patient during today’s downtime as we released everything to our production servers.

This is a fairly minor release as far as schema changes go, but please do report any issues that you come across, especially any related to genres and collections.

Visible changes with this release are limited to an indication if a specific artist credit is being edited (MBS-5387). Work on some of the changes to collections and genres is quite advanced, and we’re hoping to release some of the new features onto beta already in a week or so from now, while others might take a while longer.

Now, on to the instructions.

Schema Change Upgrade Instructions

Note: Importing the latest data dump is always a valid alternative to running ./upgrade.sh on an existing database, if you’d prefer to also get new data in one go. Just follow the relevant instructions in INSTALL.md. The git tag is v-2019-05-13-schema-change. The rest of the instructions here assume an in-place upgrade.

  1. Make sure DB_SCHEMA_SEQUENCE is set to 24 in lib/DBDefs.pm.
  2. If you’re using the live data feed (your REPLICATION_TYPE is set to RT_SLAVE), ensure you’ve replicated up to the most recent replication packet available with the old schema. If you’re not sure, run ./admin/replication/LoadReplicationChanges and see what it tells you; if you’re ready to upgrade, it should say “This replication packet matches schema sequence #25, but the database is currently at #24.”
  3. Take down the web server running MusicBrainz, if you’re running a web server.
  4. Turn off cron jobs if you’re automatically updating the database via cron jobs.
  5. Switch to the new code with git fetch origin followed by git checkout v-2019-05-13-schema-change.
  6. Install newer dependencies Yarn and NodeJS 8 or later according to install prerequisites.
  7. Run cpanm --installdeps --notest . (note the dot at the end) to ensure your perl-based dependencies are up to date.
  8. Run ./upgrade.sh (it may take a while to vacuum at the end).
  9. Set DB_SCHEMA_SEQUENCE to 25 in lib/DBDefs.pm as instructed by the output of ./upgrade.sh.
  10. Turn cron jobs back on, if applicable.
  11. Restart the MusicBrainz web server, if applicable. It’s also recommended you restart redis. If you’re accessing your MusicBrainz server in a web browser, run ./script/compile_resources.sh.

Here’s the list of resolved tickets:

Bug

  • [MBS-5387] – ACs being edited aren’t marked as having pending edits on the aliases tab
  • [MBS-9365] – event_meta_fk_id was never created as part of any upgrade script
  • [MBS-9462] – Standalone databases created before schema 21 are missing some l_event_url triggers
  • [MBS-10146] – Regression: ISE on Remove DiscID page
  • [MBS-10149] – Swap track titles with artist credits fails to update both fields properly
  • [MBS-10150] – Regression: The link to the release group reviews in the release page is broken

Improvement

  • [MBS-9664] – Add database constraints to disallow loop relationship
  • [MBS-10044] – Add place area to place lists

Database Schema Change Task

  • [MBS-10052] – Add new schema for the event art archive
  • [MBS-10173] – Create a genre table in the DB and populate it with existing genres
  • [MBS-10174] – Create an addition timestamp in the DB for new collection items
  • [MBS-10175] – Create a position integer in the DB for collection items
  • [MBS-10176] – Create a comment text field in the DB for collection items
  • [MBS-10177] – Create an editor_collection_collaborator table for collaborative collections
  • [MBS-10178] – Create a genre_alias table
  • [MBS-10181] – Create filesize for cover art and each thumb in the DB

React Conversion Task

  • [MBS-9925] – Convert collection pages to React
  • [MBS-10179] – Convert all entity list components to React

MusicBrainz Schema change upgrade downtime: 17:00 UTC (10am PST, 1pm EST, 19:00CEST)

Hi!

At 17:00 UTC (10am PST, 1pm EST, 19:00CEST) we will start the process of our schema change release. The exact time that we plan to start the change will depend on how long it takes to finish our preparations, but we expect it to be shortly after 17:00UTC.

Once we start the process we will put a banner notification on musicbrainz.org and we will also post updates to the @MusicBrainz twitter account, so follow us there for more details.

After the release is complete, we will post instructions here on how to upgrade your replicated MusicBrainz instances.

Schema change release: May 13, 2019

It’s been a while since our last schema change release in May 2017. To move forward on some features we’d like to add, we plan to have a Spring 2019 schema change release set for May 13th, 2019. This release should not be disruptive to downstream users, as we plan only to augment the schema with some new tables and columns, and not break any of the existing schema.

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

MBS-1658: Add a free text field to collection items. This change will allow users of collections to annotate each item with free text, to hold personalized info about the item (for example, that a release is of a specific pressing, was purchased in 1999 at the Village Discount, or was a gift from your mom). This ticket will add a new TEXT column to editor_collection_release and all other entity-specific editor_collection_* tables. It will not add any new tables or modify any existing columns.

MBS-5387: Mark artist credits as having pending edits when they’re being edited. On an artist’s aliases tab, we provide the ability to directly edit artist credits associated with that artist. However, doing so doesn’t indicate anywhere that the artist credits are being changed (we typically highlight entities with pending edits). To resolve this, we’ll be adding an INTEGER edits_pending column to the artist_credit table. This ticket will not change any existing columns of the artist_credit or artist_credit_name tables.

MBS-5818: Make it possible to have ordered collections. Editor’s collections of entities are automatically ordered by field, for example, releases in collection are ordered by ascending release date. Sometimes, one might want to order collector’s items by other criteria, for example, most wanted releases. This change will enable editors to order collection items by hand if they want to. To do so, an INTEGER position column will be added to the editor_collection_* tables.

MBS-7480: Store cover art image file sizes. Knowing file sizes for cover images and their thumbnails would allow us to better detect when a thumbnail isn’t available, and also allow us to display file sizes to the user before they download an image. To do this, we’ll add four INTEGER columns to the cover_art_archive.cover_art table to store the file sizes in bytes: filesize, thumb_250_filesize, thumb_500_filesize, thumb_1200_filesize.

MBS-9428: Allow multiple users to share one collection. In some cases (like a collection of cleaned up entities several people want to keep an eye on, or a radio station’s collection of vinyl the station owns), it would be quite useful if multiple editors could add (and remove) entries from a collection. This would make cooperation easier and hopefully make community projects (such as the Composer Diversity Database project) easier to start and work on. We’ll add an editor_collection_collaborator table linking collections to the editors allowed to make changes in them (only the collection owner will be able to make changes to the list of allowed collaborators).

MBS-9491: Move hard-coded genres to a database table. We recently added genres to MusicBrainz, but they’re currently stored as an object in the server code. This change will move them to a new table genre (id, gid, name, comment).

MBS-10062: Add aliases for genres. Connected to the previous issue, we need a way to be able to specify “hiphop”, “hip hop” and “hip-hop” are all the same thing, and eventually to store translated versions of genres. This will add a table genre_alias (id, genre, name, locale, edits_pending, last_updated, primary_for_locale).

MBS-9973: Add a date added column for collection items. Editor’s collections have no editing history, thus it doesn’t allow to sort items by date of addition to the collection they belongs to. To allow this, editor_collection_* tables will get a TIMESTAMP added column.

MBS-10052: Add new tables for the event poster archive. This will move us another step toward CAA-84, giving us a place to store event posters, logos, and other related images. The schema change here will be to add a new event_art_archive schema, detailed in the comments in MBS-10052. There will be no change to the existing cover_art_archive schema.

The following tickets will also be included, but only involve adding some missing foreign keys, triggers, and constraints to standalone mirrors; these will not be created and have no effect on replicated mirrors.

MBS-9365: Adds a missing foreign key between the event_meta and event tables.

MBS-9462: Adds some missing l_event_url triggers to delete unused URLs.

MBS-9664: Adds constraints to prevent an entity from linking to itself in a relationship.

If you have any questions, please do leave a comment below or on the linked JIRA tickets!

No Spring 2018 schema change

We recently decided not to have a spring 2018 schema change release. As usual, we still have some bits left over to finish up from the last spring schema change. More importantly, we’re making a concerted effort to improve the user experience (UX) of the MusicBrainz site — more on that in a blog post later.

We may decide to do an autumn 2018 schema change, but this depends on how well our UX efforts progress over the course of winter and spring.