Upgrading from Concrete v8.5 through 9.x can sometimes lead to some confusion. This document collects various challenges and solutions to aid you in your journey.
Background
Why is this upgrade path difficult? One of the main reasons is that software has to adapt to a changing landscape of other vendors in the stack, including databases, PHP, and changes in best practices regarding data encoding and storage. Concrete v8.5.x included changes to accommodate a larger set of characters that could be stored in the database. This included a change in charset from utf8 to utf8mb4.
Road blocks
One of the errors you might encounter is in migration 20190509205043 which ususally occurs when upgrading to 8.5.3 or 8.5.4:
Executing migration: 20190509205043
Refreshing schema for Concrete\Core\Entity\Express\Entry...
Refreshing schema for Concrete\Core\Entity\Express\Control\PublicIdentifierControl...
Refreshing schema for Concrete\Core\Entity\Express\Control\Control...
In AbstractMySQLDriver.php line 115:
An exception occurred while executing 'ALTER TABLE ExpressFormFieldSetPublicIdentifierControls ADD CONSTRAINT FK_67180C04BF396750 FOREIGN KEY (id) REFERENCES ExpressFormFieldSetControls (id) ON DELETE CASCADE':
SQLSTATE[HY000]: General error: 1005 Can't create table `<something>`.`ExpressFormFieldSetPublicIdentifierControls` (errno: 150 "Foreign key constraint is incorrectly formed")
This is caused by having mixed collations in the database tables.
The short answer to this problem is to run c5:database:charset:set utf8mb4, however, there are likely additional steps you should take, including backing up the database first. It's best to try this fix on a copy of your website first to ensure that it will work as expected.