Upgrading from 5.7.5.6 to 5.8.5.12

Developing with Concrete
1

I will be documenting here the process I went through in order for me to upgrade to version 5.8.5.12 an old website that I used to manage which was built on version 5.7.5.6.

I stopped being involved in this website and it was all fine for 5 years or so. The website did not have its Concrete5 version updated over time.

And then, just last week, the admin pleaded for my help in putting the site up again because their hosting provider suddenly made 7.3 the minimum version for PHP, rendering the website incompatible. I had to brush up on my PHP skills after being away from its ecosystem for 3 years now.

So I decided to put up this post in case there will still be devs who encounter the same problem.

SETTING UP MY LOCAL PHP DEVELOPMENT ENVIRONMENT

First off, I downloaded a copy of the codebase and the database dump in order for me to run the site in my local machine using PHP 7.1. It was easier for me to put up the site in my Mac machine rather than in my Windows machine because I could not find online the DLLs for PHP modules which Concrete 5.7.5.6 need to be enabled.

And so, I followed this tutorial for putting up a local PHP dev environment in Mac because it pointed me to running copy of PHP 7.1 and a PHP version switcher. The website ran on my Mac without needing to enable PHP modules in the config files.

Note: YMMV because the website I needed to run just needs the basic PHP setup.

UPDATING TO THE LATEST VERSION 5.7

I followed the “Replacing the original Concrete Directory (5.7.5.10 and earlier)” section in How to Upgrade Concrete CMS to update to version 5.7.5.13 before upgrading to version 5.8. This proved to be an easy and straightforward process.

UPGRADING TO VERSION 8.1

From here on out, I followed the steps in the “Upgrading via the command line (Version 8 and above)” section in How to Upgrade Concrete CMS.

After quite a few instances of trial and error, in my case, I found out that 8.1 is the next version I could upgrade to without encountering errors.

UPDATING TO VERSION 8.2

While updating version 8.2.1, I encountered an error message in the update process:

An exception occurred while executing 'DELETE FROM AttributeValues WHERE avID = ?' with params [105]:

SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails (`mmccon_c5`.`userattributevalues`, CONSTRAINT `FK_4DB68CA6A2A82A5D` FOREIGN KEY (`avID`) REFERENCES `AttributeValues` (`avID`))

After searching on the web, I thought this solution can help me proceed with the update. Based on the error message that was thrown by the migration script, I agreed with the solution’s suggestion of updating the foreign key in the table to have ON DELETE CASCADE. And so I ran the following queries on the database before rerunning the update:

ALTER TABLE UserAttributeValues DROP FOREIGN KEY `FK_4DB68CA6A2A82A5D`;

ALTER TABLE UserAttributeValues ADD FOREIGN KEY (avID) REFERENCES AttributeValues(avID) ON DELETE CASCADE;

When I reran the update, I encountered similar error messages for the CollectionAttributeValues and FileAttibuteValues tables. I just ran the same ALTER TABLE ... queries for them since they reference the same parent table.

And after running the last ALTER TABLE ... query, the update to version 8.2 went well.

UPDATING TO VERSION 8.5 AND SWITCHING TO PHP 7.3

I attempted to update straight to version 8.5.12 and encountered this error message:

In Resolver.php line 188:
The character set "utf8" is not supported.

To resolve that, I followed this suggestion from the Concrete5 forums. It appears that utf8mb4 is now the recommended character set for modern Concrete5 versions.

I retrieved a dump file from my local MySQL database so I could update the character set and collation. Before doing the search-and-replace on the dump file, check first which character sets your database server support using the query SHOW CHARACTER SET;.

My local database server supports the utf8mb4 character set with the utf8mb4_general_ci collation. So I went ahead with using those values in the dump file.

Note: I might also need to check which character sets are supported when I deploy to production later.

I also updated the charset values to utf8mb4 in the database connections defined in application/config/database.php.

After running ./concrete/bin/concrete5 c5:update --rerun, I hit another roadblock. This time, in the migration script dated 20170926000000.

In line 18 of that migration script, I changed the second condition of the query from postTo_cID = '' to CAST(postTo_cID AS CHAR) = '' because when I checked the structure of btSearch table, the postTo_cID column was already of integer data type.

After that, the update to version 8.5.12 went well. I also switched to PHP 7.3 at this point. You might need to do a quick regression test, especially in parts of your site which use custom code, to see if it complies with the standards of the current PHP version.

5 Likes
2

I stopped at version 8 because when I tried upgrading to version 9, I was now met with error messages saying some database table columns were non-existent or some database tables could not be created because of malformed foreign keys. And, ultimately, the goal was just to put the site up and running again on at least PHP 7.3.

Can anyone shed some light here?

1 Like
3

Hi @kenchi09 - thanks for the thoughtful write up of your process of getting to 8.5.12 :+1:

Can you post what the errors are that you’re running into re: the foreign keys, etc? That will help narrow down what the issue might be.

4

I tried updating from 8.5.12 to 9.2.1, and I encountered an error in the migration script named 20210712110100.

An exception occurred while executing 'UPDATE btContentImage SET sizingOption = 'constrain_size' WHERE maxWidth > 0 OR maxHeight > 0':  
                                                                                                                                          
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'sizingOption' in 'field list'

It looks like the migration scripts before this one failed to update the columns of the btContentImage table.

5

Hmm - wondering if this might help - c5:entities:refresh

6

Didn’t work.

It also threw this error message:

An exception occurred while executing 'ALTER TABLE OAuth2ClientScopes ADD CONSTRAINT FK_497E26F39ECFF1A8 FOREIGN KEY (clientIdentifier) REFERENCES OAuth2Client (identifier) ON DELETE CASCADE':

SQLSTATE[HY000]: General error: 1005 Can't create table `db`.`oauth2clientscopes` (errno: 150 "Foreign key constraint is incorrectly formed")
7

See

1 Like
8

This fixed the issue with the OAuth tables. c5:entities:refresh went fine.

However, when I run c5:update --rerun, these error messages still persist:

In AbstractMySQLDriver.php line 79:
An exception occurred while executing 'UPDATE btContentImage SET sizingOption = 'constrain_size' WHERE maxWidth > 0 OR maxHeight > 0':

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'sizingOption' in 'field list'

In Exception.php line 18:
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'sizingOption' in 'field list'

In PDOConnection.php line 141:
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'sizingOption' in 'field list'
9

Did you ever solve this issue? I am having the same problem moving from 8.5.19 → 9.x (I tried 9.1.1 and 9.3.7 both with same result). Any help most appreciated!

An exception occurred while executing 'UPDATE btContentImage SET sizingOption = 'constrain_size' WHERE maxWidth > 0 OR maxHeight > 0': SQLSTATE[42S22]: Column not found: 1054 Unknown column 'sizingOption' in 'field list'