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:
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
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
postUp() describing things to do immediately before or after applying the queries in the
(* 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
(* 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
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 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
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
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:schema:update actually do