When a Migration Falls Apart
Keep on Learning!
If you liked what you've learned so far, dive in! Subscribe to get access to this tutorial plus video, code and script downloads.
With a Subscription, click any sentence in the script to jump to that part of the video!
Login SubscribeOur new migration file is pretty cool! We created an entirely new entity - QuestionTag - with a relationship to Question and a relationship to Tag. But this massive change in PHP didn't translate into much in the database: the migration basically adds new id and taggedAt columns to the question_tag table.
Unfortunately... when we executed that migration, it blew up in our face! The reason is that the question_tag table already has data in it! And so when we told the table to add a new tagged_at DATETIME column that can't be NULL... it didn't know what value to use for the existing rows in the table! And so... explosion!
If you haven't actually deployed your question_tag table to production, then this isn't a real problem... because, when you do finally deploy, this table won't have any data in it the moment that this executes. In that case, all you need to do is fix your local setup. You can do this dropping your database, recreating it... and running all of your migrations from the beginning. We'll see how to do that in a minute.
But... I want to pretend like our question_tag table has been deployed to the production and it does have data in it.. and I want to fix this migration so that it does not explode when we run it on production.
Fixing the Migration
The fix for the migration is fairly simple. When we add the tagged_at column, instead of saying DATETIME NOT NULL, say DATETIME DEFAULT NOW().
| // ... lines 1 - 12 | |
| final class Version20210907192236 extends AbstractMigration | |
| { | |
| // ... lines 15 - 19 | |
| public function up(Schema $schema): void | |
| { | |
| // ... lines 22 - 24 | |
| $this->addSql('ALTER TABLE question_tag ADD id INT AUTO_INCREMENT NOT NULL, ADD tagged_at DATETIME DEFAULT NOW() COMMENT \'(DC2Type:datetime_immutable)\', DROP PRIMARY KEY, ADD PRIMARY KEY (id)'); | |
| // ... lines 26 - 27 | |
| } | |
| // ... lines 29 - 41 | |
| } |
This is a temporary change: it will allow us to add that new column and give the existing rows a default value. Then, in a separate migration that we'll create in a few minutes, we can then safely make that column NOT NULL.
How to Test a Half-Executed Migration?
Cool! But... now that we've fixed the migration, how can we run it again? Let's try the obvious:
symfony console doctrine:migrations:migrate
It fails again! But for a different reason: dropping the foreign key failed because that's already gone.
Here's the problem. When we first ran this migration, the top two lines did successfully execute. And then the third line failed. This means that our migration is in a strange state... a, sort of, "half" executed state.
If you're using PostgreSQL, this is not a problem. Each migration is wrapped in a transaction. This means that, if any of the queries fail, they will all be reverted. Unfortunately, while this works great in PostgreSQL, MySQL does not support that coolness. So if you are using PostgreSQL, you rock and the migration command we ran a minute ago did work for you.
But if you're using MySQL, then you're in our reality. To test this migration, we need to do a, sort of, "manual" rollback: we need to put our database back into the state it was before we ran this migration. Once we've done that, then we'll run this migration again to make sure it works.
At your terminal, run;
git status
Before we created the QuestionTag entity, I committed everything to git. Add the new changes we've done since then:
git add src migrations/
And then run
git status
again. Yup: everything is ready to be committed. But instead of committing, remove these changes with:
git stash
If you're not familiar with the git stash command, it removes all of the changes from your project and "stashes" them somewhere so we can put them back later. If we check the code... the migration is gone... and so is the new entity. Now that our code is back to its "old" state, we can reset the database.
Start by dropping it entirely:
symfony console doctrine:database:drop --force
And then re-recreate it:
symfony console doctrine:database:create
And then migrate:
symfony console doctrine:migrations:migrate
This executes all the migrations up to this point, which is back when we had the ManyToMany relationship. Finally, to mimic production - where we have data in the join table - run:
symfony console doctrine:fixtures:load
Perfect! Now bring back all of our changes by saying:
git stash pop
Awesome! Finally, we can now test the new migration:
symfony console doctrine:migrations:migrate
Migrating Again to add NOT NULL
This time... it works! It added that new column without failing. The only small problem is that, right now, in the database, the tagged_at column is not required: it does allow null... which is not what we want. But fixing this is easy: ask Doctrine to generate one more migration:
symfony console make:migration
This is really cool: it looked at the new QuestionTag entity, realized that the tagged_at column isn't set up correctly, and generated a new migration with ALTER TABLE question_tag CHANGE tagged_at to NOT NULL.
| // ... lines 1 - 12 | |
| final class Version20210907192620 extends AbstractMigration | |
| { | |
| // ... lines 15 - 19 | |
| public function up(Schema $schema): void | |
| { | |
| // this up() migration is auto-generated, please modify it to your needs | |
| $this->addSql('ALTER TABLE question_tag CHANGE tagged_at tagged_at DATETIME NOT NULL COMMENT \'(DC2Type:datetime_immutable)\''); | |
| } | |
| // ... lines 25 - 30 | |
| } |
Run this:
symfony console doctrine:migrations:migrate
And... it works!
So refactoring the relationship between Question and Tag to include a new QuestionTag entity didn't really change the structure of the database... though this migration did cause a headache. However, in PHP, how we save and use this relationship did just change substantially. So next, let's update our fixtures to work with the new structure.
I am using MariaDb 10.1.14 wtih docker-compose and a curious thing happens: the database accepts 0000-00-00 00:00:00 and the migration does not fail )-: So, ironically, here I am complaining because -- it works! Just kidding. I will pretend it fails and keep following the program.