Changes between Initial Version and Version 1 of Ticket #37425


Ignore:
Timestamp:
Oct 14, 2026, 3:04:24 AM (4 hours ago)
Author:
Tim Kedmenec
Comment:

Legend:

Unmodified
Added
Removed
Modified

  • Ticket #37425

    • Property Summary Adding a nullable ForeignKey on MySQL rebuilds the table (regression from #30108) → Adding a nullable ForeignKey on MySQL rebuilds the table

  • Ticket #37425 – Description

    initial v1  
    1 Since 3.0, add_field() puts the FK constraint in the same statement as the column:
     1On MySQL, adding a nullable ForeignKey to an existing table rebuilds the whole table. ADD COLUMN can be ALGORITHM=INSTANT. ADD FOREIGN KEY can't be INSTANT, and it
     2can't be INPLACE unless foreign_key_checks is off (error 1846: "Adding foreign keys needs foreign_key_checks=OFF"). Django leaves checks on, so the constraint step is
     3ALGORITHM=COPY.
     4
     5Before 3.0, add_field() ran the column add on its own and queued the FK in deferred_sql (code
     6(https://github.com/django/django/blob/9a0cc54524422dbdd9213e83a8ad7e8a4c13bd3e/django/db/backends/base/schema.py)). The column was instant, but the FK statement was
     7still a COPY. Since 3.0, 738faf9da2 (https://github.com/django/django/commit/738faf9da2a5cd03148a36375db80746c99c9623) (#30108) puts both in one statement:
    28
    39{{{#!sql
    … …  
    713}}}
    814
    9 On MySQL, ADD COLUMN can be ALGORITHM=INSTANT, but adding a foreign key can't (error 1846 with foreign_key_checks on). MySQL picks one algorithm per statement, so the
    10 combined statement is a full table rebuild. Simon pointed this out in comment:11 of #30108, but I couldn't find a follow-up ticket. On a ~100GB table, adding a
    11 nullable FK takes hours.
     15so the column add is no longer instant either (see comment:11 on #30108). On a ~100GB table this takes hours.
    1216
    13 Our workaround is a hand-written migration that splits the two steps:
     17Our workaround is a hand-written migration:
    1418
    1519{{{#!python
    … …  
    4852For a nullable FK, all existing rows are NULL, so there's nothing to validate and it's safe to disable foreign_key_checks for the constraint step.
    4953
    50 The patch makes the MySQL schema editor do this for nullable FKs with db_constraint=True. Non-null FKs keep the single statement. It includes a test that checks
     54The patch makes the MySQL schema editor do this for nullable FKs with db_constraint=True. Non-null FKs keep the single statement. The test checks that
    5155information_schema.innodb_tables.total_row_versions goes up by one, which is how an instant add shows up. A rebuild resets it to 0.
    5256
Back to Top