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

Question

I have a django project and I would like to run some unit tests. When I try: python manage.py test it throws this error and I couldn't find much information about it in here.

my db settings in settings.py:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'mydb',
        'USER': 'root',
        'PASSWORD': 'password',
        'HOST': 'localhost',           
        'PORT': '3306', 
    }
}

It looks like something related with InnoDB and MyISAM but I have no idea what is wrong and how to fix it. Thanks!

django version: 1.5.4
mysql version: 5.5

Also, there is no unique=True set in any of the django models.

Possible duplicate of http://stackoverflow.com/questions/8746207/1071-specified-key-was-too-long-max-key-length-is-1000-bytes — That1Guy, Dec 04 '13 at 22:56
Oh goodness! Somebody is running with a silly key declaration. — user2864740, Dec 04 '13 at 22:58
@That1Guy same error, different approachs. I am not creating any ddl on my own — ogzd, Dec 04 '13 at 23:02
Possible duplicate of [#1071 - Specified key was too long; max key length is 767 bytes](http://stackoverflow.com/questions/1814532/1071-specified-key-was-too-long-max-key-length-is-767-bytes) — miken32, Mar 22 '17 at 21:34

score 10 · Answer 1 · edited May 23 '17 at 10:30

10

solution is

ALTER DATABASE `databasename` CHARACTER SET utf8;

as already exposed here

https://stackoverflow.com/a/43365860/7708836

edited May 23 '17 at 10:30

Community

1
1

answered Apr 12 '17 at 10:07

Giuseppe De Marco

691
6
6

score 2 · Accepted Answer · answered Dec 04 '13 at 23:25

2

I found my own solution.

If you have somehow this in your my.cnf:

 character-set-server = utf8mb4
 collation-server = utf8mb4_unicode_ci

Then it creates this 'too much bytes' problem. Reverted it back to good old utf8 and it helped.

answered Dec 04 '13 at 23:25

ogzd

5,532
2
26
27

Did not work for me with Mariadb 10 GA, any other solution on your experience? Tried almost everything wihtout results... – danius Oct 15 '14 at 10:53
it has been a long time that I tackled with this problem, right now I don't remember anything :-) so goodluck to you. – ogzd Oct 20 '14 at 16:38
1

I finally could solve it by altering varchar length from index columns to lower than 256 values – danius Oct 21 '14 at 12:24
You can do it per database with `CREATE DATABASE CHARACTER SET utf8;` (from the [django manual](https://docs.djangoproject.com/en/1.9/ref/databases/#creating-your-database)). Tested successfully with MariaDB 10.0.27. – bufh Oct 02 '16 at 17:40

score 1 · Answer 3 · edited Oct 22 '18 at 06:25

1

I have fixed the subject issue creating a new database like below:

DROP DATABASE IF EXISTS databasename;

CREATE DATABASE databasename DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;

edited Oct 22 '18 at 06:25

Blue

22,608
7
62
92

answered Oct 22 '18 at 06:23

saifulislamrokon

11
3

score 0 · Answer 4 · answered Dec 20 '16 at 10:54

0

Create your database using the following command CREATE DATABASE CHARACTER SET utf8;

answered Dec 20 '16 at 10:54

Nids Barthwal

2,205
20
12

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

4 Answers4