Should doctrine:migration:migrate and doctrine:schema:update --force ever be run manually?

I’ve seen both doctrine:migration:migrate and doctrine:schema:update mentioned on this page, but nowhere else in the documentation and I’m a little unsure of what they do.

doctrine:migration:status shows that I have 134 “Executed Unavailable Migrations” and that that is apparently bad, but to be honst, I have no idea what that means.

I assume from looking at doctrine:schema:update --dump-sql that that command is for altering the database, but how often should that be done? Isn’t that something that is handled by Mautic itself when upgrading to a new version? Or not?

Can somebody please explain the role of these two commands?

Hi,

I’m preparing to upgrade from 3.0.1 to 3.1. I also ran the doctrine:migration:migrate and also got EXACTLY 134 “Executed Unavailable Migrations” !!

Any idea if this is normal or not ? If not how to fix this ?

Okay, it seems this is a general Symfony thing (the PHP framework powering Mautic). I digged some more and read up on things and to my understanding this is the explanation:

The doctrine:migrations commands are handled by the DoctrineMigrationsBundle and is essentially a safer way of updating the database compared to the doctrine:schema:update. As for what doctrine:schema:update is, we need to go back a bit and look at how Mautic (powered by Symfony) handles the MySQL database:

Mautic is using ORM (Object Relational Mapping) as a way for the PHP code to relate to the MySQL database structure - as described here this is offered by the Symfony DoctrineBundle which integrates with a PHP tool called Doctrine.

Mapping information is nothing but "metadata”. It is a collection of rules that informs Doctrine ORM exactly how specific PHP classes and their properties are mapped to a specific database table in MySQL. Somewhere in the Mautic source code (not sure where), there is mapping information defining how the database structure is supposed to look for this version of Mautic (i.e. “Mautic 2.15.3 should have these tables with these features and these columns”). However, since Mautic is always being developed and improved, sometimes an upgrade to a newer version of Mautic needs to change things in the database. Since this is where all our data is stored, this should be handled with extreme care (we don’t want an update to accidentally corrupt the database!).

Now, the common way to handle this is that, for example, the code dictates 'The table called leads should have a column called email’ (as an example) and then, when the php command doctrine:schema:update is run, Doctrine ORM notices that the MySQL table leads is missing the required column email and thus, it creates it in the database. If you run doctrine:schema:update --dump-sql you can see what SQL queries the Doctrine ORM believes need to be run in order to bring the actual MySQL database up to par with the mapping information provided by Mautic (the --dump-sql flag means “Do not actually do anything - just output what you would do”).

So far so good. However, using doctrine:schema:update is kind of a one-way street. Once you run it, it might launch a bunch of queries with ALTER TABLE or DROP INDEX or all sorts of changes, and in the worst-case scenario where one of the changes broke something, you’d be screwed because you have no easy way of knowing what was altered and how to undo it.

This is where the DoctrineMigrationsBundle comes in! Instead of just firing off SQL queries blindly, the MigrationsBundle bundles the queries into migration files - these are PHP files that usually contain functions like up() and down(), the former of which contains the SQL queries to be run when the migration is applied and the latter of which contains the ‘reverse’ SQL queries to be run if the migration is ever reversed - thus enabling an easier way to get out of situations where an SQL query crashed something unexpectedly. The migration files can also contain preUp() and postUp() describing things to do immediately before or after applying the queries in the up() function.

(* It is worth noting that most, if not all, Mautic-related migrations do not provide a down(). The reason for this is that since the migrations are applied alongside actual code upgrades of Mautic itself during upgrade, if you ever upgraded Mautic (+ applied the related migrations), but then later reverted some of the migrations, the Mautic code would get thoroughly confused.)

Each migration is stored in its own file in the folder app/migrations. If you are running Mautic 2.x, you will most likely have 134 migration files in that folder, dating from the period 2015-2018. When Mautic 3 was introduced, the team got rid of a lot of the old migration files (since we know they would already have been applied during the upgrade process if people upgraded from 2.16.3 - and if people started with a new server from scratch with Mautic 3, hey, there would be nothing to migrate anyway). So in the Mautic 3.x codebase there are currently way fewer migrations available.

