Once again, we’re opting to not have a schema change release in the Autumn of 2017. Expect our next schema change release to be on or about 15 May, 2018.
We’re happy to announce the release of our May 2017 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.
Currently, the only visible change for editors is the ability to add multiple lyrics languages to works. We’ve also modified the schema to support dynamic attributes for entities other than works, but the UI for that won’t be complete for another release or two.
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 rest of the instructions here assume an in-place upgrade.
- Make sure
DB_SCHEMA_SEQUENCEis set to 23 in
- If you’re using the live data feed (your
REPLICATION_TYPEis 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/LoadReplicationChangesand see what it tells you; if you’re ready to upgrade, it should say “This replication packet matches schema sequence #24, but the database is currently at #23.”
- Take down the web server running MusicBrainz, if you’re running a web server.
- Turn off cron jobs if you’re automatically updating the database via cron jobs.
- Switch to the new code with
git fetch originfollowed by
git checkout v-2017-05-15-schema-change.
cpanm --installdeps --notest .(note the dot at the end) to ensure your perl-based dependencies are up to date.
- Downgrade DBD::Pg by running
cpanm TURNSTEP/DBD-Pg-3.5.3.tar.gz(version 3.6.0 breaks things currently).
./upgrade.sh(it may take a while to vacuum at the end).
DB_SCHEMA_SEQUENCEto 24 in
lib/DBDefs.pmas instructed by the output of
- Turn cron jobs back on, if applicable.
- 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
npm installfollowed by
For those curious, here’s the list of resolved tickets (excluding MBS-8393):
- [MBS-9329] – event_meta is empty
- [MBS-9271] – Prevent usernames from being reused
- [MBS-9273] – Fix the a_ins_edit_note function in older setups to not populate edit_note_recipient for own notes
- [MBS-9274] – Fix the edit_note_idx_post_time_edit index in older setups to handle NULL post_time
- [MBS-5452] – Support multiple lyric language values for works
We’re going to start our schema change release process today at 17h UTC.
We anticipate having a short downtime of a few minutes as we”ll need to restart our database server. As usual, we’re not certain when we will start the downtime, but we’ll keep people posted about our progress in IRC and on Twitter.
Once we’re done with the release we will post instructions on this blog on how to upgrade any replicated instances of MusicBrainz you might be running.
We have picked our set of tickets and the date for our May 2017 schema change release: May, 15th 2017. This will be a fairly standard and minor schema change release — we’re only tackling 3 tickets that affect downstream users and no other infrastructure changes.
Take a look at our list of tickets for this schema change release. There really are only two tickets that will affect most of our downstream users:
- MBS-8393: “Extend dynamic attributes to all entities” Currently our works have the concept of additional attributes which allows the community to decide which sorts of new attributes to apply to a work. (e.g. catalog numbers, rhythmic structures, etc) This ticket will implement these attributes to all of our entities. Also, this ticket will not change any of the existing database tables, it will only add new tables.
- MBS-5452: “Support multiple lyric language values for works” Currently only one language or the special case “multiple languages” may be used to identify the language used in lyrics. This ticket allows more than one language to be specified for lyrics of a work.
The following tickets are special cases — they will not really affect our downstream users who do not have edit data loaded into their system. We are only including this change at the schema change release time in order to bring some older replicated systems up to date. If you do not use the edit data, then please ignore these tickets.
- MBS-9271: “Prevent usernames from being reused” This ticket does not change the schema, but for sake of minimizing downstream disruption, we’re going to carry out this ticket during the schema change.
- MBS-9274: “Fix the edit_note_idx_post_time_edit index in older setups to handle NULL post_time” This ticket fixes an SQL index on an edit related table.
- MBS-9273: “Fix the a_ins_edit_note function in older setups to not populate edit_note_recipient for own notes” This ticket also fixes an SQL index on an edit related table.
This is it — really minor this time around. If you have any questions, feel free to post them in the comments or on the tickets themselves.
Now that we’ve finally finished the schema change release, I wanted to give an account of what happened in this arduous process. Before I dive into the details, I want to offer a picture that best sums up our current situation and challenges:
The shipping container is MusicBrainz and the boat is our hosting infrastructure. This picture perfectly describes the sort of challenges we’ve faced over the past few days. 🙂
Here is what happened:
Because the site was recently running slow and our search servers kept crashing, Zas and I were not available to help Bitmap prepare for the schema change release. This long process was left to Bitmap and Gentlecat to take care of on their own. We quickly realized that we were not ready for the release when the due date came and thus we delayed one week.
Sunday 22 May
Finally we were ready to proceed with the Postgres 9.5 upgrade. Once we started the process, we kept running into small problems that we didn’t get in our test setups. We do not have access to enough infrastructure to have a complete clone of our production environment, so we can only do so much to prepare for all the things that might happen when we run upgrades on our production servers.
All the while we attempted to start the upgrade, our backup database server was running much slower than anticipated. In the end we figured out that a step for optimizing the database (analyzing it) wasn’t carried out. During this time the site was really slow/unusable, but by the time the problem became apparent we had started the upgrade and could not turn back.
Once the upgrade was done, optimizing the database took much much longer than usual: 3 hours! This process wasn’t started until about 1am local time, which made for a very long night before that process finished. And even then we hit snags and had to start over a couple of times. At about 4:30am we had the site running on Postgres 9.5 in read only mode. The plan was to rest and start the schema change release in the morning.
Monday 23 May
Of course we had spent all of our time working on the Postgres upgrade and site stability, so our document that we use to plan the schema change was not in place. We spent the day preparing this and other bits for the release. To get an appreciation for what this document looks like, have a look! Note that some steps could be instant, others might take hours to carry out. Others might involve a sub-step or 20 not included in the document.
In the evening we were ready to make the change. By this point our backup DB was performing much better, so the read-only site worked acceptably. Thus, we started the release. Overall, the actual release process was reasonably smooth – we hit a few snags and had to do a lot of waiting for our slow servers. At about 1am in the morning things were finally complete. We proceeded with our sanity checks to make sure things went smoothly and all of them passed.
We proceeded to put the site into read-write mode and immediately saw portions of Postgres crashing, which is really bad. With community feedback we quickly deduced that some write operations were causing Postgres back-end processes to crash. We went back to read-only mode on the site and things stabilized and we finally went to bed at 3am.
Tuesday 24 May
In the morning we quickly found the source of database trouble with the help from the Postgres people on IRC. Thanks for the swift help Johto! We found that the steps for installing the updated third party extensions into Postgres had not completed correctly. Repeating the steps by hand fixed this problem.
Sadly yesterday morning we got an email informing us that our Live Data Feed replication stream had become corrupted. 😦 This was heartbreaking news to us, since it means a great inconvenience to all of our Live Data Feed users. We immediately split into two teams: Zas, chirlu and myself to fix the root cause of the issue and Bitmap to investigate fixing the stream.
I proceeded to setup a test environment was able to quickly reproduce the problem. Zas and chirlu were an amazing support team Googling issues as I came across them. Within fairly short time we fixed the problem and deployed the fix to our database server. The problem was caused by a bug in a piece of code that we’ve been using for 13 years! A change in Postgres caused this bug to actually become a problem and corrupt our replication feed. 😦
Once the problems were fixed we needed to initiate a new data dump and check to make sure the replication stream is working correctly. Of course we found a problem that we fixed and re-started the process to dump the data. Loads of hurry-up-and-wait situations to try our patience!
When we were satisfied that things were working correctly we re-enabled the site as read-write at about 1am and allowed people to continue editing. Exhausted we stumbled into bed waiting for data dumps to sync out to the FTP site.
Wednesday 25 May
Today Bitmap was flying home and as soon as WiFi became available on his flight he started working and helping with putting the schema change to bed. We’ve verified that everything is working as expected. At last this saga comes to and end and we can all take a break and catch up on sleep!
Thank you for your patience through all of this.
Starting with this release, PostgreSQL 9.5 is now our minimum supported version. In order to import any future data sets, you will need to upgrade your installation to version 9.5.
Due to unforeseen problems with the Live Data Feed (AKA replication), users with slave databases will be required to first import a fresh data dump into their new 9.5 installation. We apologize that this is the case, but even had this stream not been broken, doing a clean import is faster and easier than doing the migration. For details on what happened during this rather lengthy schema change release, stay tuned for a post mortem blog post that covers the details.
If you have a non-replicated standalone database, you can use pg_upgrade and run ./upgrade.sh directly, but for simplicity we strongly recommend importing the latest data dump. Thus, we will only provide instructions for a clean import:
- Make sure you have PostgreSQL 9.5 installed, and your database settings in lib/DBDefs.pm are updated to point to the 9.5 installation if you currently have an older version of postgres running. If you already have postgres 9.5 and want to replace the existing database there, you’ll need to drop it first (using dropdb or from within psql). Be careful that you’re not dropping any important data if this is a standalone database that you’ve made changes to.
- Take down the web server running MusicBrainz, if you’re running a web server.
- Turn off cron jobs if you are automatically updating the database via cron jobs.
- Switch to the new code with
git fetch originfollowed by
git checkout v-2016-05-23-schema-change-v2
cpanm --installdeps --notest .to ensure your perl-based dependencies are up to date. Note the dot at the end.
DB_SCHEMA_SEQUENCEto 23 in
- Download the latest data dumps. If you don’t need historical edit data, excluding the edit dump will speed up your import significantly.
- Initialize a new database from the data dumps downloaded in step 7. Detailed instructions for doing this are located in INSTALL.md in the musicbrainz-server repository; if your data dumps are in /tmp, the command should simply be something like
./admin/InitDb.pl --createdb --import /tmp/mbdump*.tar.bz2.
- After the import has finished, turn cron jobs back on, if applicable.
- Restart the MusicBrainz web server, as well as memcached, if applicable.
We would like to thank bitmap, Gentlecat, zas, chirlu, reosarevok, gcilou for contributing directly to the release and we’d also like to thank all of the people who helped test, debug or otherwise offer support in this quite difficult release. Thank you!
And finally, here’s the list of changes you can expect in the upgrade:
- [MBS-6406] – Admins can’t change email addresses
- [MBS-8288] – Missing indexes for inverse lookup on *_gid_redirect tables
- [MBS-8669] – Primary key for place table missing on old slaves
- [MBS-8906] – Release pages ISE if CB doesn’t return JSON from its API for whatever reason
- [MBS-8928] – If you submit the release editor without being logged in, it displays “[object Object]” as an error mesage
- [MBS-8943] – Some pages do not respect DB_READ_ONLY setting
- [MBS-1873] – Fix vote tallies for edits
- [MBS-3887] – Duplicate artist and label names not being checked against alias
- [MBS-8287] – Log deleted entities that were in a subscribed collection
- [MBS-8433] – Work attributes don’t have a uuid
- [MBS-8716] – Store the edit data in a JSONB column
- [MBS-8717] – Move the edit data to a separate table
- [MBS-8838] – Add gids to all *_type* tables
- [MBS-8873] – Convert and unify artist credit editors to React
- [MBS-8909] – Add logos to IMDb and VGMdb links in the sidebar
- [MBS-8939] – Update the Instagram logo used in the sidebar
- [MBS-8940] – Let banner message editors dismiss the banner only temporarily
- [MBS-8656] – Bring edit table indexes back into sync
- [MBS-8719] – Stop materializing of edit and vote counts
- [MBS-8720] – Add a materialized view of edit note recipients
- [MBS-8727] – Prevent duplicate votes
- [MBS-8800] – Create the earthdistance extension and add a geodetic index for place coordinates
- [MBS-8804] – Add BRIN indexes for timestamp columns
- [MBS-8897] – add new entity icons
- [MBS-8938] – Schema changes to support alternative tracklists
We’ve finally completed the schema update and things are returning to normal. We need to get a new data dump out and then we will provide upgrade instructions tomorrow. As you might be able to guess, unless you are already on Postgres 9.5, we are going to recommend a clean data import, rather than a migration, if you have a replicated slave.
And, if anyone even dare ask (within the next week) when an updated VM will be released, you owe the whole development team each 2 bars of high quality chocolate.
Feeling lucky, punk?
P.S. Can you tell we’ve been up too long? 🙂