[Django] #34879: "Data truncated for column .." for migration changing auto-id-field
Django
-----------------------------------------+------------------------
Reporter: Wolfgang Fehr | Owner: nobody
Type: Bug | Status: new
Component: Migrations | Version: 3.2
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 |
-----------------------------------------+------------------------
== 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",
),
),
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/34879>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Reporter: Wolfgang Fehr | Owner: nobody
Type: Bug | Status: new
Component: Migrations | Version: 3.2
Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Wolfgang Fehr):
* Attachment "traceback.txt" added.
full traceback of migration-error that occured
Django
-------------------------------+--------------------------------------
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
-------------------------------+--------------------------------------
Old description:
New description:
== Resulting error ==
~~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)''
--
--
Ticket URL: <https://code.djangoproject.com/ticket/34879#comment:1>
Django
Old description:
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
non-deterministic behaviour of some kind.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/34879#comment:2>
Django
-------------------------------+--------------------------------------
Reporter: Wolfgang Fehr | Owner: nobody
Component: Migrations | Version: 3.2
Severity: Normal | Resolution: worksforme
Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------+--------------------------------------
* status: new => closed
* resolution: => worksforme
Comment:
Thanks for the report, however it works for me:
{{{#!sql
python manage.py sqlmigrate test_34879 0002
--
-- Alter field id on somemodel
--
ALTER TABLE `test_34879_somemodel` DROP FOREIGN KEY
`test_34879_somemodel_some_self_foreignkey_466fbd54_fk_test_3487`;
ALTER TABLE `test_34879_somemodel` MODIFY `id` bigint AUTO_INCREMENT NOT
NULL;
ALTER TABLE `test_34879_somemodel` MODIFY `some_self_foreignkey_id` bigint
NULL;
ALTER TABLE `test_34879_somemodel` ADD CONSTRAINT
`test_34879_somemodel_some_self_foreignkey_id_466fbd54_fk` FOREIGN KEY
(`some_self_foreignkey_id`) REFERENCES `test_34879_somemodel` (`id`);
}}}
Moreover Django 3.2 is in extended support and doesn't receive bugfixes
anymore (except security patches), and support for MySQL < 8 was dropped
in Django 4.2.
There is a known issue with `ManyToManyField`, so maybe you've encountered
it (check out #32674).
--
Ticket URL: <https://code.djangoproject.com/ticket/34879#comment:3>