MySQL Schema is different about the same class definitions. (depends on create table vs alter table)

[Jordan Bae]

Hi, My name is Jordan. When I delete the column, I found some picky points in Django.

MySQL Schema is different about the same class definitions.
MySQL Schemas are different about the nullable column default value.

For example,

1) when it was made by 'create table'

class PhoneBook(models.Model):
    name = models.CharField(max_length=32, null=True, blank=True, default='jordan')
    phone_number = models.CharField(max_length=32, null=True, blank=True)

The above code creates the migrations file as shown below.

operations = [
        migrations.CreateModel(
            name='PhoneBook',
            fields=[
                ('id', models.AutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID')),
                ('name', models.CharField(blank=True, default='jordan', max_length=32, null=True)),
                ('phone_number', models.CharField(blank=True, max_length=32, null=True)),
            ],
        ),
]

When Migrate, SQL is executed as shown below.

CREATE TABLE `main_phonebook` (
	`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
	`name` varchar(32) NULL,
	`phone_number` varchar(32) NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2) when it was made by 'alter table'
For the situation where the ‘alter table’ is applied, let's define the model class first as below and then add the name column.

class PhoneBook2(models.Model):
    phone_number = models.CharField(max_length=32, null=True, blank=True)

and I added 'name' column.

class PhoneBook2(models.Model):
    name = models.CharField(max_length=32, null=True, blank=True, default='jordan')
    phone_number = models.CharField(max_length=32, null=True, blank=True)

operations = [
        migrations.AddField(
            model_name='phonebook2',
            name='name',
            field=models.CharField(blank=True, default='jordan', max_length=32, null=True),
        ),
    ]

this operations make this SQL.

ALTER TABLE `main_phonebook2` ADD COLUMN `name` varchar(32) DEFAULT %s NULL ['jordan']
ALTER TABLE `main_phonebook2` ALTER COLUMN `name` DROP DEFAULT []

and table schema is like below.

CREATE TABLE `main_phonebook2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `phone_number` varchar(32) DEFAULT NULL,
  `name` varchar(32),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

We can see that the same code creates different schemas.

+ when i try to remove the column

class PhoneBook(models.Model):
    # name = models.CharField(max_length=32, null=True, blank=True, default='jordan')
    phone_number = models.CharField(max_length=32, null=True, blank=True)


class PhoneBook2(models.Model):
    # name = models.CharField(max_length=32, null=True, blank=True, default='jordan')
    phone_number = models.CharField(max_length=32, null=True, blank=True)

happened like this.

In [2]: p=PhoneBook(phone_number='010-1234-1234')                         

In [3]: p.save()                                                          
INSERT INTO `main_phonebook` (`phone_number`) VALUES (%s) ['010-1234-1234']

In [4]: p2=PhoneBook2(phone_number='010-1234-1234')                       

In [5]: p2.save()                                                         
INSERT INTO `main_phonebook2` (`phone_number`) VALUES (%s) ['010-1234-1234']

!!!!!!!!error!!!!!!!!!!!!!!!
IntegrityError: (1364, "Field 'name' doesn't have a default value")

I made PR for fixing this.
​https://github.com/django/django/pull/13982

[Simon Charette]

​Confirmed that this is an issue on MySQL and not on SQLite and Postgres but I didn't bother on Oracle.

The weird part is that MySQL still reports the column as NULLable through introspection; while SQLite and Postgres consider that NULL implies a fallback DEFAULT NULL while MySQL's doesn't.

Thanks for the report Jordan, feel free to include regression test from the above branch in your PR.

I think it should be fine to define BaseDatabaseSchemaEditor.sql_alter_column_no_default_null = sql_alter_column_no_default and adjust its _alter_column_default_sql method to branch of field.null to avoid the override in MySQL's schema editor. Chances are that some other backends behave the same way and a lot of this code is likely to change anyway when #470 lands.

[Jordan Bae]

@Simon Charette Thank you for reviewing the suggestion.

I updated code according to your guide.
Could you check this? ​https://github.com/django/django/pull/13982
If this is proper to what you're saying, I will add testcases.
And i wanna check where is it right to put the testcases.
I guess tests/backends/test_schema.py.

[Simon Charette]

Patch LGTM pending some cosmetic adjustments.

[Mariusz Felisiak <felisiak.mariusz@…>]

In d4ac23b:

Fixed #32425 -- Fixed adding nullable field with default on MySQL.

Thanks Simon Charette for the review.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 9eed2582:

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

Thanks Simon Charette for the review.

Backport of d4ac23bee1c84d8e4610350202ac068fc90f38c0 from master