Flag of Ukraine
SymfonyCasts stands united with the people of Ukraine
This tutorial has a new version, check it out!

When Migrations Fail

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

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:

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

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

... 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()

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

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

38
Login or Register to join the conversation
Fernando A. Avatar
Fernando A. Avatar Fernando A. | posted 3 years ago

I createt the migration, and it did run without failing, just saying :)

4 Reply

Hey Fernando!

Haha, you were more lucky than we ;)

Cheers!

1 Reply
Default user avatar
Default user avatar Neulaender | victor | posted 3 years ago

Yeah but, WHY is it not failing. I even tried to give "nullable=false" in the PHPDoc. Still just doing it's migrations. Shouldn't it be halted, unless a consistent database state is given? Like all dates having an actual value?

Also: Why risk running "migrate" on production, if I have to run "DROP TABLE;" if sth. happens? Why not use "--dry-run"?

Reply

Hey Neulaender,

I think it just depends on your *SQL DB server and its version.

> Why not use "--dry-run"?

Well, probably because --dry-run does not actually execute queries? It's helpful to see if you have any syntax or logic error in your migrations, like calling invalid method, or calling service that does not exist if you ever do it in migrations, but it won't show you that the query will fail like in the case we had.

Cheers!

Reply

Hey JP,

No errors - even better, right? ;)

Cheers!

Reply
Default user avatar
Default user avatar Charlene Eboña | posted 2 years ago

I tried to run the php bin/console make:migration but come up with the error of [ERROR] Missing package: to use the make:migration command,

1 Reply
Default user avatar

i already run the composer require doctrine/doctrine-migrations-bundle "^3.0". But when trying again the php bin/console make:migration still come up with the [ERROR] Missing package: to use the make:migration command

1 Reply
Default user avatar

Have the same error with Symfony 5 and "symfony/orm-pack": "dev-master"

Reply

Hey Charlene Eboña

That's interesting. Did you start coding from scratch or did you download the course code from this page? Double check that you have installed symfony/orm-pack in your composer.json file, if you don't, then check if you have these 3 libraries installed


doctrine/orm
doctrine/doctrine-bundle
doctrine/doctrine-migrations-bundle
-1 Reply
Default user avatar
Default user avatar Charlene Eboña | MolloKhan | posted 2 years ago

Coding from scratch. Yes the symfony/orm-pack was already in my composer.json

Reply

Hmm, interesting, does it says which package is missing? I'm assuming that you have MakerBundle installed as well. Try running the command passing in the -vvv flag to get more debug information

Reply

Do you think it's a good idea to get help from another PHP library to add createdAt and updatedAt where we can do the same with Doctrine Lifecycle (PrePost, preUpdate)?

1 Reply

Hey Ahmad Mayahi

I like to manage myself the "createdAt" field because it is very easy but for the "updatedAt" field, I usually handle it with gedmo's timestampable extension: https://github.com/Atlantic...

Cheers!

Reply

Hey Diego Aguiar ,

updatedAt is just like createdAt it could be done by the lifecycle as follows:


/**
* @ORM\PreUpdate()
*/
public function setUpdatedAt(): void
{
$this->updatedAt = new \DateTime();
}

Reply

Oh yea, for sure but you don't need a lifecycle for setting up the "createdAt" field, you only have to set it at the constructor :)

1 Reply
Simon L. Avatar
Simon L. Avatar Simon L. | posted 1 year ago

Hi there !

If a migration fails, does the database return to its initial state? In other words: does Symfony automatically rolls back the struture of the database if something went wrong?

Reply

Hey Stileex,

Doctrine bundle does not do any rollbacks manually. If you have an automated deploy script - you should care about doing those rollbacks on failed migration execution, i.e. literally execute another Symfony command, but usually it's difficult to achieve on practice because you may have a few separate queries in your migration and because some of them might be successful in the beginning but failed in the middle of the migration - you would need to rollback only successful queries in down(), but it's difficult to know programmatically which failed and should be rollback-ed - so usually it's not enough to just call rollback automatically and usually it's done manually by devs in complex migrations.

Hope it helps!

Cheers!

1 Reply
Simon L. Avatar

Thanks :)

Reply
Robert Avatar

Hmm, im not getting any errors on do:mi:mi .. yay or nay ?

Reply

Hey Roby

I guess you have older version of mysql server, or it's configured to avoid this error. By default mysql server is configured with options NO_ZERO_DATE and NO_ZERO_IN_DATE if you don't have this two mods, then your server will not throws errors about wrong datetime.

Cheers!

Reply
Karin W. Avatar
Karin W. Avatar Karin W. | posted 2 years ago

