Buy

When Migrations Fail

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

Login Subscribe

My other favorite Doctrine Extension behavior is timestampable. Go back to the library's documentation and click to view the Timestampable docs.

Oh, it's so nice: with this behavior, we can add $createdAt and $updatedAt fields to our entity, and they will be automatically set. Believe me, this will save your butt sometime in the future when something happens on your site you can't quite explain. A mystery!

Adding the createdAt & updatedAt Fields

Ok, step 1: we need those 2 new fields. We could easily add them by hand, but let's generate them instead. Run:

php bin/console make:entity

Update the Article entity and add createdAt, as a datetime, and say "no" to nullable: this should always be populated. Do the same thing for updatedAt: it should also always be set: it will match createdAt when the entity is first saved. Hit enter to finish adding fields:

190 lines src/Entity/Article.php
... lines 1 - 10
class Article
{
... lines 13 - 55
/**
* @ORM\Column(type="datetime")
*/
private $createdAt;
/**
* @ORM\Column(type="datetime")
*/
private $updatedAt;
... lines 65 - 165
public function getCreatedAt(): ?\DateTimeInterface
{
return $this->createdAt;
}
public function setCreatedAt(?\DateTimeInterface $createdAt): self
{
$this->createdAt = $createdAt;
return $this;
}
public function getUpdatedAt(): ?\DateTimeInterface
{
return $this->updatedAt;
}
public function setUpdatedAt(?\DateTimeInterface $updatedAt): self
{
$this->updatedAt = $updatedAt;
return $this;
}
}

Next, you guys know the drill, run:

php bin/console make:migration

Awesome! Move over and open that file. Yep, this looks good: an ALTER TABLE to add created_at and updated_at:

29 lines src/Migrations/Version20180418130337.php
... lines 1 - 2
namespace DoctrineMigrations;
use Doctrine\DBAL\Migrations\AbstractMigration;
use Doctrine\DBAL\Schema\Schema;
/**
* Auto-generated Migration: Please modify to your needs!
*/
class Version20180418130337 extends AbstractMigration
{
public function up(Schema $schema)
{
// this up() migration is auto-generated, please modify it to your needs
$this->abortIf($this->connection->getDatabasePlatform()->getName() !== 'mysql', 'Migration can only be executed safely on \'mysql\'.');
$this->addSql('ALTER TABLE article ADD created_at DATETIME NOT NULL, ADD updated_at DATETIME NOT NULL');
}
public function down(Schema $schema)
{
// this down() migration is auto-generated, please modify it to your needs
$this->abortIf($this->connection->getDatabasePlatform()->getName() !== 'mysql', 'Migration can only be executed safely on \'mysql\'.');
$this->addSql('ALTER TABLE article DROP created_at, DROP updated_at');
}
}

Go back to your terminal, and run it:

php bin/console doctrine:migrations:migrate

When a Migration Fails

And... great! Wait, woh! No! It exploded! Check it out:

Incorrect datetime value: 0000-00-00

Hmm. The problem is that our database already has articles. So when MySQL tries to create a new datetime column that is not nullable, it has a hard time figuring out what value to put for those existing rows!

Yep, unfortunately, sometimes, migrations fail. And fixing them is a delicate process. Let's think about this. What we really want to do is create those columns, but allow them to be null... at first. Then, we can update both fields to today's date. And, then we can use another ALTER TABLE query to finally make them not null.

That's totally doable! And we just need to modify the migration by hand. Instead of NOT NULL, use DEFAULT NULL. Do the same for updated_at:

30 lines src/Migrations/Version20180418130337.php
... lines 1 - 10
class Version20180418130337 extends AbstractMigration
{
public function up(Schema $schema)
{
// this up() migration is auto-generated, please modify it to your needs
$this->abortIf($this->connection->getDatabasePlatform()->getName() !== 'mysql', 'Migration can only be executed safely on \'mysql\'.');
$this->addSql('ALTER TABLE article ADD created_at DATETIME DEFAULT NULL, ADD updated_at DATETIME DEFAULT NULL');
... line 19
}
... lines 21 - 28
}

Below that, call $this->addSql() with:

UPDATE article SET created_at = NOW(), updated_at = NOW()