Whenever you run doctrine:migrations:status, the code reads the files in app/migrations and shows them as Available Migrations. Alongside this, it will read the table migrations in the database, which shows how many of them you have already applied (either through ordinary Mautic upgrade or, in rare cases of troubleshooting, manually with doctrine:migrations:status). Any migration files that do not show up in the migrations table is labeled as a New Migration and will be applied if you ever run doctrine:migrations:migrate

(* note that the migration files themselves also contain checks to see if they have already been applied, so even if you run some old migrations the first time after, say, a fresh install, you might often see Schema includes this migration letting you know that the migration wasn’t needed. It still counts as executed).

That accounts for three of the numbers output by doctrine:migrations:status - Executed Migrations, Available Migrations and New Migrations. But what about Executed Unavailable Migrations (i.e. the one with the scary red color)? Simply put, it is the reverse logic of New Migrations:

  • New Migrations include all migrations that have a migration file in app/migrations, but no corresponding row in the table migrations in the MySQL DB.
  • Executed Unavailable Migrations include all migrations that have a row in migration in the MySQL DB but no corresponding migration file in app/migrations.

Despite the red color, it is rarely something to be concerned about. It is just the MigrationsBundle alerting us that “Apparently we have applied a migration called Versionxxxxxx, but I have no idea what that migration was”. It is fixed by making sure the number of rows in the migrations table and the number of files in app/migrations line up (and are named the same, obviously).

Finally, keep in mind that except when it actually applies migrations, the DoctrineMigrationsBundle has no idea whether the database itself actually looks like. Thus, it is STRONGLY discouraged to alter stuff in the MySQL database on your own as you might end up in trouble down the road.
If you ever managed to make some dumb, custom changes to the database end just want to have it return to the ‘vanilla’ Mautic structure, you can try running doctrine:schema:update --force --complete, which tells the Doctrine ORM to throw out anything in the database which doesn’t match the mapping information provided by Mautic. This will cause any custom fields, tables, indexes etc. that you added directly in the database (as opposed to in the Mautic interface) to be dropped, as well as altering everything else to match the mapping information provided. Keep in mind that this will (ironically) cause the migrations table to be dropped, and thus, the next time you run doctrine:migrations:status, all migration files in app/migrations will be considered New Migrations.
Thus, running doctrine:schema:update --force --complete followed by doctrine:migrations:migrate should theoretically bring you back to a vanilla Mautic database schema with your data intact (except for data in whatever columns that potentially got dropped). Obviously, you should take plenty of backups before attempting this as this is very much a ‘last resort’.

So, that was a long and rambley description based upon what I could find and test myself. Hopefully, this gives a pretty good idea of what doctrine:migrations:migrate and doctrine:schema:update actually do :slightly_smiling_face:

1 Like

Thank you very much for the detailed explanation, very much appreciated.

So if I understood correctly the 134 “Executed Unavailable Migrations” are here only because there’s still entries in the migrations table which does not correspond to a app/migrations/VersionXXXXXXXX.php file.

So I took the decision to cleanup the migrations table (yes I know, despite the warning, crazy…)

So first a full DB backup:
mysqldump -u root -p --default-character-set=utf8mb4 mauticdb -r 20200902-mauticdb.sql

Then a small backup of the migrations table only:
mysqldump -u root -p --default-character-set=utf8mb4 mauticdb migrations -r 20200902-migrations.sql

And, better safe than sorry, a full backup of the website:
tar czvf 20200902-mmm.mysite.com.tgz /var/www/mmm.mysite.com

Migration status before my change in the DB:

# 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

Then in the MySQL DB:

MariaDB [mauticdb]> select count(*) from migrations;
+----------+
| count(*) |
+----------+
|      147 |
+----------+
1 row in set (0.001 sec)

MariaDB [mauticdb]> select count(*) from migrations where executed_at is null;
+----------+
| count(*) |
+----------+
|      136 |
+----------+
1 row in set (0.001 sec)

