I guess you could make a snapshot, remove the offending keys, do the migration, check consistency, add the keys back (if needed).
Sounds very familiar, similar errors were found when migrating from early M3 versions to late M3 versions for Mautic instances previously migrated from M2 to M3 due to a problem in the M2 to M3 migration code that was dormant until you needed to upgrade to a newer M3 version.
Could this still be related?
opened 09:56PM - 22 Feb 21 UTC
closed 10:47PM - 23 Feb 21 UTC
T1
bug
essential
installation
[//]: # ( 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. )
Similar issues:
Check Troubleshooting | Mautic and if it’s not related to this thread, please start a new one so we don’t mix several issues together.
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 T…
1 Like