#34879: "Data truncated for column .." for migration changing auto-id-field
-------------------------------+--------------------------------------
Reporter: Wolfgang Fehr | Owner: nobody
Type: Bug | Status: new
Component: Migrations | Version: 3.2
Severity: Normal | Resolution:
Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------+--------------------------------------
Description changed by Wolfgang Fehr:
Old description:
> == Introduction (i.e. how this happened) ==
>
> Came across this with updating my system and upgrading `djangocms_blog`
> vom version `1.2.3 => 2.0.5`.
>
> The migration `0041` changes the ID-fields of the models from `INT(11)`
> to `BIGINT(20)`, or `AutoField` to `BigAutoField`.
> ([https://github.com/nephila/djangocms-
> blog/blob/develop/djangocms_blog/migrations/0041_auto_20230720_1508.py
> migration 0041 of djangocms-blog])
>
> Most systems just ran the migration successfully, but some failed.
> The failed ones even stated "OK" in the migration output, even though the
> migration itself failed and wasn't applied.
> (also very interesting to see that behaviour)
>
> == Resulting error ==
>
> The migration basically failed (but not in all systems) with following
> error:
> {{{
> ...
> django.db.utils.DataError: (1265, "Data truncated for column 'parent_id'
> at row 1")
> Applying djangocms_blog.0041_auto_20230720_1508...%
> }}}
>
> See full [attachment:traceback.txt traceback] in attachments.
>
> Since this error didn't occur in every system, I assume this might be a
> race-condition of some kind.
> With this "ID-change" 2 database-columns will be changed: `id` and
> `some_self_foreignkey_id`.
> My guess would be that the latter sometimes is changed on second
> position.
> This results in `id` being `BIGINT(20)` and `some_self_foreignkey_id`
> being `INT(11)`.
> With this, the reference fails due to "referenced column can store more
> data".
>
> In my case, the `ForeignKey`-fields were all `NULL`, so the migration
> doesn't care about the data itself, just the schema.
>
> (I don't know if this problem is `django=3.2`-specific or if it also
> might occur on newer versions)
>
> In the local environment and with integration-tests, everything works
> fine.
>
> My solution for now - to make sure there isn't a downtime due to
> migrations - is to execute following SQL before migrating:
> `ALTER TABLE someapp_somemodel CHANGE some_self_foreignkey_id
> some_self_foreignkey_id BIGINT(20) NULL;`
>
> ----
> == Environment Information ==
> - Database: MySQL 5.7.40
> - python: 3.11
> - django: 3.2.21
> - django-cms: 3.11.4
> - djangocms-blog: 2.0.5
>
> == Basic Example of Model + Migration ==
>
> {{{#!python
> class SomeModel(models.Model):
> some_self_foreignkey = models.ForeignKey(
> "self",
> null=True,
> blank=True,
> related_name="+",
> on_delete=models.CASCADE,
> )
> }}}
>
> When changing "Auto-Id-Field" from `AutoField` to `BigAutoField` via
> `AppConfig` or `settings.py`, following migration-operation would be
> done:
>
> {{{#!python
> migrations.AlterField(
> model_name="somemodel",
> name="id",
> field=models.BigAutoField(
> auto_created=True,
> primary_key=True,
> serialize=False,
> verbose_name="ID",
> ),
> ),
> }}}
New description:
== Introduction (i.e. how this happened) ==
Came across this with updating my system and upgrading `djangocms_blog`
vom version `1.2.3 => 2.0.5`.
The migration `0041` changes the ID-fields of the models from `INT(11)` to
`BIGINT(20)`, or `AutoField` to `BigAutoField`.
([https://github.com/nephila/djangocms-
blog/blob/develop/djangocms_blog/migrations/0041_auto_20230720_1508.py
migration 0041 of djangocms-blog])
Most systems just ran the migration successfully, but some failed.
The failed ones even stated "OK" in the migration output, even though the
migration itself failed and wasn't applied.
(also very interesting to see that behaviour)
== Resulting error ==
The migration basically failed (but not in all systems) with following
error:
{{{
...
django.db.utils.DataError: (1265, "Data truncated for column 'parent_id'
at row 1")
Applying djangocms_blog.0041_auto_20230720_1508...%
}}}
See full [attachment:traceback.txt traceback] in attachments.
Since this error didn't occur in every system, I assume this might be a
race-condition of some kind.
With this "ID-change" 2 database-columns will be changed: `id` and
`some_self_foreignkey_id`.
My guess would be that the latter sometimes is changed on second position.
This results in `id` being `BIGINT(20)` and `some_self_foreignkey_id`
being `INT(11)`.
With this, the reference fails due to "referenced column can store more
data".
In my case, the `ForeignKey`-fields were all `NULL`, so the migration
doesn't care about the data itself, just the schema.
(I don't know if this problem is `django=3.2`-specific or if it also might
occur on newer versions)
In the local environment and with integration-tests, everything works
fine.
~~My solution for now - to make sure there isn't a downtime due to
migrations - is to execute following SQL before migrating:~~
~~`ALTER TABLE someapp_somemodel CHANGE some_self_foreignkey_id
some_self_foreignkey_id BIGINT(20) NULL;`~~
''(edit: that alone doesn't work due to the constraints)''
----
== Environment Information ==
- Database: MySQL 5.7.40
- python: 3.11
- django: 3.2.21
- django-cms: 3.11.4
- djangocms-blog: 2.0.5
== Basic Example of Model + Migration ==
{{{#!python
class SomeModel(models.Model):
some_self_foreignkey = models.ForeignKey(
"self",
null=True,
blank=True,
related_name="+",
on_delete=models.CASCADE,
)
}}}
When changing "Auto-Id-Field" from `AutoField` to `BigAutoField` via
`AppConfig` or `settings.py`, following migration-operation would be done:
{{{#!python
migrations.AlterField(
model_name="somemodel",
name="id",
field=models.BigAutoField(
auto_created=True,
primary_key=True,
serialize=False,
verbose_name="ID",
),
),
}}}
--
--
Ticket URL: <https://code.djangoproject.com/ticket/34879#comment:1>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/0107018adb20c222-b04e7df2-9317-49c5-a079-5e0f4528b6a5-000000%40eu-central-1.amazonses.com.
