Error while upgrading from 3.3.4 to 4.0

[francesco]

Your software
My PHP version is : 7.4
My MySQL/MariaDB version is (delete as applicable): MySQL/MariaDB version 10.3.31-MariaDB-log-cll-lve

Updating/Installing Errors
I am: Updating
Upgrading via : Web

These errors are showing in the installer :

These errors are showing in the Mautic log :

[2021-08-30 18:44:08] console.CRITICAL: Error thrown while running command “doctrine:migrations:migrate --no-interaction --env=prod --no-debug”. Message: “An exception occurred while executing ‘ALTER TABLE oauth2_clients ADD CONSTRAINT FK_CLIENT_ROLE FOREIGN KEY (role_id) REFERENCES roles (id)’: SQLSTATE[HY000]: General error: 1005 Can’t create table dbname.oauth2_clients (errno: 150 “Foreign key constraint is incorrectly formed”)” {“exception”:"[object] (Doctrine\DBAL\Exception\DriverException(code: 0): An exception occurred while executing ‘ALTER TABLE oauth2_clients ADD CONSTRAINT FK_CLIENT_ROLE FOREIGN KEY (role_id) REFERENCES roles (id)’:\n\nSQLSTATE[HY000]: General error: 1005 Can’t create table dbname.oauth2_clients (errno: 150 “Foreign key constraint is incorrectly formed”) at /path_to_mautic/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:128, Doctrine\DBAL\Driver\PDO\Exception(code: HY000): SQLSTATE[HY000]: General error: 1005 Can’t create table dbname.oauth2_clients (errno: 150 “Foreign key constraint is incorrectly formed”) at /path_to_mautic/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDO/Exception.php:18, PDOException(code: HY000): SQLSTATE[HY000]: General error: 1005 Can’t create table dbname.oauth2_clients (errno: 150 “Foreign key constraint is incorrectly formed”) at /path_to_mautic/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:132)",“command”:“doctrine:migrations:migrate --no-interaction --env=prod --no-debug”,“message”:“An exception occurred while executing ‘ALTER TABLE oauth2_clients ADD CONSTRAINT FK_CLIENT_ROLE FOREIGN KEY (role_id) REFERENCES roles (id)’:\n\nSQLSTATE[HY000]: General error: 1005 Can’t create table dbname.oauth2_clients (errno: 150 “Foreign key constraint is incorrectly formed”)”}

Your problem
My problem is : i was running versione 3.2.4. I updatet via web interface to 3.2.5 and then to 3.3.4 and everything goes well

then i tried to update from 3.3.4 to 4 and everything goes well until the last step “database updade” that returns an error. Then i saw in the log file and there was the error i pasted above.

then i restored a backup of mautic directory and a backup of the database and i’m still running 3.3.4… someone knows what i can do?

thanks

Steps I have tried to fix the problem :

[joeyk]

Hey, is this an instance, that you upgraded from M2 ? There was an issue with the M2->M3 upgrades which caused the same problems. I also have a couple of instances, that are stuck with 3.3.3 because of the same reason.
Migrations won’t run due to key constrains.
I looked at:

Migrations failed when upgrading to 3.3

and:

Fix migration for 3.3.0

[francesco]

Thanks! I’ll take a look to the links you posted!

[aaaskew]

I have the same error on upgrade 3.3.1 → 4.0.2. I believe this installation was upgaded from earlier v2 releases through the web interface. Has a fix been found?

My investigation so far:

• Under mysql:

SHOW ENGINE INNODB STATUS;
…

LATEST FOREIGN KEY ERROR

2021-11-17 08:40:00 0x7f8f30e67700 Error in foreign key constraint of table mautic.ma_oauth2_clients:
FOREIGN KEY (role_id) REFERENCES ma_roles (id):
…

This is the ‘last’ error so maybe more problems exist?

After the upgrade I see:

DESC desc mautic.ma_oauth2_clients;
±--------------------±-----------------±-----±----±--------±---------------+
| Field | Type | Null | Key | Default | Extra |
±--------------------±-----------------±-----±----±--------±---------------+
…
| role_id | int(10) unsigned | YES | | NULL | |
±--------------------±-----------------±-----±----±--------±---------------+

DESC mautic.ma_roles;
±---------------------±-------------±-----±----±--------±---------------+
| Field | Type | Null | Key | Default | Extra |
±---------------------±-------------±-----±----±--------±---------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
…

I also have an oauth2_clients table without the ‘ma_’ that I believe was created at 3.3.1 but never used due to an incorrect prefix on clean install and restore of data at that release. This uses int(10) unsigned for the ids. Another difference is that these unused tables are utf8mb4 while the used tables are plain utf8.

Should I apply a fix to one column of one table manually or would it be worth trying something as described in Was unable to update to 4.0, here's what finally worked for me ?

[vesper8]

This is how I was able to resolve my problem once and for all. Now I have a fully upgraded and clean installation of v4

Was unable to update to 4.0, here's what finally worked for me

I’ve been updating Mautic since v2 and I guess I had some old problems with the DB schema. I was on 3.3.1 and I was able to update to 3.3.4 no problem, but then all hell broke loose when I tried to update to 4.0 I was getting errors related to the oauth2_clients table foreign key constraints. I wasted a lot of time trying to figure this out, finally what worked for me I created a brand new 3.3.4 install, allowed the installation wizard to create a new database. I then dumped all of my existi…

[martinkiely]

Hi -

I’m also experiencing an error upgrading from 3.3.4.to 4.0 or 4.0.2

Error: Interface ‘Bazinga\OAuthServerBundle\Model\AccessTokenInterface’ not found - in file /home/xxxxxxxxxxxx/xxxxxxxxx.xxxxxxxxxxxxxxxxxxx.com/app/bundles/ApiBundle/Entity/oAuth1/AccessToken.php - at line 23

Is this a database issue also?

I would appreciate suggestions on how to move forward to upgrade to 4.0.2. Thank you.

I look forward to your reply.

Thank you.

[marinardz21]

Hola!!
¿Lograron solucionar?
Estoy intentando actualizar de la versión 3.3.4 a la 4.2 pero no lo he logrado.
Agradecería que me dieran alguna sugerencia.
Saludos!!

[francesco]

Hi everyone. I leave the version to 3.3.4 for a few months and this morning i started to figure out how to update. Searching on forum and git i saw that during update a field is added to the oauth_clients table and this field refers to the role id field.

the problem is that the new field role_id is unsigned but role ID field is not. So i found this solution: change the table roles field ID adding the unsigned attribute.

this is not as simple as it seems to be because there are various foreign key involved anyway i found the right sql commands to execute.

These are the steps to upgrade via CLI:

1. enter into the mautic dir
2. digit php bin/console mautic:update:find
3. digit Y
4. digit php bin/console mautic:update:apply
5. open phpmyadmin, select the database, click SQL, paste the query below end execute
6. digit php bin/console mautic:update:apply --finish
7. clear cache php bin/console cache:clear or delete elements in /var/cache

IMPORTANT: execute queries (5) before the apply --finish command otherwise you’ll have error for foreign key

IMPORTANT 2 after the upgrade you could get a 403 error (i got it) to solve the problem edit the .htaccess code commenting rows 103, 104 and 105 https://github.com/mautic/mautic/issues/10913

# Apache 2.4+
<IfModule authz_core_module>
    # Deny access via HTTP requests to all PHP files.
#    <FilesMatch "\.php$">
#        Require all denied
#    </FilesMatch>

ALTER TABLE users DROP FOREIGN KEY FK_1483A5E9D60322AC;

ALTER TABLE `users` CHANGE `role_id` `role_id` INT(11) UNSIGNED NOT NULL;

ALTER TABLE permissions DROP FOREIGN KEY FK_2DEDCC6FD60322AC;

ALTER TABLE `permissions` CHANGE `role_id` `role_id` INT(11) UNSIGNED NOT NULL;

ALTER TABLE `roles` CHANGE `id` `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `permissions` ADD CONSTRAINT `FK_2DEDCC6FD60322AC` FOREIGN KEY (`role_id`) REFERENCES `roles`(`id`) ON DELETE CASCADE ON UPDATE RESTRICT;

ALTER TABLE `users` ADD CONSTRAINT `FK_1483A5E9D60322AC` FOREIGN KEY (`role_id`) REFERENCES `roles`(`id`) ON DELETE RESTRICT ON UPDATE RESTRICT;