Setting the auto increment value of a database model breaks when you add a new field to the model

[Kevin Renskers]

Setting the auto increment value of a database model breaks when you add a new field to the model. It only breaks in SQLite.

I have simple model Content:

class Content(models.Model):
    name = models.CharField(max_length=255, blank=True)

In the initial migration I've added an extra operation:

migrations.RunSQL("INSERT INTO sqlite_sequence (seq, name) VALUES (50000, 'content_content');"),

Now when I create my first model instance its ID starts at 50,001, as expected. However when I then add a second field to my Content model and create a migration, now my first model instance ID is 1 instead of 50,001. Somehow the migrations.AddField operation has undone the sqlite_sequence modification.

I have a repro here: ​https://github.com/kevinrenskers/django-seq-repro.

It has two commits:

1. The initial commit where a Content model is added, and in the initial migration I set the initial auto increment value. A test is added to make sure the first model instance has ID 50,001, which passes.
2. The second commit adds a single field to the Content model (with the accompanying migration file), and now the test fails.

The test can be fixed by adding another RunSQL command to the second migration:
migrations.RunSQL("UPDATE sqlite_sequence SET seq = 50000 WHERE name = 'content_content'"),

It seems like a bug that the auto increment modification is undone by the second migration.

Important to note: this only happens in SQLite. With PostgreSQL the query in the initial migration would be ALTER SEQUENCE content_content_id_seq RESTART WITH 50000;, and the test still passes after adding the new field to the Content model. Only with SQLite does it break and is the second operation to update sqlite_sequence necessary.

[Kevin Renskers]

The big problem is that I now need to add migrations.RunSQL("UPDATE sqlite_sequence SET seq = 50000 WHERE name = 'content_content'") to every single migration that adds a new field to my model, or my tests start to fail in CI (which runs using SQLite). Locally (using PostgreSQL) all tests pass just fine.

Some background on why I need to change the auto increment value: website content with IDs until 50,000 are stored in an old historical table, and my view fetches the content from the correct model based on the ID: lower than 50,000 use the old model, newer use the new model. My unit tests break when I create a new Content instance, expect its ID to be at least 50,000, but instead the ID is 1 and the view handles it in a way that's not expected.

[Natalia Bidart]

Hello Kevin, thank you for your report.

It's unclear that this qualifies as a bug report for Django. This may be better suited to be a support request: the best place to get answers to your issue is using any of the user support channels from ​this link.

I did some research nevertheless because I was curious, and I found out a solution that works. I followed ​this post and added to the initial migration this code:

def set_counter(apps, schema_editor):
    Content = apps.get_model("ticket_35763", "Content")
    db_alias = schema_editor.connection.alias
    Content.objects.using(db_alias).create(id=50000, name="Initial content.")

# ...

        migrations.RunPython(set_counter, None),

With the above, all future Content insertions, even after new migrations, use the correct value.

So, since the goal of this issue tracker is to track issues about Django itself, and your issue seems, at first, to be located in your custom code, I'll be closing this ticket as invalid following the ​ticket triaging process. If, after debugging, you find out that this is indeed a bug in Django, please re-open with the specific details and please be sure to include a small Django project to reproduce or a failing test case.

[Kevin Renskers]

Yeah the .create method doesn't work in my real world app since the real model has relationships, and I'd also have to create those objects just to create a dummy object in the migration.

It really does feel like a Django bug to me though. It only breaks using SQLite, and only when adding a second migration. The initial adjustment of the auto increment value works just fine using a proper documented query, not via a workaround where we're creating a dummy object. But once I add a second field to the model and a new migration is run, the auto increment value is lost. Somehow it seems like the migrations.AddField operation is doing the auto increment value, and only in SQLite.

I don't understand how this issue is located "in my custom code". I'm just updating the auto increment using SQLite's command for that.

[Claude Paroz]

I suspect that this issue could be related to the fact that SQLite AFAIR doesn't support adding columns, so Django is emulating that by deleting and recreating the entire table to add new columns. I also suppose that the sequence could be lost during that process. If that's correct, I guess that Django should ideally also restore any preexisting sequence on those tables. If those suppositions are confirmed by some test, then we should accept this ticket as a bug.

[Natalia Bidart]

Replying to Claude Paroz:

I suspect that this issue could be related to the fact that SQLite AFAIR doesn't support adding columns, so Django is emulating that by deleting and recreating the entire table to add new columns. I also suppose that the sequence could be lost during that process. If that's correct, I guess that Django should ideally also restore any preexisting sequence on those tables. If those suppositions are confirmed by some test, then we should accept this ticket as a bug.

If this would be the case (sequences are lost when columns are added), then this would be a issue affecting, for example, any primary key, wouldn't it? We should've had reports long time ago...

(I have searched in the web and I couldn't find any similar report.)

[Natalia Bidart]

Replying to Kevin Renskers:

It really does feel like a Django bug to me though. It only breaks using SQLite, and only when adding a second migration.

Are you saying that further migrations (third, fourth) are not causing any issues for you?

I don't understand how this issue is located "in my custom code". I'm just updating the auto increment using SQLite's command for that.

What I mean is that your use case seems a very specific need arising from a niche use case. I don't think this applies to the broader ecosystem, and Django is a framework designed to offer robust and accurate solutions for common scenarios.

My advice at this time would be to start a new conversation on the ​Django Forum, where you'll reach a wider audience and likely get extra feedback.

[Kevin Renskers]

Are you saying that further migrations (third, fourth) are not causing any issues for you?

No. Every migration that adds a new field to the model undoes the auto increment value change, and thus I need to add migrations.RunSQL("UPDATE sqlite_sequence SET seq = 50000 WHERE name = 'content_content'") to every single migration that adds a new field to my model.

What I mean is that your use case seems a very specific need arising from a niche use case.

I respectfully disagree. I change the auto increment value of my table, and Django's migration undoes this.

[Claude Paroz]

Kevin, I think that if you could create a failing test in the Django's own test suite, it could help pushing this forward.

Replying to Natalia Bidart:

If this would be the case (sequences are lost when columns are added), then this would be a issue affecting, for example, any primary key, wouldn't it? We should've had reports long time ago...

I think the issue is not that the sequence itself is lost, but re-creating the table and the related sequence is resetting the sequence to the next available value, instead of keeping the manually-set value of the sequence.