134 Executed Unavailable Migrations - unable to doctrine:schema:update - foreign key constraint 'FK_3A18CA5A19EB6921'

maumatic · August 28, 2020, 4:13pm

Your software
My Mautic version is: 3.1.0
My PHP version is: 7.3.20
My MariaDB version is : 10.5

Your problem
My problem is: There are 134 Executed Unavailable Migrations

Here’s the migration status:

[root@vps mmm.mysite.com]#  php bin/console cache:clear

 // Clearing the cache for the prod environment with debug false


 [OK] Cache for the "prod" environment (debug=false) was successfully cleared.


[root@vps mmm.mysite.com]# php bin/console doctrine:migration:status

== Configuration

>> Name:                                               Mautic Migrations
>> Database Driver:                                    pdo_mysql
>> Database Host:                                      localhost
>> Database Name:                                      mauticdb
>> Configuration Source:                               manually configured
>> Version Table Name:                                 migrations
>> Version Column Name:                                version
>> Migrations Namespace:                               Mautic\Migrations
>> Migrations Directory:                               /var/www/mmm.mysite.com/app/migrations
>> Previous Version:                                   2020-08-05 18:57:14 (20200805185714)
>> Current Version:                                    2020-08-15 15:37:11 (20200815153711)
>> Next Version:                                       Already at latest version
>> Latest Version:                                     2020-08-15 15:37:11 (20200815153711)
>> Executed Migrations:                                147
>> Executed Unavailable Migrations:                    134
>> Available Migrations:                               13
>> New Migrations:                                     0
[root@vps mmm.mysite.com]#

Steps I have tried to fix the problem:

I’ve run doctrine:migration:migrate and doctrine:schema:update without success

[root@vps mmm.mysite.com]# php bin/console doctrine:migrations:migrate --no-interaction --env=prod --no-debug

[2020-08-28 18:08:42] Your database version (5.5.5-10.1.35-MariaDB) does not support generated columns. Upgrade at least to 10.2.6 and update `db_server_version` accordingly to get the speed improvements.

[2020-08-28 18:08:42]
[2020-08-28 18:08:42]                     Mautic Migrations
[2020-08-28 18:08:42]

[2020-08-28 18:08:42] WARNING! You have 134 previously executed migrations in the database that are not registered migrations.
[2020-08-28 18:08:42]     >> 2015-04-02 00:00:00 (20150402000000)
[2020-08-28 18:08:42]     >> 2015-05-04 00:00:00 (20150504000000)
[2020-08-28 18:08:42]     >> 2015-05-21 00:00:00 (20150521000000)
[2020-08-28 18:08:42]     >> 2015-07-18 00:00:00 (20150718000000)
[2020-08-28 18:08:42]     >> 2015-07-24 00:00:00 (20150724000000)
[2020-08-28 18:08:42]     >> 2015-08-01 00:00:00 (20150801000000)
...
...
[2020-08-28 18:08:42]     >> 2018-12-04 00:00:00 (20181204000000)
[2020-08-28 18:08:42]     >> 2019-07-15 06:50:13 (20190715065013)
[2020-08-28 18:08:42] No migrations to execute.
[root@vps mmm.mysite.com]# php bin/console doctrine:schema:update --force

 Updating database schema...


In AbstractMySQLDriver.php line 106:

  An exception occurred while executing 'ALTER TABLE oauth2_accesstokens CHANGE client_id client_id INT UNSIGNED NOT NULL, CHANGE user_id user_id INT UNSIGNED NOT NULL, CHANGE token token VARCHAR(191) NOT NULL, CHANGE expires_at expires_at BIGINT DEFAULT NULL, CHANGE s
  cope scope VARCHAR(191) DEFAULT NULL':

  SQLSTATE[HY000]: General error: 1832 Cannot change column 'client_id': used in a foreign key constraint 'FK_3A18CA5A19EB6921'


In PDOConnection.php line 80:

  SQLSTATE[HY000]: General error: 1832 Cannot change column 'client_id': used in a foreign key constraint 'FK_3A18CA5A19EB6921'


In PDOConnection.php line 75:

  SQLSTATE[HY000]: General error: 1832 Cannot change column 'client_id': used in a foreign key constraint 'FK_3A18CA5A19EB6921'


