Upgrading 8.5.2 to 8.5.6 fails with foreign key error

Using Concrete / Help
1

PHP 7.4.33
Concrete5 8.5.2 → 8.5.6

This site was running on an ancient version of Concrete5 (8.2.1). I have managed to update to version 8.5.2. I now run into this issue: Upgrading Concrete from 8.5 to 9.x :: Concrete CMS
And the linked github page: Migration error 8.5.2 -> 8.5.4 · Issue #8981 · concretecms/concretecms · GitHub
Does not resolve the issue.

When I execute ./concrete/bin/concrete5 c5:update && ./concrete/bin/concrete5 c5:package-update --all && ./concrete/bin/concrete5 c5:entities:refresh, that appears to be working as expected. I then follow that with ./concrete/bin/concrete5 c5:database:charset:set utf8mb4, but that does not work as it keeps throwing the same errors that the Foreign Key can’t be updated:

converting table “atExpressSettings”: An exception occurred while executing ‘ALTER TABLE atExpressSettings CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci’:
SQLSTATE[HY000]: General error: 1832 Cannot change column ‘exEntityID’: used in a foreign key constraint ‘FK_E8F67F0FCE2D7284’

I have been working on this issue for days now and I’m unable to get passed it to move the side up in the versioning scheme.

What else might I give a try?

2

If you have access to phpMyAdmin or Adminer you could run the query manually like this

SET FOREIGN_KEY_CHECKS=0;
ALTER TABLE atExpressSettings CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
SET FOREIGN_KEY_CHECKS=1;

If there are more errors like this an you have access to the command line you can run

concrete/bin/concrete5 orm:schema-tool:update --dump-sql

and that will give you all the queries to put between the “SET FORIEGN_KEY_CHECKS” lines there to complete the upgrade.

3

When I tried to execute the statements in PhpMyAdmin it throws an error as well.

#1832 - Cannot change column 'exEntityID': used in a foreign key constraint 'FK_E8F67F0FCE2D7284'

The SQL dump provides a huge list of alter statements. I executed those, but nothing was updated according to Mysql.

4

Did you include the first and last lines of the sample I shared? The query seems to be still checking Foreign Key Constraints.

5

Yes. I wrapped it in the Foreign Key Checks.

6

Well that’s odd, I’ve never had that not work.

7

Yeah, that’s why I’m stumped and stuck.

8

Do you have a lot of custom express entities set up in this site? Just wondering if something might’ve gotten deleted without cleaning itself up. Sometimes that can happen with a user getting deleted, so maybe if there’s an Express entity associated with a user? But those are just wild guesses.

9

Just wondering how you resolved this as I have the same issue trying to upgrade past 8.5.2.

Any help/insight would be appreciated.

10

I haven’t needed to do this for a while.

My workround was (assuming you don’t have any data in the express tables) was to delete the tables, the recreate them from a fresh install of a later core version. There are some forum posts on that subject quite a way back.

11

Have you tried following the information here:
Upgrading Concrete from 8.5 to 9.x :: Concrete CMS ?

If you have, see if all the tables are set with the same collation: Issue after 8.5.x update towards 9.x - Foreign key constraint is incorrectly formed

12

Thanks for the advice.
Replacing the tables worked.

1 Like
13

I’ll give that a try as I haven’t been successful so far.

1 Like
14

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;

I’ve updated the tutorial with the above information.