Flag of Ukraine
SymfonyCasts stands united with the people of Ukraine

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.

Start your All-Access Pass
Buy just this tutorial for $12.00

With a Subscription, click any sentence in the script to jump to that part of the video!

Login Subscribe

Our 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.

Leave a comment!

Login or Register to join the conversation
davidmintz Avatar
davidmintz Avatar davidmintz | posted 1 year ago

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.

1 Reply

Hey Elliott!

> So, ironically, here I am complaining because -- it works!

Haha, sorry! :D

Thanks for this tip! Yeah, it may depend on the DB provider you're using, and sometimes even on its version. Even if it works with zeros... you still may want to fix it in the migration to avoid that weird date in your data :)


Wael-G Avatar
Wael-G Avatar Wael-G | posted 2 months ago | edited

you could just delete the 2 first lines in the migration
$this->addSql('ALTER TABLE question_tag DROP FOREIGN KEY FK_339D56FB1E27F6BF');

$this->addSql('ALTER TABLE question_tag DROP FOREIGN KEY FK_339D56FBBAD26311');
and put them back once the migration is executed.
following your solution you already altered the data that already exists in your production environment


Hey @Wael-G,

Yes, you can do that but I prefer to put any queries I want to rollback in the down() method of the migration


Jesus Avatar
Jesus Avatar Jesus | posted 1 year ago | edited

Why don'y set default value?


  • @ORM\Column(type="datetime_immutable", options={"default":"CURRENT_TIMESTAMP"})
    private \DateTimeImmutable $taggedAt;

The migration generate this colum as:


sadikoff Avatar sadikoff | SFCASTS | Jesus | posted 1 year ago | edited

Hey Jesús G.

I'd say to have more control over timezones. Your app will have full control of date settings, so it will be easier to maintain and develop your app =)


MattHB Avatar

regarding cl 2:49

youre going to get fed up with my comments!


as mysql now supports transactions, is there any way to get the migrations bundle to take that into account and use it? Like Pgres?

or is there some reason that cant be done?


Hey Matt,

Good question! I suppose it's possible, but I've never tried it :) Please, take a look at "transactional: true" option of the bundle, see docs for more info: https://symfony.com/bundles... - it seems like something you're interested in.

I hope this helps!


Cat in space

"Houston: no signs of life"
Start the conversation!

This tutorial also works great for Symfony 6!

What PHP libraries does this tutorial use?

// composer.json
    "require": {
        "php": ">=8.1",
        "ext-ctype": "*",
        "ext-iconv": "*",
        "babdev/pagerfanta-bundle": "^3.3", // v3.3.0
        "composer/package-versions-deprecated": "^1.11", //
        "doctrine/doctrine-bundle": "^2.1", // 2.4.2
        "doctrine/doctrine-migrations-bundle": "^3.0", // 3.1.1
        "doctrine/orm": "^2.7", // 2.9.5
        "knplabs/knp-markdown-bundle": "^1.8", // 1.9.0
        "knplabs/knp-time-bundle": "^1.11", // v1.16.1
        "pagerfanta/doctrine-orm-adapter": "^3.3", // v3.3.0
        "pagerfanta/twig": "^3.3", // v3.3.0
        "sensio/framework-extra-bundle": "^6.0", // v6.2.1
        "stof/doctrine-extensions-bundle": "^1.4", // v1.6.0
        "symfony/asset": "5.3.*", // v5.3.4
        "symfony/console": "5.3.*", // v5.3.7
        "symfony/dotenv": "5.3.*", // v5.3.7
        "symfony/flex": "^1.3.1", // v1.17.5
        "symfony/framework-bundle": "5.3.*", // v5.3.7
        "symfony/monolog-bundle": "^3.0", // v3.7.0
        "symfony/runtime": "5.3.*", // v5.3.4
        "symfony/stopwatch": "5.3.*", // v5.3.4
        "symfony/twig-bundle": "5.3.*", // v5.3.4
        "symfony/validator": "5.3.*", // v5.3.14
        "symfony/webpack-encore-bundle": "^1.7", // v1.12.0
        "symfony/yaml": "5.3.*", // v5.3.6
        "twig/extra-bundle": "^2.12|^3.0", // v3.3.1
        "twig/string-extra": "^3.3", // v3.3.1
        "twig/twig": "^2.12|^3.0" // v3.3.2
    "require-dev": {
        "doctrine/doctrine-fixtures-bundle": "^3.3", // 3.4.0
        "symfony/debug-bundle": "5.3.*", // v5.3.4
        "symfony/maker-bundle": "^1.15", // v1.33.0
        "symfony/var-dumper": "5.3.*", // v5.3.7
        "symfony/web-profiler-bundle": "5.3.*", // v5.3.5
        "zenstruck/foundry": "^1.1" // v1.13.1