Problem when updating from 8.5.12 to 9.2.1

The following problem or error occurs with the Remote_Update from 8.5.12 to 9.2.1 (see error message below). Does anyone have an idea what to do?
Thank you in advance for your help.

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 mp_cms.oauth2clientscopes (errno: 150 “Foreign key constraint is incorrectly formed”)

I get a message that says “Update Strongly Discouraged” when updating from 8.5.12 to 9.2.1, hoping to see some responses to this one.

Where are you getting this message @hrice88 ?

Hi @mendesknabepr - do you have any OAuth or secondary authentication services enabled on the site? Just trying to get a sense of what might give rise to something like this issue.

Hello Evan,
no, nothing like that is activated.

Also, is this on Windows? I’m seeing the lowercase table names. Might help to know the environment too. I’ll keep digging though.

All right I think I have a solution:

This comes from the database creating one table with one collation and the other with another. In some sites there’s a difference between the databases created during install and when Concrete adds tables during an upgrade later.

The fix is to make the OAuth2ClientScopes and OAuth2Client databases tables have the same collation. If they don’t you can use a command like

ALTER TABLE OAuth2ClientScopes CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

to fix one or both of them.

Let me know if that does the trick.

Hello Evan, first of all thank you for your feedback on this issue and sorry for taking so long to respond. Unfortunately, I was no longer able to try out or carry out your approach. A friend of mine, who is a “guru” when it comes to SQL databases, has already helped me. He fixed all problems in the database. Unfortunately, I can’t even say exactly what he has changed or fixed. But now the update from 8.5.12 to 9.2.1 worked properly. Unfortunately, there is now another problem that I’m kind of stuck with. As I said, 9.2.1 runs clean. However, this is under PHP 7.4.9. But I have to update to PHP 8.x. I did this and now I have the following problem. (See attached screenshot). While I understand what the problem is here, PHP 8.x no longer supports __autoload() like this, you have to use sql_autoload_register() instead. Unfortunately I have no idea “where” I need to adjust this.
Do you perhaps have an idea?
Once again, thank you very much in advance for your hard work.

I just came across this issue when updating a site from 9.1.3 to 9.2.2. The site started out way back in v7-point-something and it turned out many tables used outdated character sets and collations which resulted in in the core upgrade to 9.2.2 failing with foreign key errors (I am amazed it got as far as 9.1.3 without this issue showing up)

Rather than fix them all manually, I added a Database Collation Fixer to my Extreme Clean addon. This lists database tables with outdated collation and then fixes the collation and character set.