[Django] #32425: MySQL Schema is different about the same class definitions. (depends on create table vs alter table)
Django
create table vs alter table)
--------------------------------------+--------------------------
Reporter: Jordan Bae | Owner: nobody
Type: Bug | Status: assigned
Component: Migrations | Version: master
Severity: Normal | Keywords: mysql
Triage Stage: Unreviewed | Has patch: 1
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
--------------------------------------+--------------------------
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.**
--
Ticket URL: <https://code.djangoproject.com/ticket/32425>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
create table vs alter table)
Reporter: Jordan Bae | Owner: nobody
Type: Bug | Status: assigned
Component: Migrations | Version: master
Keywords: mysql | Triage Stage: Unreviewed
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Description changed by Jordan Bae:
Old description:
New description:
For example,
+ 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")
}}}
--
--
Ticket URL: <https://code.djangoproject.com/ticket/32425#comment:1>
Django
create table vs alter table)
Reporter: Jordan Bae | Owner: nobody
Type: Bug | Status: assigned
Component: Migrations | Version: master
Keywords: mysql | Triage Stage: Unreviewed
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Description changed by Jordan Bae:
Old description:
> Hi, My name is Jordan. When I delete the column, I found some picky
> + 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")
> }}}
New description:
For example,
+ 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
--
--
Ticket URL: <https://code.djangoproject.com/ticket/32425#comment:2>
Django
create table vs alter table)
Reporter: Jordan Bae | Owner: Jordan Bae
Type: Bug | Status: assigned
Component: Migrations | Version: master
Keywords: mysql | Triage Stage: Unreviewed
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Jordan Bae):
* owner: nobody => Jordan Bae
--
Ticket URL: <https://code.djangoproject.com/ticket/32425#comment:3>
Django
create table vs alter table)
Reporter: Jordan Bae | Owner: Jordan Bae
Component: Migrations | Version: master
Keywords: mysql | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Simon Charette):
* stage: Unreviewed => Accepted
Comment:
[https://github.com/django/django/compare/master...charettes:ticket-32425
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 `NULL`able
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/32425#comment:4>
Django
create table vs alter table)
Reporter: Jordan Bae | Owner: Jordan Bae
Component: Migrations | Version: master
Keywords: mysql | Triage Stage: Accepted
Easy pickings: 0 | UI/UX: 0
Changes (by Mariusz Felisiak):
* needs_tests: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/32425#comment:5>
Django
create table vs alter table)
Reporter: Jordan Bae | Owner: Jordan Bae
Component: Migrations | Version: master
Keywords: mysql | Triage Stage: Accepted
Easy pickings: 0 | UI/UX: 0
Comment (by 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.
--
Ticket URL: <https://code.djangoproject.com/ticket/32425#comment:6>
Django
create table vs alter table)
Reporter: Jordan Bae | Owner: Jordan Bae
Component: Migrations | Version: master
Keywords: mysql | Triage Stage: Ready for checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Simon Charette):
* needs_tests: 1 => 0
* stage: Accepted => Ready for checkin
Comment:
Patch LGTM pending some cosmetic adjustments.
--
Ticket URL: <https://code.djangoproject.com/ticket/32425#comment:7>
Django
create table vs alter table)
Reporter: Jordan Bae | Owner: Jordan Bae
Component: Migrations | Version: master
Severity: Normal | Resolution: fixed
Keywords: mysql | Triage Stage: Ready for checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Mariusz Felisiak <felisiak.mariusz@…>):
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"d4ac23bee1c84d8e4610350202ac068fc90f38c0" d4ac23b]:
{{{
#!CommitTicketReference repository=""
revision="d4ac23bee1c84d8e4610350202ac068fc90f38c0"
Fixed #32425 -- Fixed adding nullable field with default on MySQL.
Thanks Simon Charette for the review.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/32425#comment:8>
Django
create table vs alter table)
Reporter: Jordan Bae | Owner: Jordan Bae
Type: Bug | Status: closed
Severity: Normal | Resolution: fixed
Keywords: mysql | Triage Stage: Ready for checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Mariusz Felisiak <felisiak.mariusz@…>):
In [changeset:"9eed258283f369dcb0ae0b84b4a1b24bf374db7b" 9eed2582]:
{{{
#!CommitTicketReference repository=""
revision="9eed258283f369dcb0ae0b84b4a1b24bf374db7b"
[3.2.x] Fixed #32425 -- Fixed adding nullable field with default on MySQL.
Thanks Simon Charette for the review.
Backport of d4ac23bee1c84d8e4610350202ac068fc90f38c0 from master
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/32425#comment:9>