Alter ForeignKey from non-nullable to nullable, including data migration, in squashed migration, leads to duplicate index created

[fvkluck]

Hi all,

I recently ran into an issue when squashing migrations. The issue is that there is a duplicate index in the database. We're probably going to work around it by skipping step 3 below, but I thought it still makes sense to create an issue, in the case someone else runs into it.

I managed to find a minimal reproducible example and uploaded it here

The steps I took to create this example:

1. Create new django project and new app
2. Create ModelA and ModelB, where ModelB has a nullable ForeignKey relation to ModelA, generate migrations
3. Create a data migration where you create some objects of ModelA and perform some filter(..).update(...)s on ModelB
4. Alter the ForeignKey from ModelB to ModelA to be non-nullable. Generate migrations, selecting option 2 (don't specify a default) when prompted by Django.
5. Squash these migrations

The result in the database is:

There are two indexes here, where I would expect only 1.

The peculiar thing is that without step 3, only 1 unique index is created (as I would expect). I haven't investigated if the issue also occurs when you perform less or different creations/updates in step 3.
The issue doesn't occur when using sqlite as backend, but it also occurs with the django-mssql-backend.

Best,
Florian

[fvkluck]

I just realised that it's not peculiar at all that it doesn't occur without step 3. When including step 3, the result of the squashmigrations is that step 2 and 4 are separate steps. Without step 3, squashmigrations will squash step 2 and 4 into a single AddField.
So the issue must be that first performing an AddField and then an AlterField with this particular field somehow creates two indices on the field.

It might be related to #22, even though the error message is different.

[timnyborg]

So the issue must be that first performing an AddField and then an AlterField with this particular field somehow creates two indices on the field.

Is this after upgrading to v1.1? Because that's what I'm seeing after an upgrade, when running AlterField after AddField

The migration:

migrations.AlterField(
    model_name='student',
    name='domicile',
    field=models.ForeignKey(
        db_column='domicile', on_delete=django.db.models.deletion.DO_NOTHING, to='student.Domicile', default=181
    ),
),

Migration output:

...
ALTER TABLE [student] ADD DEFAULT 181 FOR [domicile]; (params [])
UPDATE [student] SET [domicile] = %s WHERE [domicile] IS NULL; (params [181])
ALTER TABLE [student] ALTER COLUMN [domicile] int NOT NULL; (params [])
CREATE INDEX [student_domicile_2f87fe85] ON [student] ([domicile]); (params ())
CREATE INDEX [student_domicile_2f87fe85] ON [student] ([domicile]); (params ())
Traceback (most recent call last):
...
pyodbc.ProgrammingError: ('42S11', "[42S11] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The operation failed because an index or statistics with name 'student_domicile_2f87fe85' already exists on table 'student'. (1913) (SQLExecDirectW)")

[timnyborg]

Possibly also relevant:
Altering a TextField to a JSONField causes an index to be created (or attempted, at least), even if db_index=False. So it seems like there may be a single bug trying to create extra single-column indices on all AlterField commands.

migrations.AlterField(
    model_name='studentarchive',
    name='json',
    field=models.JSONField(db_index=False),
),

CREATE INDEX [student_archive_json_7f95184c] ON [student_archive] ([json]); (params ())

Traceback:
pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Column 'json' in table 'student_archive' is of a type that is invalid for use as a key column in an index. (1919) (SQLExecDirectW)")

[absci]

@fvkluck Thanks for the detailed information. I'm able to reproduce this issue, it's under investigation.
@timnyborg I can also reproduce your issue, I think this is a different problem. This happened after the upgrade to 1.1. I'll also fix this.

[fvkluck]

Thanks for looking into it!

[timnyborg]

@timnyborg I can also reproduce your issue, I think this is a different problem. This happened after the upgrade to 1.1. I'll also fix this.

I'm seeing the same issue after upgrading to 1.1.1 (AlterField: IntegerField -> ForeignKey). However, it does not occur when null=True on the ForeignKey.

[absci]

@timnyborg I can also reproduce your issue, I think this is a different problem. This happened after the upgrade to 1.1. I'll also fix this.

I'm seeing the same issue after upgrading to 1.1.1 (AlterField: IntegerField -> ForeignKey). However, it does not occur when null=True on the ForeignKey.

Ok, I'll take a look.

[absci]

@timnyborg I have created models like this and couldn't reproduce the issue.

class Model1(models.Model):
    field1 = models.IntegerField()

class Model2(models.Model):
    field2 = models.IntegerField()
    field_1 = models.ForeignKey(Model1, null=False, on_delete=models.CASCADE, default=1)

I change default=1 to 2, or null=False to True. The migration went successfully. May I ask for a more detailed step to help reproduce it?

[timnyborg]

Sure, I'll try to boil my project down to a reproducible example.

[timnyborg]

Turns out it's specifically when converting an IntegerField with null=True to a ForeignKey with null=False (so the opposite of the initial issue), and probably an uncommon thing to do.
See example migrations at: https://github.com/timnyborg/mssql_issue/tree/master/app/migrations

I've worked around the issue in my own project by updating the initial migrations to null=False.

[absci]

Turns out it's specifically when converting an IntegerField with null=True to a ForeignKey with null=False (so the opposite of the initial issue), and probably an uncommon thing to do. See example migrations at: https://github.com/timnyborg/mssql_issue/tree/master/app/migrations

I've worked around the issue in my own project by updating the initial migrations to null=False.

Thanks for your repro app. I'll fix this issue in the future.

[rjschave]

Our team encountered a problem applying migrations from the Wagtail (https://github.com/wagtail/wagtail) project when using mssql-django version 1.1. We were able to reproduce the problem without Wagtail.

Starting with a char field that allows NULL values and then modifying the field to no longer allow NULL values is enough to reproduce the problem.

Here is a link to our sample project - https://github.com/eclipseconsulting/mssql-django-test-v1.1. The readme contains more details.

This problem does not occur with mssql-django version 1.0

[absci]

Our team encountered a problem applying migrations from the Wagtail (https://github.com/wagtail/wagtail) project when using mssql-django version 1.1. We were able to reproduce the problem without Wagtail.

Starting with a char field that allows NULL values and then modifying the field to no longer allow NULL values is enough to reproduce the problem.

Here is a link to our sample project - https://github.com/eclipseconsulting/mssql-django-test-v1.1. The readme contains more details.

This problem does not occur with mssql-django version 1.0

Thanks for the sample project, I could reproduce it. I'll be fixing this issue.