Cannot perform schema migrations without downtime using MySQL in strict mode

[Paul Tiplady]

Performing schema migrations without downtime requires a fair bit of forethought, for example see

​http://pankrat.github.io/2015/django-migrations-without-downtimes/

In summary, the process for adding a new field is:

1. App and DB at version N
2. Migrate DB schema to version N+1
3. Update app server instances to version N+1

Problem:

At step 2, the version N app must be compatible with the N+1 database schema. This doesn't work in MySQL strict mode when adding many types of field - from my (non-exhaustive research):

• a non-nullable field (e.g. BooleanField)
• setting a nullable field to be non-nullable
• setting a nullable field to have a default (e.g. NullBooleanField(default=False))
• any form of CharField

since Django does not support setting default values in the DB. In MySQL strict mode, an insert that omits a value from a no-default field produces an error ("Field doesn't have a default value").

This is not a problem in Rails, since that framework does write default values into the DB schema; for a look at zero-downtime schema migrations in Rails-land, see:

​https://samsaffron.com/archive/2018/03/22/managing-db-schema-changes-without-downtime

Proposal:

I'm sure this has been discussed previously but I can't find any justification in the docs for the design decision that Django does not write defaults to the DB; is there any room to revisit that decision? It does seems to make it challenging (and in many cases impossible) to perform hitless migrations, which is a big issue in the modern paradigm of continuous delivery.

I would like to make it possible to write safe DB migrations without having to manually write SQL for every migration (since that negates a significant benefit of using Django's ORM, and is more error-prone).

I'd be interested in other suggestions for how to achieve this, but I propose to add configuration to the BaseDatabaseSchemaEditor method to make it possible to leave the default value in the schema (e.g. to pass in a flag that would skip adding the "DROP DEFAULT" in add_field here: ​https://github.com/django/django/blob/master/django/db/backends/base/schema.py#L420-L428).

With that change in place there are a few possibilities for how to enable the new behaviour in an application --

1. globally through a settings flag (this seems hacky as it wouldn't keep track of the state of existing fields in the schema)
2. per-model class by adding a new Meta field specifying whether defaults should be written to the DB -- that way each model can be migrated individually and only as/when desired.

There may be other options and there are definitely more details to flesh out, but I'll pause here as I'm sure there's plenty to discuss in the above.

I should also add that I have some resource available to fix this issue that I'm seeing, and it's currently looking like I'll have to carry patched versions of the DatabaseSchemaEditor code (unless there's a way of avoiding changes to core Django that I'm missing). I'd be happy to work with you guys to shape these changes and contribute them back if there is interest in improving the story for zero-downtime migrations in the ORM.

[Tim Graham]

You can read #470 for the historical "wontfix" decision regarding database defaults. I just reopened that ticket in light of the discussion linked to in comment 17. Can we close this ticket in light of that?

[Paul Tiplady]

Thanks for the link -- I'm happy to carry on the discussion in that issue/thread.

[Vaibhav Awachat]

Currently using this ​https://github.com/3YOURMIND/django-add-default-value to run migrations without downtime using MySQL in strict mode.