Hi,
So, technically I'm not having a problem with this chapter, it's just that my errors in the terminal are so darn difficult to read. I'm using the PHPstorm build-in terminal and I just can't find where I can change the colours of the terminal. Believe me, I've tried, I've googled, and it's getting really annoying. Any chance you can help me out with this?

Reply

Hey Karin,

I'm not sure if it's possible for PhpStorm. I use a separate terminal called iTerm2 on Mac and it supports different colors. For PhpStorm, I think the most you can do is to change PhpStorm theme, but it will affect the color of file s content, so it depends on if you will like it or no. Anyway, I'd suggest you to use a separate console for executing commands, it's better IMO. In case you're interested how to change theme in PhpStorm - here it is: https://www.jetbrains.com/h...

Cheers!

1 Reply
Renaud G. Avatar
Renaud G. Avatar Renaud G. | posted 2 years ago

Hello,
I'm blocked right at the beginning of this course: the command make:migration does nothing and returns "No database changes were detected.". I can see that my Article entity is modified (and that the createdAt and updatedAt fields are present). When I remove the Gedmo sluggable stuff in the Article entity that we added in the previous chapter, the make:migration command works again and produces a migration file. I'm currently rocking Symfony 4.3.8 (I have installed all things manually since the beginning of the first tutorial and kept symfony up to date).

Reply
Renaud G. Avatar

I thought moving from stof to antishov's fork (composer require antishov/doctrine-extensions-bundle) would fix the issue for me. But it didn't do anyting appart fixing an annoying deprecation warning. So I'm still there with my modified entity and my make:migration command that sees no change.

Reply
Renaud G. Avatar

Turns out it was a just a cache issue and that executing 'bin/console cache:clear' after updating the entity was the only thing needed. Who knows why.

Reply

Hey Brain,

Oh, it sounds like a cache issue, yeah. Trying to clear the cache at first is always a good idea :) Glad it works for you now and you figured out how to solve it yourself! And thank you for sharing your solution with others!

Cheers!

Reply
Avraham M. Avatar
Avraham M. Avatar Avraham M. | posted 3 years ago

Hello!

When running make:migration,

VersionXXXXXXXXX.php file contains queries not intended at all.

There is notNull field without default value, it adds DEFAULT NULL , it produces query

$this->addSql('ALTER TABLE patients_allergies CHANGE patient_id patient_id INT UNSIGNED DEFAULT NULL');

Also, there is another field with default value 0,

migration file contains query to change it

$this->addSql('ALTER TABLE procedures CHANGE is_vaccination is_vaccination TINYINT(1) DEFAULT NULL');

I had and legacy database and created Entities using

https://symfony.com/doc/cur...

and it made good work - probably it should not make difference.

So, I suppose I should inspect before running,
is there a way to improve it and avoid producing unneeded queries in migration?

Thanks!

Reply

Hey Avraham,

> VersionXXXXXXXXX.php file contains queries not intended at all

Hm, what do you mean? You don't have indentation in that file? Or you mean the query is written in one line? Because it's ok, "make:migration" command does not expand queries to multilines.

Hm, what version of DB do you use? Because it's important to specify the same version for "server_version" option, see https://symfony.com/doc/cur... for more information. Otherwise, migrations might be generated a bit differently and it might cause generating migration instructions diff continuously on every run. Also, make sure you're on the latest version of maker bundle and doctrine migration bundle, sometimes bugs might happen, so upgrading to the newest version is a good idea to start.

I hope this helps!

Cheers!

Reply
Avraham M. Avatar
Avraham M. Avatar Avraham M. | victor | posted 3 years ago

Thanks Victor!

Reply
Avraham M. Avatar
Avraham M. Avatar Avraham M. | victor | posted 3 years ago

Hey Victor,
Thanks!

It is not about indentation - that's ok.

I mean VersionXXXXXXX.php file contains unneeded queries I didn't expect to appear,

some generated queries will try to make changes to my table structure I am not interested in.

There is notNull field patient_id without default value,
it is linked to id of patients table filed by Foreign key constraint.
Generated query adds DEFAULT NULL and after running it deletes notNull definition from patient_allergies patient_id field definition.

The annotation is like that:

/**
* @var Patient
*
* @ORM\ManyToOne(targetEntity="Patient")
* @ORM\JoinColumns({
* @ORM\JoinColumn(name="patient_id", referencedColumnName="id")
* })
*/

private $patient;

I see new query will change
$this->addSql('ALTER TABLE patients_allergies CHANGE patient_id patient_id INT UNSIGNED DEFAULT NULL');

