Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

My model:

class Course(models.Model):
    language = models.ForeignKey(Language)
    name = models.CharField(max_length=50, unique=True, default='course')
    title = models.CharField(max_length=1024, default='no title')
    foreign_title = models.CharField(max_length=1024, default='no title', blank=True)
    header = models.CharField(max_length=1024, default='', blank=True)
    description = models.TextField(null=True, blank=True)

    def __str__(self):
        return self.title

    def __unicode__(self):
        return u'%s' % self.title

I am add "unique_together":

class Course(models.Model):
    language = models.ForeignKey(Language)
    name = models.CharField(max_length=50, unique=True, default='course')
    title = models.CharField(max_length=1024, default='no title')
    foreign_title = models.CharField(max_length=1024, default='no title', blank=True)
    header = models.CharField(max_length=1024, default='', blank=True)
    description = models.TextField(null=True, blank=True)

    class Meta:
        unique_together = ['language', 'name', 'title']

    def __str__(self):
        return self.title

    def __unicode__(self):
        return u'%s' % self.title

In-migration time getting the error:

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

My DB is: mysql Ver 15.1 Distrib 10.0.17-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

I tried to increase the length of the index mysql:

MariaDB [(none)]> set global innodb_file_format = BARRACUDA;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> set global innodb_large_prefix = ON;
Query OK, 0 rows affected (0.00 sec)

But this is still not enough:

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

The problem index length. How to specify the length limit Django index?

like image 615
Stan Zeez Avatar asked Apr 21 '15 20:04

Stan Zeez


5 Answers

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

like image 173
tstoev Avatar answered Oct 20 '22 16:10

tstoev


solution is

ALTER DATABASE `databasename` CHARACTER SET utf8; 
like image 44
Giuseppe De Marco Avatar answered Nov 09 '22 03:11

Giuseppe De Marco


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
like image 4
Ivan Kukurudziak Avatar answered Nov 09 '22 03:11

Ivan Kukurudziak


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.

like image 4
MewX Avatar answered Nov 09 '22 04:11

MewX


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.

like image 2
Rick James Avatar answered Nov 09 '22 04:11

Rick James