django migrate raise mysql error (1071, 'Specified key was too long; max key length is 767 bytes')

[wilslee]

Hello!
I am using the sorl-thumbnail v12.3 and mysql db in my django project.

And it raise mysql error (1071, 'Specified key was too long; max key length is 767 bytes') when migrate the sql. Because set the database`s collate with "utf8mb4_general_ci" .

Does somebady catch this problem? I just overwirte the max_length by this in models.py :

KVStore._meta.get_field('key').max_length = 128

Does it has any other solution?

[elton2048]

Hello! I also face this problem. I have try different solutions but each with different issues behind.

Overriding the max_length in models.py is one of the solution. Actually the max_length can be set as 190 (767 / 4 = 191.75, setting 190 for a 10-multiple). However I am not sure whether it will cause any problems for the model.

Another solution would be enforcing the encoding of the table to utf8, in that way it can ensure the max key length is fixed no matter what encoding of the database used. To do this we need to change the migrations/0001_initial.py.

# Change the construction of the table which ensure the character set of to UTF8. Require library sqlparse

operations = [
        migrations.CreateModel(
            name='KVStore',
            fields=[
                ('key', models.CharField(serialize=False, db_column='key', max_length=10, primary_key=True)),
                ('value', models.TextField()),
            ],
        ),
        migrations.RunSQL(
            "ALTER TABLE `thumbnail_kvstore` CONVERT TO character\nSET utf8 COLLATE utf8_general_ci;"
        ),
        migrations.AlterField(
            model_name='KVStore',
            name='key',
            field=models.CharField(serialize=False, db_column='key', max_length=200, primary_key=True),
        ),
    ]

However as the comment stated to do this the environment requires sqlparse library as the migrations.RunSQL needs the library in MySQL (See https://docs.djangoproject.com/en/1.10/ref/migration-operations/#runsql ).

[dericktsai]

It's a bit late, but I also ran into this problem this week. My solution is make a database router. All tables sorl-thumbnail created stored in sqlite.
first step is give app_label to all tables in sorl.thumbnail.models (only one actually)

@python_2_unicode_compatible
class KVStore(models.Model):
    key = models.CharField(
        max_length=200, primary_key=True,
        db_column=settings.THUMBNAIL_KEY_DBCOLUMN
    )
    value = models.TextField()

    def __str__(self):
        return self.key

    class Meta:
        app_label = 'thumbnail'

next is create a database router file

class ThumbnailDatabaseRouter(object):
    """
    Determine how to route database calls for an app's models (in this case, for thumbnail).
    All other models will be routed to the next router in the DATABASE_ROUTERS setting if applicable,
    or otherwise to the default database.
    """

    def db_for_read(self, model, **hints):
        if model._meta.app_label == 'thumbnail':
            return 'db_thumbnail'
        return None

    def db_for_write(self, model, **hints):
        """Send all write operations on Example app models to `example_db`."""
        if model._meta.app_label == 'thumbnail':
            return 'db_thumbnail'
        return None

    def allow_relation(self, obj1, obj2, **hints):
        """Determine if relationship is allowed between two objects."""

        # Allow any relation between two models that are both in the Example app.
        if obj1._meta.app_label == 'thumbnail' and obj2._meta.app_label == 'thumbnail':
            return True
        # No opinion if neither object is in the Example app (defer to default or other routers).
        elif 'thumbnail' not in [obj1._meta.app_label, obj2._meta.app_label]:
            return None

        # Block relationship if one object is in the Example app and the other isn't.
        return False

    def allow_migrate(self, db, app_label, model_name=None, **hints):
        """Ensure that the Example app's models get created on the right database."""
        if app_label == 'thumbnail':
            # The Example app should be migrated only on the example_db database.
            return db == 'db_thumbnail'
        elif db == 'db_thumbnail':
            # Ensure that all other apps don't get migrated on the example_db database.
            return False

        # No opinion for all other scenarios
        return None

finally alter settings

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'xxxx',
        'USER' : xxxxx'',
        'PASSWORD' : 'xxxxxx',
        'HOST' : 'xxxxx',
        'OPTIONS': {
            'init_command': "SET sql_mode='STRICT_TRANS_TABLES'",
            'charset': 'utf8mb4',
            'use_unicode': True,
        },
    },
    'db_thumbnail': {
            'ENGINE': 'django.db.backends.sqlite3',
            'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),
    },

}

DATABASE_ROUTERS = ['myproject.thumbnail_db_router.ThumbnailDatabaseRouter',]

and that's it