Setting defaults in the DB to support zero-downtime migrations

[Paul Tiplady]

It can be quite fiddly to support zero-downtime DB migrations in Django. For example see https://dev.mysql.com/doc/refman/5.7/en/data-type-defaults.html for tricks in Postgres; I'll refer to MySQL herein.

In general the sequence is to first upgrade the DB schema to the new version, while keeping the old version of the application running. This works if the DB fields have a `DEFAULT NULL`, or if strict mode is not enabled; in either case omitted fields are defaulted to NULL or the implicit default, respectively  (under MySQL).

However it seems that manual SQL must be written in order to support adding fields that aren't nullable; since Django's ORM drops the DB-level default when the field is not nullable, there's a window after the schema migration, but before the application code has been upgraded, where the old-version code could try to write a None to the DB, while the new-version DB schema doesn't support it.

For example, a NullBooleanField(default=None) produces this SQL:

`bool_field` tinyint(1) DEFAULT NULL,

Whereas a BooleanField(default=False) (or NullBooleanField with a default) produces:

`bool_field` tinyint(1),

This is the same for the other field types I've investigated; Django explicitly removes the default from the DB when migrating from a Nullable Field to a non-Nullable one.

In MySQL using non-strict mode, this would often go unnoticed (since MySQL coerces NULL to the implicit default in that case), but under strict mode is recommended, that option is not available (per https://dev.mysql.com/doc/refman/5.7/en/data-type-defaults.html).

Achieving zero-downtime migrations would be much easier if the default value was set in the DB; is there a reason that Django does not do this? Even if this was an optional flag which only worked for literal values (i.e. not functions), it would seem to be a very useful feature. (e.g. `Field(set_default_in_db=True)`).

Indeed it seems to me that (based on the paucity of articles/documentation around hitless DB migrations) currently most Django users are likely incurring brief outages every time they perform a migration (perhaps without realizing it), whereas if DB-level defaults were the default behaviour, writing hitless DB migrations would require a lot less thought.

I'm sure there's been discussion of this before, so apologies in advance for being unable to locate that thread; I'd be interested in any thoughts around this.

Cheers,
Paul

[Ryan Hiebert]

I've found other places where things are fiddly for production migrations as well. Migrating a nullable to a non-nullable field is one of a range of cases where the migration cannot be run until the code has been fully deployed to no longer write nulls to the database. A similar case, that's even a little bit more tricky, is removing fields. It's not sufficient in general to just avoid using the field in your user code. You also have to remove the field from the model itself, or else the model in many cases will, by default, attempt to gather that field from the database and cause an error.

I'm using Heroku, and that meant that I needed to have a first commit that removed the field from the model, and leave the migration for a second commit (and deployment). This meant that there was a time where things were inconsistent. In the case of removing the null-ability of a field (assuming that in your situation it won't take the lock for longer than you can permit) they would not have to be split into a separate commit, but the code needs to be fully deployed before the migration can be run.

Things got even more complicated when I wanted to start using Heroku's Release Phase to automatically run migrations, which would automatically migrate _before_ the release is out, which doesn't work for either the to-not-null case or attempting to remove a field in a single commit. My team addressed that by introducing a concept of "safe" migrations, which for us is referring to the ability to run it before the associated code is released. Now we have a `safemigrate` command, which will only run migrations that are safe, based on the presence of a manual property added to the migration classes, and thus is acceptable to always run in the release phase. This allows us to mark migrations as unsafe when we need to ensure that a migration gets run manually after the associated code is deployed.

There are a couple complexities with the approach, especially related to dealing with sets of inter-dependent migrations where some are safe and some are unsafe, but the approach does seem to be working for us. A known challenge, but one that is, for our purposes, enough of an edge-case that we've not addressed it, is that the reverse of safe migrations are often unsafe, and the reverse of unsafe migrations are often safe. This is further complicated because in some cases, such as data migrations, the forward and reverse may _both_ be safe or unsafe, so it's not as simple as being the opposite of the forward direction. We have, so far, completely ignored this bit of complexity. We also have not attempted to automatically figure out whether a migration is safe or not based on the operations included.

If what we've done here is of interest to others, I will look into sharing the code to get further feedback on it. Please let me know if you're interested in seeing it.

--
You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-developers+unsubscribe@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at https://groups.google.com/group/django-developers.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/3581b394-2d16-485b-a836-8e28c1983470%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.