[3.2.x] Fixed #32425 -- Fixed adding nullable field with default on MySQL.

Thanks Simon Charette for the review.

Backport of d4ac23bee1c84d8e4610350202ac068fc90f38c0 from master

4 files changed, 37 insertions, 1 deletions

diff --git a/django/db/backends/base/schema.py b/django/db/backends/base/schema.py
index f879d59fa9..adaf6d7378 100644
--- a/django/db/backends/base/schema.py
+++ b/django/db/backends/base/schema.py
@@ -62,6 +62,7 @@ class BaseDatabaseSchemaEditor:
     sql_alter_column_not_null = "ALTER COLUMN %(column)s SET NOT NULL"
     sql_alter_column_default = "ALTER COLUMN %(column)s SET DEFAULT %(default)s"
     sql_alter_column_no_default = "ALTER COLUMN %(column)s DROP DEFAULT"
+    sql_alter_column_no_default_null = sql_alter_column_no_default
     sql_alter_column_collate = "ALTER COLUMN %(column)s TYPE %(type)s%(collation)s"
     sql_delete_column = "ALTER TABLE %(table)s DROP COLUMN %(column)s CASCADE"
     sql_rename_column = "ALTER TABLE %(table)s RENAME COLUMN %(old_column)s TO %(new_column)s"
@@ -891,7 +892,13 @@ class BaseDatabaseSchemaEditor:
             params = []
 
         new_db_params = new_field.db_parameters(connection=self.connection)
-        sql = self.sql_alter_column_no_default if drop else self.sql_alter_column_default
+        if drop:
+            if new_field.null:
+                sql = self.sql_alter_column_no_default_null
+            else:
+                sql = self.sql_alter_column_no_default
+        else:
+            sql = self.sql_alter_column_default
         return (
             sql % {
                 'column': self.quote_name(new_field.column),
diff --git a/django/db/backends/mysql/schema.py b/django/db/backends/mysql/schema.py
index b7268e2b23..9bbcffc899 100644
--- a/django/db/backends/mysql/schema.py
+++ b/django/db/backends/mysql/schema.py
@@ -10,6 +10,7 @@ class DatabaseSchemaEditor(BaseDatabaseSchemaEditor):
     sql_alter_column_not_null = "MODIFY %(column)s %(type)s NOT NULL"
     sql_alter_column_type = "MODIFY %(column)s %(type)s"
     sql_alter_column_collate = "MODIFY %(column)s %(type)s%(collation)s"
+    sql_alter_column_no_default_null = 'ALTER COLUMN %(column)s SET DEFAULT NULL'
 
     # No 'CASCADE' which works as a no-op in MySQL but is undocumented
     sql_delete_column = "ALTER TABLE %(table)s DROP COLUMN %(column)s"
diff --git a/django/db/backends/oracle/schema.py b/django/db/backends/oracle/schema.py
index 3197fcc892..cf875fc5e3 100644
--- a/django/db/backends/oracle/schema.py
+++ b/django/db/backends/oracle/schema.py
@@ -14,6 +14,7 @@ class DatabaseSchemaEditor(BaseDatabaseSchemaEditor):
     sql_alter_column_not_null = "MODIFY %(column)s NOT NULL"
     sql_alter_column_default = "MODIFY %(column)s DEFAULT %(default)s"
     sql_alter_column_no_default = "MODIFY %(column)s DEFAULT NULL"
+    sql_alter_column_no_default_null = sql_alter_column_no_default
     sql_alter_column_collate = "MODIFY %(column)s %(type)s%(collation)s"
 
     sql_delete_column = "ALTER TABLE %(table)s DROP COLUMN %(column)s"
diff --git a/tests/schema/tests.py b/tests/schema/tests.py
index b994c252d9..4522f5faf4 100644
--- a/tests/schema/tests.py
+++ b/tests/schema/tests.py
@@ -3095,6 +3095,33 @@ class SchemaTests(TransactionTestCase):
             if connection.features.can_introspect_default:
                 self.assertIsNone(field.default)
 
+    def test_add_field_default_nullable(self):
+        with connection.schema_editor() as editor:
+            editor.create_model(Author)
+        # Add new nullable CharField with a default.
+        new_field = CharField(max_length=15, blank=True, null=True, default='surname')
+        new_field.set_attributes_from_name('surname')
+        with connection.schema_editor() as editor:
+            editor.add_field(Author, new_field)
+        Author.objects.create(name='Anonymous1')
+        with connection.cursor() as cursor:
+            cursor.execute('SELECT surname FROM schema_author;')
+            item = cursor.fetchall()[0]
+            self.assertIsNone(item[0])
+            field = next(
+                f
+                for f in connection.introspection.get_table_description(
+                    cursor,
+                    'schema_author',
+                )
+                if f.name == 'surname'
+            )
+            # Field is still nullable.
+            self.assertTrue(field.null_ok)
+            # The database default is no longer set.
+            if connection.features.can_introspect_default:
+                self.assertIn(field.default, ['NULL', None])
+
     def test_alter_field_default_dropped(self):
         # Create the table
         with connection.schema_editor() as editor: