Upgrading Concrete from 8.5 to 9.x

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: Character Sets and Collations

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 which will set all of the tables to use the same character set encoding.

If that doesn't completely solve the problem, you may have tables with mismatched collations. The following SQL query should help establish if your tables have differing collations set:

-- replace `<database name>` with the actual name of
-- your database
SET @database_name := '<database name>';
SELECT
  TABLE_COLLATION, 
  count(*) AS n
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = @database_name
GROUP BY TABLE_COLLATION;

You should get a result like this if they are all the same:

+--------------------+-----+
| TABLE_COLLATION    | n   |
+--------------------+-----+
| utf8mb4_unicode_ci | 427 |
+--------------------+-----+
1 row in set (0.088 sec)

If they are not the same, you can convert the tables that are in the minority with something like the following (after backing up your database in case anything goes wrong):

-- replace `<table_name>` with the name of
-- the actual table you want to convert
ALTER TABLE <table_name> 
CONVERT TO CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

If that doesn't solve the problem, 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.