[Fixed]-Django.db.utils.OperationalError: (1071, 'Specified key was too long; max key length is 767 bytes')

solution is

ALTER DATABASE `databasename` CHARACTER SET utf8; 

The previous two answers did not help in my case, so I’m posting my solution to my case when your limit is 1000 (i.e. 1071, ‘Specified key was too long; max key length is 1000 bytes’).

First of all, make sure you are working on utf8 encoding!

Then, navigate to your setting file my.ini, find the line default-storage-engine=xxx. If it is

default-storage-engine=MYISAM

please change to

default-storage-engine=InnoDB

Then, the problem should be solved.

The reason is simply because MYISAM does not support key size greater than 1000 bytes.

You can recreate “database” again:

CREATE DATABASE mydatabase CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;

or change config file. Open /etc/mysql/mariadb.conf.d/50-server.cnf and change:

character-set-server  = utf8
collation-server      = utf8_general_ci

Just upgrade and migrate database to MySQL-8.0.11 or ** MySQL-5.7.21** also make sure to use utf8 and utf8_general_ci

I had that problem, then I updated to 8.0.11 on my testing enviroment and 5.7.21 on the server. Now it migrates on both enviroments.

I think all 4 of these things are needed:

SET GLOBAL innodb_file_per_table = ON,
           innodb_file_format = Barracuda,
           innodb_large_prefix = ON;
CREATE/ALTER TABLE ...
    ROW_FORMAT = DYNAMIC or COMPRESSED

This should get past the limit of 767 bytes for one column, but won’t get past the 3072 bytes limit for the entire index.

In order to have a compound unique index composed of strings, normalize some of the strings. Replacing a long string with a 4-byte INT will shrink the index below the limit.

I ended up adding

'OPTIONS': { 'init_command': 'SET storage_engine=INNODB;' }

to my DB backed configuration in settings.py and that fixed the problem.

The MySQL server was configured to use InnoDB as a default engine, but due to some reason it still tried to create tables with the MyISAM. I am running MySQL 5.1 with Django 2.2.1 and Python 3.6.7

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

Since this is one of the top posts for this kind of error I can share my solution that worked for me when storing S3 paths into my db.

I’m adding a MySQL prefix index to the desired field.

# my_app/models.py
class File(models.Model):
    s3_path = models.CharField(max_length=1024, help_text="S3 object key")


# my_app/migration_000x.py
from django.db import migrations

class Migration(migrations.Migration):

    dependencies = [
        (...),
    ]

    operations = [
        migrations.RunSQL(
            sql="CREATE INDEX my_app_file_s3_path__prefix ON my_app_file (s3_path(768));",
            reverse_sql="DROP INDEX my_app_file_s3_path__prefix ON my_app_file;",
        )
    ]

I can get away with this because I know that most of the entries will be quite short (100-200 chars) so they will benefit even from just a prefix index but I still maintain max_length that is forced upon me by AWS S3.

I’m using InnoDB engine with utf8mb4_unicode_ci for all my tables.

export database from local and import it using command line
like that
mysql -u username -p database_name < file.sql
this worked for me