Opened 9 months ago

Closed 9 months ago

#34879 closed Bug (worksforme)

"Data truncated for column .." for migration changing auto-id-field

Reported by: Wolfgang Fehr Owned by: nobody
Component: Migrations Version: 3.2
Severity: Normal Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Wolfgang Fehr)

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.
(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 traceback in attachments.

Since this error didn't occur in every system, I assume this might be a non-deterministic behaviour 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

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:

migrations.AlterField(
    model_name="somemodel",
    name="id",
    field=models.BigAutoField(
        auto_created=True,
        primary_key=True,
        serialize=False,
        verbose_name="ID",
    ),
),

Attachments (1)

traceback.txt (5.5 KB ) - added by Wolfgang Fehr 9 months ago.
full traceback of migration-error that occured

Download all attachments as: .zip

Change History (4)

by Wolfgang Fehr, 9 months ago

Attachment: traceback.txt added

full traceback of migration-error that occured

comment:1 by Wolfgang Fehr, 9 months ago

Description: modified (diff)

comment:2 by Wolfgang Fehr, 9 months ago

Description: modified (diff)

comment:3 by Mariusz Felisiak, 9 months ago

Resolution: worksforme
Status: newclosed

Thanks for the report, however it works for me:

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).

Note: See TracTickets for help on using tickets.
Back to Top