doctrine:schema:update [--complete] [--dump-sql] [-f|--force] [--em [EM]] [-h|--help] [-q|--quiet] [-v|vv|vvv|--verbose] [-V|--version] [--ansi] [--no-ansi] [-n|--no-interaction] [-e|--env ENV] [--no-debug] [--] <command>

[root@vps mmm.mysite.com]#

Any idea on how to fix that ?
Thanks for any hint.

madpet · September 2, 2020, 12:26pm

Hi @maumatic please see my detailed explanation over here. It should answer the question

maumatic · September 2, 2020, 2:01pm

Thank you very much @madpet

With your help I think I fixed the 134 “Executed Unavailable Migrations” problem.

Now I still have the doctrine:schema:update which is failing with the message:
General error: 1832 Cannot change column 'client_id': used in a foreign key constraint 'FK_3A18CA5A19EB6921'

When I run doctrine:schema:update --dump-sql I have more than 100 SQL statements to be executed to bring my database up to date. So far it’s working ok like that but I fear one day it won’t anymore, so I still would like to do this update.

Any hint about fixing this foreign key constraint ?

madpet · September 2, 2020, 3:07pm

I can’t help you with the foreign key error. I suppose you need to debug exactly what is going on. Once way is to use doctrine:schema:update --dump-sql to see what queries are needed and then simply run them manually one at a time in the MySQL console. Then eventually you will hit the one that causes the error and you may have a better idea of why.

Alternatively, you can try the doctrine:schema:update --force --complete to force the database to drop anything not reflected in the mapping information, but that might be very risky as it can cause you to lose data so I would recommend the first strategy first.

maumatic · September 3, 2020, 8:56am

So here’s what I tried to solve this foreign key problem: I disabled the foreign key checks in mysql and then tried to run the first SQL statement given by doctrine:schema:update --dump-sql

MariaDB [mauticdb]> SET foreign_key_checks = 0;
Query OK, 0 rows affected (0.001 sec)

MariaDB [mauticdb]> ALTER TABLE oauth2_accesstokens CHANGE client_id client_id INT UNSIGNED NOT NULL, CHANGE user_id user_id INT UNSIGNED NOT NULL, CHANGE token token VARCHAR(191) NOT NULL, CHANGE expires_at expires_at BIGINT DEFAULT NULL, CHANGE scope scope VARCHAR(191) DEFAULT NULL;
ERROR 1025 (HY000): Error on rename of './mauticdb/#sql-alter-3aa-6a7d2' to './mauticdb/oauth2_accesstokens' (errno: 150 "Foreign key constraint is incorrectly formed")
MariaDB [mauticdb]> desc oauth2_accesstokens;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| client_id  | int(11)      | NO   | MUL | NULL    |                |
| user_id    | int(11)      | NO   | MUL | NULL    |                |
| token      | varchar(255) | NO   | UNI | NULL    |                |
| expires_at | bigint(20)   | YES  |     | NULL    |                |
| scope      | varchar(255) | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+
6 rows in set (0.001 sec)

MariaDB [mauticdb]>

So it failed and I stopped here. Any hint on how to get the tables updated ?

madpet · September 4, 2020, 7:25am

Nope, sorry. I don’t know anything about oauth2 or foreign keys in this context. Hopefully someone else can assist.

nilyin · September 10, 2020, 6:59pm

I have same problem after upgrading to 3.1.0 from 3.0.2 and after trying to check database scheme.

.

In my case all is very strange as mysql doesn’t properly respond to
SET foreign_key_checks = 0;
and throws a lot of table constraints’ errors.

I have actually tried manually drop FOREIGN KEYS one by one for every table. it looks like that it works this way, but I gave up for a while as it takes too much time.

I plan to drop the full mautic database and restore from backup… hopefully it will change strange behavior, that I faced after db migration…

I use mysql Ver 14.14 Distrib 5.7.29-32, for Linux (x86_64) using 6.2

TBarregren · September 10, 2020, 7:52pm

I have the exact same problem. I couldn’t find any bug report, so I created one: https://github.com/mautic/mautic/issues/9185

madpet · September 21, 2020, 10:13am

@maumatic If you still have the problem, try looking here for an explanation and recommendations to fix: https://github.com/mautic/mautic/issues/9129#issuecomment-696010876

maumatic · February 7, 2021, 6:55am

I could finally update my database scheme, I explained how I did here: