# Error while upgrading from 3.3.4 to 4.0 **URL:** \ **Category:** Mautic 4 Install/Upgrade Support\ **Created:** [September 2, 2021, 8:53am UTC](https://forum.mautic.org/t/error-while-upgrading-from-3-3-4-to-4-0/20686 "2021-09-02T08:53:29Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![francesco](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mautic.org/francesco/32/82_2.png) [@francesco](https://forum.mautic.org/u/francesco)\ **Post date:** [September 2, 2021, 8:53am UTC](https://forum.mautic.org/t/error-while-upgrading-from-3-3-4-to-4-0/20686/1 "2021-09-02T08:53:29Z")
**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_ : ---
**Author:** ![joeyk](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mautic.org/joeyk/32/11164_2.png) [@joeyk](https://forum.mautic.org/u/joeyk)\ **Post date:** [September 2, 2021, 9:00am UTC](https://forum.mautic.org/t/error-while-upgrading-from-3-3-4-to-4-0/20686/2 "2021-09-02T09:00:57Z")
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: > > > \[//\]: # ( Invisible comment: > IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII…I > Before you create the issue: > IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII > Search for similar report among other reported issues. > Learn how to troubleshoot at https://www.mautic.org/docs/en/tips/troubleshooting.html > Use drag&drop to attach images or other files ) > > \## Bug Description > > When upgrading to 3.3 from 3.2.5 the migrations did not run correctly. The error was > > \`\`\` > ALTER TABLE lead\_lists ADD category\_id INT UNSIGNED DEFAULT NULL -\> ALTER TABLE lead\_lists ADD CONSTRAINT FK\_6EC1522A12469DE2 FOREIGN KEY (category\_id) REFERENCES categor > ies (id) ON DELETE SET NULL Migration 20210104171005 failed during Execution. Error An exception occurred while executing 'ALTER TABLE lead\_lists ADD CONSTRAINT FK\_6EC1522A12469DE2 FOREIGN K > EY (category\_id) REFERENCES categories (id) ON DELETE SET NULL': \\ SQLSTATE\[HY000\]: General error: 1005 Can't create table \`#sql-965\_c385\` (errno: 150 "Foreign key constraint is incorrectly formed") > \`\`\` > > The isse is, that in migration https://github.com/mautic/mautic/blob/3.3.0/app/migrations/Version20210104171005.php is run command > > \`\`\` > ALTER TABLE {$this-\>prefix}lead\_lists ADD category\_id INT UNSIGNED DEFAULT NULL > \`\`\` > > Without explicitly set width of the column, in our case it created INT(10) and then the migration failed. After setting this field to INT(11) ran without an error. > > Safer to have > > \`\`\` > ALTER TABLE {$this-\>prefix}lead\_lists ADD category\_id INT(11) UNSIGNED DEFAULT NULL > \`\`\` > > Probably the cause is the setting and version of Mysql server (5.5.5-10.2.16-MariaDB-10.2.16+maria~artful). > > Thanks > > | Q | A > | --- | --- > | Mautic version | 3.2.5 > | PHP version | 7.4 > | Mysql version | 5.5.5-10.2.16-MariaDB-10.2.16+maria~artful > | Browser | any > > \### Steps to reproduce > 1. Upgrade to 3.3.0 > > > \### Log errors > > ALTER TABLE lead\_lists ADD category\_id INT UNSIGNED DEFAULT NULL -\> ALTER TABLE lead\_lists ADD CONSTRAINT FK\_6EC1522A12469DE2 FOREIGN KEY (category\_id) REFERENCES categor > ies (id) ON DELETE SET NULL Migration 20210104171005 failed during Execution. Error An exception occurred while executing 'ALTER TABLE lead\_lists ADD CONSTRAINT FK\_6EC1522A12469DE2 FOREIGN K > EY (category\_id) REFERENCES categories (id) ON DELETE SET NULL': \\ SQLSTATE\[HY000\]: General error: 1005 Can't create table \`#sql-965\_c385\` (errno: 150 "Foreign key constraint is incorrectly formed") > > \[//\]: # ( Invisible comment: > Please check for related errors in the latest log file in \[mautic root\]/app/log/ and/or the web server's logs and post them here. Be sure to remove sensitive information if applicable. ) and: > > > | Q | A > | --------------------------------…------ | --- > | Branch? | "3.3" > | Bug fix? | no > | New feature? |no > | Deprecations? |no > | BC breaks? |no > | Automated tests included? |no > | Related user documentation PR URL | na > | Related developer documentation PR URL | na > | Issue(s) addressed | Fixes #9709 > > \#### Description: > This addresses schema differences for M2 and M3 introduced in https://github.com/mautic/mautic/pull/9544. It should also remove the need for https://github.com/mautic/mautic/pull/9714 since it seems that some categories.id has a length of 10 and some 11 for different versions of MariaDB. > > \#### Steps to test this PR: > 1. Load up \[this PR\](https://mautibox.com) > 2. Preferably run the migrations on a failed upgrade, a M2 installation upgraded to M3, and an installation on M3. All three should work. (I've only tested on a M3 schema as I don't have a M2 schema but tried to manipulate the database to emulate all three scenarios). ---
**Author:** ![francesco](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mautic.org/francesco/32/82_2.png) [@francesco](https://forum.mautic.org/u/francesco)\ **Post date:** [September 2, 2021, 9:13am UTC](https://forum.mautic.org/t/error-while-upgrading-from-3-3-4-to-4-0/20686/3 "2021-09-02T09:13:21Z")
Thanks! I’ll take a look to the links you posted! 😗 ---
**Author:** ![aaaskew](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mautic.org/aaaskew/32/608_2.png) [@aaaskew](https://forum.mautic.org/u/aaaskew)\ **Post date:** [November 17, 2021, 11:33am UTC](https://forum.mautic.org/t/error-while-upgrading-from-3-3-4-to-4-0/20686/4 "2021-11-17T11:33:37Z")
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](https://forum.mautic.org/t/was-unable-to-update-to-4-0-heres-what-finally-worked-for-me/20980) ? ---
**Author:** ![vesper8](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mautic.org/vesper8/32/2043_2.png) [@vesper8](https://forum.mautic.org/u/vesper8)\ **Post date:** [November 17, 2021, 12:06pm UTC](https://forum.mautic.org/t/error-while-upgrading-from-3-3-4-to-4-0/20686/5 "2021-11-17T12:06:22Z")
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](https://forum.mautic.org/t/was-unable-to-update-to-4-0-heres-what-finally-worked-for-me/20980): > > 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… ---
**Author:** ![martinkiely](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mautic.org/martinkiely/32/2651_2.png) [@martinkiely](https://forum.mautic.org/u/martinkiely)\ **Post date:** [November 22, 2021, 9:07pm UTC](https://forum.mautic.org/t/error-while-upgrading-from-3-3-4-to-4-0/20686/6 "2021-11-22T21:07:43Z")
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. ---
**Author:** ![marinardz21](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mautic.org/marinardz21/32/4722_2.png) [@marinardz21](https://forum.mautic.org/u/marinardz21)\ **Post date:** [March 17, 2022, 9:28pm UTC](https://forum.mautic.org/t/error-while-upgrading-from-3-3-4-to-4-0/20686/7 "2022-03-17T21:28:52Z")
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!! ---
**Author:** ![francesco](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mautic.org/francesco/32/82_2.png) [@francesco](https://forum.mautic.org/u/francesco)\ **Post date:** [May 1, 2022, 5:13pm UTC](https://forum.mautic.org/t/error-while-upgrading-from-3-3-4-to-4-0/20686/8 "2022-05-01T17:13:35Z")
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](https://github.com/mautic/mautic/issues/10913) ```auto # Apache 2.4+ # Deny access via HTTP requests to all PHP files. # # Require all denied # ``` ```auto 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; ```