Mysql error 1071 : Specified key was too long; max key length is 767 bytes

[pjambet]

I'm using mysql with encoding set as utf8mb4.

Because of that, an index on a column larger than 191 characters will trigger a mysql error 1071 when migrating djcelery

django.db.utils.DatabaseError: (1071, 'Specified key was too long; max key length is 767 bytes')

I've tried tons of different stuff in order to make that work, but the only working solution I came up with was to fork the project, and reduce all columns that caused the error to 191 chars.

Any help here would be extremely appreciated !

[ask]

Ouch, I'm not quite sure how this can be easily fixed. Maybe there is some setting in Django to set a max length for the field? You could monkey patch CharField directly I guess.

[ask]

The only alternative I can think of that django-celery could do to fix this would be to have an environment variable to set a limit, something like CELERY_DB_CHARMAX. But that would be a pretty awkward solution and I really feel that this is something that belongs in Django.

[ericbuehl]

The field(s) in question are already a bit arbitrary in length. There shouldn't be an issue with truncating existing users' data as this solution requires a modification to the migration itself. Also, I noticed what I believe to be a small typo with @pjambet's fix: ericbuehl@1a946a7

Is this acceptable to be pulled upstream?

[ericbuehl]

And another field: ericbuehl@092700c

[PAStheLoD]

I know +1 isn't going to solve much, but I've just run into this while deploying a django-based click-click thingie (uhm, software, app and program are too mainstream now I guess).

All the uniquely constrained text/varchar fields are affected due to InnoDB's limit for on-page column size. (Anything larger than the 767 bytes goes into a new page/block, which is a no-no for indexes, hence the restriction.)

A potential workaround is to recommend using the DYNAMIC (or COMPRESSED) row format and the innodb_large_prefix option (which pushes this limit to 3072 bytes). However, if people tinker this much with their RDBMS installation they should also mind that lowering the page size lowers this extended limit too proportionally.

[trevoriancox]

This is still an issue. pjambet has fixed a few lengths in his fork, but not 100%, so I had to fork from ericbuehl's fork of that.

As I understand it, django-celery is still useful despite what the README says. From http://docs.celeryproject.org/en/latest/django/first-steps-with-django.html:

"If you want to store task results in the Django database then you still need to install the django-celery library for that (alternatively you can use the [unstable] SQLAlchemy result backend)."

We generally ignore results, but want to capture certain task results the simplest way possible.

[chris-morgan]

I’ve published a variant of the current release with appropriate patches; it’s available at roi-com-au/django-celery@v3.1.17-mysql-utf8mb4.

If anyone wants to use this, the requirements.txt line would be

git+git://github.com/roi-com-au/django-celery.git@v3.1.17-mysql-utf8mb4#egg=django-celery==3.1.17-mysql-utf8mb4

[mingforpc]

How can I fixed it? The only way to fix this issue is modify models.py and 0001_initial.py by myself?

[chris-morgan]

@JianMingZhuo I gave a patched version of 3.1.17, with the instructions in my comment above. Add such a line to your requirements.txt and you’re set.

[mingforpc]

@chris-morgan Thanks

[auvipy]

I would request to send a PR

[chris-morgan]

@auvipy Wait, you’re closing the issue when it’s not fixed? That’s not how issues are supposed to work.

[auvipy]

@chris-morgan I requested you to send a PR as you got a patched version

[chris-morgan]

Note that the patch there is not suitable for application; I rewrote history there rather than creating a new migration. I’m not sure what the desirable behaviour would be in case of overflow when shortening 255 characters to 191. And cutting it down for everyone just because of MySQL’s incompetence in matters of Unicode seems rather sad, if the chance of desiring a value of over 191 characters is non-zero. I’ve got no idea whether that is the case or not. Also I’m not working on that project any more and may well never need to use django-celery with MySQL again.

[datacorder]

For avoiding this error in MariaDB just specify utf8_general_ci at creation time:

create database database_name default CHARACTER set utf8 default COLLATE utf8_general_ci;

If you can't create a new database, just convert it. It will avoid this error.

[ochanje210]

still having the issue...

[elfgzp]

Upgrade mysql to 5.7 and try migrate again.

wget https://dev.mysql.com/get/mysql-apt-config_0.8.1-1_all.deb  
sudo dpkg -i mysql-apt-config_0.8.1-1_all.deb  
sudo apt-get update
sudo apt-get install mysql-server

[pjambet]

Closing this issue since it's now been a few years and I haven't worked with django-celery in a while