Error upgrading 8.5.16 to 9.2.8

Developing with Concrete
1

I’m using the elemental theme. I’ve replaced /concrete with the same folder from 9.2.8. I visit the site and get the following error:

An exception occurred while executing ‘ALTER TABLE OAuth2ClientScopes ADD CONSTRAINT FK_497E26F3C7FD45E9 FOREIGN KEY (scopeIdentifier) REFERENCES OAuth2Scope (identifier)’: SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint

I’ve checked the collation on both tables and they match.

2

There is a chicken and egg issue with the oAuth tables when updating from 8 to 9. Dependencies between the tables prevent them from updating - even if empty. If you are not using oAuth at all, you can safely remove them and replace with similar from a fresh 9.2.x install.

1 Like
3

Cheers, John, worked great.

I deleted all tables containing oAuth and imported the same set of tables from a 9.2 database.

I then ran in to another error, a couple of tables related to messages were missing. Importing these from 9.2 sorted, and the upgrade ran smoothly.

Great tip, thanks again John!!

4

I’ve run into this same issue, but only on databases that were created on a host that doesn’t support the utf8_mb4 character set, and were later transferred to a host that does.

My preferred solution is to convert the tables:

SET FOREIGN_KEY_CHECKS=0;

ALTER TABLE `OAuth2AccessToken` DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `OAuth2AccessToken` CHANGE `identifier` `identifier` char(36) NOT NULL COMMENT '(DC2Type:guid)';

ALTER TABLE `OAuth2AccessToken` CHANGE `client` `client` char(36) DEFAULT NULL COMMENT '(DC2Type:guid)';

ALTER TABLE `OAuth2AccessToken` CHANGE `scopes` `scopes` longtext NOT NULL COMMENT '(DC2Type:json_array)';

ALTER TABLE `OAuth2AuthCode` DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `OAuth2AuthCode` CHANGE `identifier` `identifier` char(36) NOT NULL COMMENT '(DC2Type:guid)';

ALTER TABLE `OAuth2AuthCode` CHANGE `client` `client` char(36) DEFAULT NULL COMMENT '(DC2Type:guid)';

ALTER TABLE `OAuth2AuthCode` CHANGE `scopes` `scopes` longtext NOT NULL COMMENT '(DC2Type:json_array)';

ALTER TABLE `OAuth2Client` DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `OAuth2Client` CHANGE `identifier` `identifier` char(36) NOT NULL COMMENT '(DC2Type:guid)';

ALTER TABLE `OAuth2Client` CHANGE `name` `name` varchar(255) NOT NULL;

ALTER TABLE `OAuth2Client` CHANGE `redirectUri` `redirectUri` varchar(255) NOT NULL;

ALTER TABLE `OAuth2Client` CHANGE `clientKey` `clientKey` varchar(255) NOT NULL;

ALTER TABLE `OAuth2Client` CHANGE `clientSecret` `clientSecret` varchar(255) NOT NULL;

ALTER TABLE `OAuth2RefreshToken` DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `OAuth2RefreshToken` CHANGE `identifier` `identifier` char(36) NOT NULL COMMENT '(DC2Type:guid)';

ALTER TABLE `OAuth2RefreshToken` CHANGE `accessToken` `accessToken` char(36) DEFAULT NULL COMMENT '(DC2Type:guid)';

ALTER TABLE `OAuth2Scope` DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `OAuth2Scope` CHANGE `identifier` `identifier` varchar(255) NOT NULL;

ALTER TABLE `OAuth2Scope` CHANGE `description` `description` varchar(255) NOT NULL;