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

[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",
    ),
),

[Wolfgang Fehr]

full traceback of migration-error that occured

[Mariusz Felisiak]

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