[Django] #35763: Setting the auto increment value of a database model breaks when you add a new field to the model

Django

unread,

Sep 15, 2024, 1:39:14 PM9/15/24

to django-...@googlegroups.com

#35763: Setting the auto increment value of a database model breaks when you add a
new field to the model
-------------------------------------+-------------------------------------
Reporter: Kevin Renskers | Type: Bug
Status: new | Component: Database
| layer (models, ORM)
Version: 5.1 | Severity: Normal
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/35763>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

Django

unread,

Sep 15, 2024, 1:48:18 PM9/15/24

to django-...@googlegroups.com

#35763: Setting the auto increment value of a database model breaks when you add a
new field to the model
-------------------------------------+-------------------------------------

Comment (by 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.
--
Ticket URL: <https://code.djangoproject.com/ticket/35763#comment:1>

Django

unread,

Sep 17, 2024, 1:22:01 AM9/17/24

to django-...@googlegroups.com

#35763: Setting the auto increment value of a database model breaks when you add a
new field to the model
-------------------------------------+-------------------------------------

Reporter: Kevin Renskers | Owner: MrInfaith
Type: Bug | Status: assigned

Changes (by MrInfaith):

* owner: (none) => MrInfaith
* status: new => assigned

--
Ticket URL: <https://code.djangoproject.com/ticket/35763#comment:2>

Django

unread,

Sep 17, 2024, 3:46:27 PM9/17/24

to django-...@googlegroups.com

#35763: Setting the auto increment value of a database model breaks when you add a
new field to the model
-------------------------------------+-------------------------------------

Reporter: Kevin Renskers | Owner: Sachin
| Kundalwal
Type: Bug | Status: closed

Component: Database layer | Version: 5.1
(models, ORM) |

Severity: Normal | Resolution: invalid

Changes (by Natalia Bidart):

* resolution: => invalid
* status: assigned => closed

Comment:

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
[https://docs.djangoproject.com/en/dev/faq/help/#how-do-i-do-x-why-
doesn-t-y-work-where-can-i-go-to-get-help this link].

I did some research nevertheless because I was curious, and I found out a
solution that works. I followed
[https://stackoverflow.com/questions/117800/how-to-get-django-autofields-
to-start-at-a-higher-number this post] and added to the initial migration
this code:
{{{#!python
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
[https://docs.djangoproject.com/en/dev/internals/contributing/triaging-
tickets/#closing-tickets 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.
--
Ticket URL: <https://code.djangoproject.com/ticket/35763#comment:3>

Django

unread,

Sep 17, 2024, 3:53:34 PM9/17/24

to django-...@googlegroups.com

#35763: Setting the auto increment value of a database model breaks when you add a
new field to the model
-------------------------------------+-------------------------------------
Reporter: Kevin Renskers | Owner: Sachin
| Kundalwal
Type: Bug | Status: closed
Component: Database layer | Version: 5.1
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by 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.
--
Ticket URL: <https://code.djangoproject.com/ticket/35763#comment:4>

Django

unread,

Sep 17, 2024, 4:36:25 PM9/17/24

to django-...@googlegroups.com

#35763: Setting the auto increment value of a database model breaks when you add a
new field to the model
-------------------------------------+-------------------------------------
Reporter: Kevin Renskers | Owner: Sachin
| Kundalwal
Type: Bug | Status: closed
Component: Database layer | Version: 5.1
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by 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.
--
Ticket URL: <https://code.djangoproject.com/ticket/35763#comment:5>

Django

unread,

Sep 19, 2024, 8:31:25 AM9/19/24

to django-...@googlegroups.com

#35763: Setting the auto increment value of a database model breaks when you add a
new field to the model
-------------------------------------+-------------------------------------
Reporter: Kevin Renskers | Owner: Sachin
| Kundalwal
Type: Bug | Status: closed
Component: Database layer | Version: 5.1
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Natalia Bidart):

Replying to [comment:5 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.)
--
Ticket URL: <https://code.djangoproject.com/ticket/35763#comment:6>

Django

unread,

Sep 19, 2024, 8:36:12 AM9/19/24

to django-...@googlegroups.com

#35763: Setting the auto increment value of a database model breaks when you add a
new field to the model
-------------------------------------+-------------------------------------
Reporter: Kevin Renskers | Owner: Sachin
| Kundalwal
Type: Bug | Status: closed
Component: Database layer | Version: 5.1
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Natalia Bidart):

Replying to [comment:4 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
[https://forum.djangoproject.com/c/internals/5 Django Forum], where you'll
reach a wider audience and likely get extra feedback.
--
Ticket URL: <https://code.djangoproject.com/ticket/35763#comment:7>

Django

unread,

Sep 19, 2024, 8:41:02 AM9/19/24

to django-...@googlegroups.com

#35763: Setting the auto increment value of a database model breaks when you add a
new field to the model
-------------------------------------+-------------------------------------
Reporter: Kevin Renskers | Owner: Sachin
| Kundalwal
Type: Bug | Status: closed
Component: Database layer | Version: 5.1
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by 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.
--
Ticket URL: <https://code.djangoproject.com/ticket/35763#comment:8>

Django

unread,

Sep 19, 2024, 2:34:27 PM9/19/24

to django-...@googlegroups.com

#35763: Setting the auto increment value of a database model breaks when you add a
new field to the model
-------------------------------------+-------------------------------------
Reporter: Kevin Renskers | Owner: Sachin
| Kundalwal
Type: Bug | Status: closed
Component: Database layer | Version: 5.1
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by 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 [comment:6 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.
--
Ticket URL: <https://code.djangoproject.com/ticket/35763#comment:9>

Reply all

Reply to author

Forward