mysql - Django alterfield migration to textfield fails - Stack Overflow

The migration for Django package django-mail-queue contains the following in relevant part:

migrations.AlterField(
            model_name='mailermessage',
            name='to_address',
            field=models.TextField(db_index=True, verbose_name='To'),
        ),

But it fails on MySQL 9.0.1 (Django 5.1.5) with the following error:

django.db.utils.OperationalError: (1170, "BLOB/TEXT column 'to_address' 
used in key specification without a key length")

Any idea why? I found a closed bug-report for Django (from some years ago) in which this kind of AlterField request had problems, but it appears to be unrelated.

The field was created in migration 0001 with the following:

        migrations.CreateModel(
            name='MailerMessage',
            fields=[
                ('id', models.AutoField(verbose_name='ID', serialize=False,
                                        auto_created=True, primary_key=True)),
                ('subject', models.CharField(max_length=250, verbose_name='Subject', blank=True)),
                ('to_address', models.TextField(verbose_name='To')),
                ('bcc_address', models.TextField(verbose_name='BCC', blank=True)),
                ('from_address', models.EmailField(max_length=250, verbose_name='From')),
                ('content', models.TextField(verbose_name='Content', blank=True)),
                ('html_content', models.TextField(verbose_name='HTML Content', blank=True)),
                ('app', models.CharField(max_length=250, verbose_name='App', blank=True)),
                ('sent', models.BooleanField(default=False, verbose_name='Sent', editable=False)),
                ('last_attempt', models.DateTimeField(verbose_name='Last attempt',
                                                      null=True, editable=False, blank=True)),
            ],
            options={
                'verbose_name': 'Message',
                'verbose_name_plural': 'Messages',
            },
            bases=(models.Model,),
        ),

There are no other references to this field in the list of migrations.

The sqlmigrate command suggested below produced the following output:

--
-- Alter field file_attachment on attachment
--
-- (no-op)
--
-- Alter field sent on mailermessage
--
CREATE INDEX `mailqueue_mailermessage_sent_5a2c49dd` ON `mailqueue_mailermessage` (`sent`);
--
-- Alter field to_address on mailermessage
--
CREATE INDEX `mailqueue_mailermessage_to_address_88fb3d2d` ON `mailqueue_mailermessage` (`to_address`);

But I don't know if the problem is resolved or if it will happen again in production.

The migration for Django package django-mail-queue contains the following in relevant part:

migrations.AlterField(
            model_name='mailermessage',
            name='to_address',
            field=models.TextField(db_index=True, verbose_name='To'),
        ),

But it fails on MySQL 9.0.1 (Django 5.1.5) with the following error:

django.db.utils.OperationalError: (1170, "BLOB/TEXT column 'to_address' 
used in key specification without a key length")

Any idea why? I found a closed bug-report for Django (from some years ago) in which this kind of AlterField request had problems, but it appears to be unrelated.

The field was created in migration 0001 with the following:

        migrations.CreateModel(
            name='MailerMessage',
            fields=[
                ('id', models.AutoField(verbose_name='ID', serialize=False,
                                        auto_created=True, primary_key=True)),
                ('subject', models.CharField(max_length=250, verbose_name='Subject', blank=True)),
                ('to_address', models.TextField(verbose_name='To')),
                ('bcc_address', models.TextField(verbose_name='BCC', blank=True)),
                ('from_address', models.EmailField(max_length=250, verbose_name='From')),
                ('content', models.TextField(verbose_name='Content', blank=True)),
                ('html_content', models.TextField(verbose_name='HTML Content', blank=True)),
                ('app', models.CharField(max_length=250, verbose_name='App', blank=True)),
                ('sent', models.BooleanField(default=False, verbose_name='Sent', editable=False)),
                ('last_attempt', models.DateTimeField(verbose_name='Last attempt',
                                                      null=True, editable=False, blank=True)),
            ],
            options={
                'verbose_name': 'Message',
                'verbose_name_plural': 'Messages',
            },
            bases=(models.Model,),
        ),

There are no other references to this field in the list of migrations.

The sqlmigrate command suggested below produced the following output:

--
-- Alter field file_attachment on attachment
--
-- (no-op)
--
-- Alter field sent on mailermessage
--
CREATE INDEX `mailqueue_mailermessage_sent_5a2c49dd` ON `mailqueue_mailermessage` (`sent`);
--
-- Alter field to_address on mailermessage
--
CREATE INDEX `mailqueue_mailermessage_to_address_88fb3d2d` ON `mailqueue_mailermessage` (`to_address`);

But I don't know if the problem is resolved or if it will happen again in production.

• mysql
• django
• migration

Share Improve this question Follow edited Jan 16 at 13:25 Mike Robinson asked Jan 16 at 12:55 Mike RobinsonMike Robinson 9,03566 gold badges3333 silver badges4949 bronze badges 10

• So what was the original field (looking back in the migration)? What if you use sqlmigrate on that migration file to see the SQL django generates? – willeM_ Van Onsem Commented Jan 16 at 13:19
• The only previous reference is a CreateModel statement in migration 0001, which says in relevant part: ('to_address', models.TextField(verbose_name='To')), I have updated the question with the complete entry. – Mike Robinson Commented Jan 16 at 13:23
• So essentially it seems to generate an index. Can you run manage.py sqlmigrate mailqueue 0008. – willeM_ Van Onsem Commented Jan 16 at 13:25
• Van, I ran the command that you suggested and added the output thereof to the original question. It appeared to run successfully. But I still don't know if the problem is now resolved or if it will happen again in production. Why did it happen here? (This package is years old ...) Nothing about any of it strikes me as "unusual." Could this be a Django bug that I need to report to someone? – Mike Robinson Commented Jan 16 at 13:31
• 1 looks like it was not really made for a MySQL database, since MySQL can not index over arbitrary lang fields: stackoverflow.com/a/1827099/67579 so a TextField and db_index=True makes not much sense. Likely a VARCHAR(..) should be used, so a CharField(max_length=255) or probably better: an EmaiLField, but likely it "abuses" the field to store multiple email addresses. – willeM_ Van Onsem Commented Jan 16 at 13:36

 |  Show 5 more comments

1 Answer 1

Sorted by: Reset to default 1

The reason this happens is because Django aims to be SQL dialect independent, but some technical details about different databases are (not) yet covered.

In this particular case can a MySQL database not index a VARCHAR(…) field if the maximum length is quite large or for TEXT where there is no limit at all. MySQL can add an index for items with a maximum of 255 characters.

The migration thus aims to add an index on the to_address field, and that field can not be indexed in MySQL. The index is however strictly speaking not necessary to get the thing working. We can fake the migration ^[Django-doc], so that Django thinks we did the migration, whereas in reality, we did not. We can do this by first migrating up to 0007, then fake 0008, and finally run the rest of the migrations, like:

python3 manage.py migrate mailqueue 0007
python3 manage.py migrate --fake mailqueue 0008
python3 manage.py migrate mailqueue