If it sounds not practical question, I will sure just inspect the VersionXXXXXXXX.php before running migration.
Thanks!

Reply

Hey Avraham,

Well, then yes, what I said about server_version is totally relevant. Also, it depends if you mix the same table for entity data and unrelated to the entity data. I mean, if you have some fields that are not mapped to your entity and that you work with via doctrine DBAL - Doctrine migration might think that those fields should be removed, so better do not mix data in the same table. About foreign key constraints - yes, doctrine generate the key name using some algorithm, so if you were created those keys manually before - Doctrine will try to remove it and create a new one with a different name.

Anyway, you always have to check the final migration, especially if you're working on a legacy project and not all the columns are mapped to the entity properties.

Cheers!

Reply

Hello I tried several times, and it's failed.. I dropped the database and do it again but without success


> ALTER TABLE article CHANGE created_at created_at DATETIME NOT NULL, CHANGE updated_at updated_at DATETIME NOT NULL
Migration 20190109093240 failed during Execution. Error An exception occurred while executing 'ALTER TABLE article CHANGE created_at created_at DATETIME NOT NULL, CHANGE updated_at updated_at DATETIME NOT NULL':

SQLSTATE[22004]: Null value not allowed: 1138 Invalid use of NULL value

In AbstractMySQLDriver.php line 103:

An exception occurred while executing 'ALTER TABLE article CHANGE created_a
t created_at DATETIME NOT NULL, CHANGE updated_at updated_at DATETIME NOT N
ULL':

SQLSTATE[22004]: Null value not allowed: 1138 Invalid use of NULL value


In PDOConnection.php line 90:

SQLSTATE[22004]: Null value not allowed: 1138 Invalid use of NULL value


In PDOConnection.php line 88:

SQLSTATE[22004]: Null value not allowed: 1138 Invalid use of NULL value


Reply

Ok.. Then I configure the timestamp, reload the fixtures, and then retry the migration and it's work :


++ migrating 20190109093240

-> ALTER TABLE article CHANGE created_at created_at DATETIME NOT NULL, CHANGE updated_at updated_at DATETIME NOT NULL

++ migrated (0.25s)

------------------------

++ finished in 0.25s
++ 1 migrations executed
++ 1 sql queries

Reply

Hey Camille Seuvin
It's awesome that you solved this yourself.

BTW this error caused by records in table with `null` values in this fields, that's why configuring timestamps solved it.

Cheers!

Reply
Dmitriy Avatar
Dmitriy Avatar Dmitriy | posted 4 years ago

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. :(

Reply

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!

Reply
Cat in space

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

What PHP libraries does this tutorial use?

// composer.json
{
    "require": {
        "php": "^7.1.3",
        "ext-iconv": "*",
        "composer/package-versions-deprecated": "^1.11", // 1.11.99
        "knplabs/knp-markdown-bundle": "^1.7", // 1.7.0
        "knplabs/knp-time-bundle": "^1.8", // 1.8.0
        "nexylan/slack-bundle": "^2.0,<2.2.0", // v2.0.0
        "php-http/guzzle6-adapter": "^1.1", // v1.1.1
        "sensio/framework-extra-bundle": "^5.1", // v5.1.4
        "stof/doctrine-extensions-bundle": "^1.3", // v1.3.0
        "symfony/asset": "^4.0", // v4.0.4
        "symfony/console": "^4.0", // v4.0.14
        "symfony/flex": "^1.0", // v1.17.6
        "symfony/framework-bundle": "^4.0", // v4.0.14
        "symfony/lts": "^4@dev", // dev-master
        "symfony/orm-pack": "^1.0", // v1.0.6
        "symfony/twig-bundle": "^4.0", // v4.0.4
        "symfony/web-server-bundle": "^4.0", // v4.0.4
        "symfony/yaml": "^4.0" // v4.0.14
    },
    "require-dev": {
        "doctrine/doctrine-fixtures-bundle": "^3.0", // 3.0.2
        "easycorp/easy-log-handler": "^1.0.2", // v1.0.4
        "fzaninotto/faker": "^1.7", // v1.7.1
        "symfony/debug-bundle": "^3.3|^4.0", // v4.0.4
        "symfony/dotenv": "^4.0", // v4.0.14
        "symfony/maker-bundle": "^1.0", // v1.4.0
        "symfony/monolog-bundle": "^3.0", // v3.1.2
        "symfony/phpunit-bridge": "^3.3|^4.0", // v4.0.4
        "symfony/profiler-pack": "^1.0", // v1.0.3
        "symfony/var-dumper": "^3.3|^4.0" // v4.0.4
    }
}