Opened 4 years ago

Closed 4 years ago

Last modified 4 years ago

#32425 closed Bug (fixed)

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

Reported by: Jordan Bae Owned by: Jordan Bae
Component: Migrations Version: dev
Severity: Normal Keywords: mysql
Cc: Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by 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

Change History (9)

comment:1 by Jordan Bae, 4 years ago

Description: modified (diff)

comment:2 by Jordan Bae, 4 years ago

Description: modified (diff)

comment:3 by Jordan Bae, 4 years ago

Owner: changed from nobody to Jordan Bae

comment:4 by Simon Charette, 4 years ago

Triage Stage: UnreviewedAccepted

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.

comment:5 by Mariusz Felisiak, 4 years ago

Needs tests: set

comment:6 by Jordan Bae, 4 years ago

@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.

comment:7 by Simon Charette, 4 years ago

Needs tests: unset
Triage Stage: AcceptedReady for checkin

Patch LGTM pending some cosmetic adjustments.

comment:8 by Mariusz Felisiak <felisiak.mariusz@…>, 4 years ago

Resolution: fixed
Status: assignedclosed

In d4ac23b:

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

Thanks Simon Charette for the review.

comment:9 by Mariusz Felisiak <felisiak.mariusz@…>, 4 years ago

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

Note: See TracTickets for help on using tickets.
Back to Top