alter field type on MySQL "forgets" nullability (and more)

[Simon Percivall]

A migration changing field type on MySQL will remove "NOT NULL" (and every other attribute) from the field. This is because MODIFY COLUMN on MySQL requires the whole field definition to be repeated, not just the type.

So for instance:

class T(models.Model):
    field = models.SmallIntegerField()

will create a table:

CREATE TABLE `app_t` (
  ...
  `field` smallint(6) NOT NULL,
  ...
) ENGINE=InnoDB

Changing the field definition to:

...
    field = models.IntegerField()
...

will create a migration:

...
migrations.AlterField(
    model_name='t',
    name='field',
    field=models.IntegerField(),
)
...

resulting in the SQL

ALTER TABLE `app_t` MODIFY `field` integer

resulting in the table:

CREATE TABLE `app_t` (
  ...
  ``field` int(11) DEFAULT NULL,
  ...
)

This applies to 1.7 through 1.8.

[Tim Graham]

Could you check if this is a duplicate of #23678?

[Claude Paroz]

test failure

[Claude Paroz]

Are schema corruption bugs considered having the same severity as data loss bugs? I'd say yes, and if it is confirmed, the patch should then also be backported to 1.7.x. Thoughts?

[Tim Graham]

Okay with me.

[Claude Paroz <claude@…>]

In 02260ea:

Fixed #24595 -- Prevented loss of null info in MySQL field alteration

Thanks Simon Percivall for the report, and Simon Charette and Tim
Graham for the reviews.

[Claude Paroz <claude@…>]

In bbfcd961:

[1.8.x] Fixed #24595 -- Prevented loss of null info in MySQL field alteration

Thanks Simon Percivall for the report, and Simon Charette and Tim
Graham for the reviews.
Backport of 02260ea3f6 from master.

[Claude Paroz <claude@…>]

In ada0845d:

[1.7.x] Fixed #24595 -- Prevented loss of null info in MySQL field alteration

Thanks Simon Percivall for the report, and Simon Charette and Tim
Graham for the reviews.
Backport of 02260ea3f61b from master.

[Claude Paroz]

The failures on Oracle shown in Jenkins tend to show that Oracle may also be affected by this bug. I'll hand this over to a more knowledgeable person, though.

[Shai Berger <shai@…>]

In 773ec512:

[1.7.x] Fixed #24595 Oracle test failure

The only problem for Oracle was the test, which tested nullity on
text/char fields -- but Oracle interprets_empty_strings_as_null.

Backport of d5a0acc from master

[Shai Berger <shai@…>]

In 8363f217:

[1.8.x] Fixed #24595 Oracle test failure

The only problem for Oracle was the test, which tested nullity on
text/char fields -- but Oracle interprets_empty_strings_as_null.

Backport of d5a0acc from master

[Shai Berger]

Well, for some reason the commit hook missed it on master, but just to clarify,
d5a0acc fixed the problem there.

[Claude Paroz]

Thanks Shai!