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 Migrationsinclude all migrations that have a migration file inapp/migrations, but no corresponding row in the tablemigrationsin the MySQL DB. -
Executed Unavailable Migrationsinclude all migrations that have a row inmigrationin the MySQL DB but no corresponding migration file inapp/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 