MariaDB [mauticdb]> select * from migrations;
+----------------+---------------------+
| version        | executed_at         |
+----------------+---------------------+
| 20150402000000 | NULL                |
| 20150504000000 | NULL                |
| 20150521000000 | NULL                |
| 20150718000000 | NULL                |
| 20150724000000 | NULL                |
| 20150801000000 | NULL                |
| 20150829000000 | NULL                |
| 20150901000000 | NULL                |
| 20151022000000 | NULL                |
| 20151120000000 | NULL                |
| 20151207000000 | NULL                |
| 20160114000000 | NULL                |
| 20160225000000 | NULL                |
| 20160405000000 | NULL                |
| 20160414000000 | NULL                |
| 20160420000000 | NULL                |
| 20160426000000 | NULL                |
...
| 20181017154600 | NULL                |
| 20181111095447 | 2020-08-03 14:49:15 |
| 20181128122944 | NULL                |
| 20181129105419 | NULL                |
| 20181204000000 | NULL                |
| 20190704154940 | NULL                |
| 20190715065013 | NULL                |
| 20190724110039 | NULL                |
| 20191126093923 | 2020-08-03 14:49:15 |
| 20191219155630 | 2020-08-03 14:49:16 |
| 20200211095409 | 2020-08-03 16:17:58 |
| 20200212141530 | 2020-08-03 16:17:58 |
| 20200220172041 | 2020-08-03 16:17:58 |
| 20200227110431 | 2020-08-03 16:17:58 |
| 20200302164801 | 2020-08-03 16:17:58 |
| 20200422144300 | 2020-08-03 16:17:58 |
| 20200805185714 | 2020-08-28 16:01:17 |
| 20200815153711 | 2020-08-28 16:01:17 |
+----------------+---------------------+
147 rows in set (0.001 sec)

So there’s 136 rows with “executed_at” set to null. Only 134 migrations are reported as “Executed Unavailabe
I looked into app/migrations directory and found 2 “Version” files having a matching number in the migrations table but with the executed_at set to null.

So with the following query I count match the 134 “Executed Unavailable Migrations”:

MariaDB [mauticdb]> select count(*) from migrations where executed_at is null and version not like '20190704154940' and version not like '20190724110039';
+----------+
| count(*) |
+----------+
|      134 |
+----------+
1 row in set (0.001 sec)

Then I just deleted them:

MariaDB [mauticdb]> delete from migrations where executed_at is null and version not like '20190704154940' and version not like '20190724110039';
Query OK, 134 rows affected (0.002 sec)

MariaDB [mauticdb]> select * from migrations;
+----------------+---------------------+
| version        | executed_at         |
+----------------+---------------------+
| 20181111095447 | 2020-08-03 14:49:15 |
| 20190704154940 | NULL                |
| 20190724110039 | NULL                |
| 20191126093923 | 2020-08-03 14:49:15 |
| 20191219155630 | 2020-08-03 14:49:16 |
| 20200211095409 | 2020-08-03 16:17:58 |
| 20200212141530 | 2020-08-03 16:17:58 |
| 20200220172041 | 2020-08-03 16:17:58 |
| 20200227110431 | 2020-08-03 16:17:58 |
| 20200302164801 | 2020-08-03 16:17:58 |
| 20200422144300 | 2020-08-03 16:17:58 |
| 20200805185714 | 2020-08-28 16:01:17 |
| 20200815153711 | 2020-08-28 16:01:17 |
+----------------+---------------------+
13 rows in set (0.002 sec)

Migrations status after this:

# 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:                                13
    >> Executed Unavailable Migrations:                    0
    >> Available Migrations:                               13
    >> New Migrations:                                     0

Then I though maybe the 2 migrations were not executed, so I tried this:

# php bin/console doctrine:migration:migrate

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.


                    Mautic Migrations


WARNING! You are about to execute a database migration that could result in schema changes and data loss. Are you sure you wish to continue? (y/n)y
No migrations to execute.

Weird. Anyway, it looks better now. :slight_smile:

Any thought ? Is what I did OK or not ?

1 Like

I’m by no means an expert, but it looks good to me :slight_smile:

1 Like

Sometimes these commands:

doctrine:migration:migrate
doctrine:schema:update –force

don’t seem to actually run, even though there are migration pending. If anyone has this problem I suggest checking your db_server_version in your config file. Mine was automatically set to ‘db_server_version’ => ‘5.5’, by my installation. When I changed this to ‘db_server_version’ => ‘10.3.23-MariaDB’, the commands worked as expected.

It seems Mautic installer cannot always identify MariaDB correctly.