30 lines src/Migrations/Version20180418130337.php
... lines 1 - 10
class Version20180418130337 extends AbstractMigration
{
public function up(Schema $schema)
{
// this up() migration is auto-generated, please modify it to your needs
$this->abortIf($this->connection->getDatabasePlatform()->getName() !== 'mysql', 'Migration can only be executed safely on \'mysql\'.');
$this->addSql('ALTER TABLE article ADD created_at DATETIME DEFAULT NULL, ADD updated_at DATETIME DEFAULT NULL');
$this->addSql('UPDATE article SET created_at = NOW(), updated_at = NOW()');
}
... lines 21 - 28
}

We still need another query to change things back to not null, but don't do it yet: we can be lazy. Instead, find your terminal: let's try the migration again. But, wait! You may or may not be able to re-run the migration immediately. In this case, the original migration had only one query, and that one query failed. This means that no part of the migration executed successfully.

But sometimes, a migration may contain multiple lines of SQL. And, if the second or third line fails, then, well, we're in a really weird state! In that situation, if we tried to rerun the migration, the first line would execute for the second time, and it would probably fail.

Basically, when a migration fails, it's possible that your migration system is now in an invalid state. When that happens, you should completely drop your database and start over. You can do that with:

php bin/console doctrine:database:drop --force

And then:

php bin/console doctrine:database:create

And then you can migrate. Anyways, we are not in an invalid state: so we can just re-try the migration:

php bin/console doctrine:migrations:migrate

And this time, it works! To finally make the fields not nullable, we can ask Doctrine to generate a new migration:

php bin/console make:migration

Go check it out!

29 lines src/Migrations/Version20180418130730.php
... lines 1 - 2
namespace DoctrineMigrations;
use Doctrine\DBAL\Migrations\AbstractMigration;
use Doctrine\DBAL\Schema\Schema;
/**
* Auto-generated Migration: Please modify to your needs!
*/
class Version20180418130730 extends AbstractMigration
{
public function up(Schema $schema)
{
// this up() migration is auto-generated, please modify it to your needs
$this->abortIf($this->connection->getDatabasePlatform()->getName() !== 'mysql', 'Migration can only be executed safely on \'mysql\'.');
$this->addSql('ALTER TABLE article CHANGE created_at created_at DATETIME NOT NULL, CHANGE updated_at updated_at DATETIME NOT NULL');
}
public function down(Schema $schema)
{
// this down() migration is auto-generated, please modify it to your needs
$this->abortIf($this->connection->getDatabasePlatform()->getName() !== 'mysql', 'Migration can only be executed safely on \'mysql\'.');
$this->addSql('ALTER TABLE article CHANGE created_at created_at DATETIME DEFAULT NULL, CHANGE updated_at updated_at DATETIME DEFAULT NULL');
}
}

Ha! Nice! It simply changes the fields to be NOT NULL. Run it!

php bin/console doctrine:migrations:migrate

And we are good! Now, back to Timestampable!

Leave a comment!

  • 2018-05-21 Victor Bocharsky

    Hey Dmitriy,

    Uh oh, you certainly should avoid running this command on production :)

    First of all, you need to understand why this error happened: whether due to bad migration logic or due to developer error like somehow running the same migration twice. When you understand it - you will see what to do next. But here's some possible outcomes for you:
    - You can skip this migration by marking it as executed with: bin/console doctrine:migrations:version YYYYMMDDHHMMSS --add - this will not execute the migration but just add it to the table, so on the next bin/console doctrine:migrations:migrate call this migration will be skipped. But make sure that all the SQL statements in the skipped migration were already applied. If not - you will need to manually execute missing one.
    - If that table was created mistakenly and do not hold any data - you can try to remove it manually and re-run the migration again to create it. But it depends on what else operations are supposed to be done that migration, probably just manually removing this table won't be enough.

    So, in any case, it's very bad and you should think about it and try to do your best to avoid this problem in the future, but for now you have to do extra work to make things working. As a tip, you can use bin/console doctrine:query:sql <sql> command to execute any SQL command on your production.

    Cheers!

  • 2018-05-20 Дмитрий Ченгаев

    What about if migrations fail on my production server?

    I have error "Table '...' already exists"

    Command "php bin/console doctrine:database:drop --force" will remove all my data on production server. :(