How to create this table? #1071 - Specified key was too long; max key length is 1000 bytes

Question

CREATE TABLE mini
(
realurl varchar(200) NOT NULL,
catagory varchar(200),
PRIMARY KEY (realurl,catagory),
FOREIGN KEY (realurl) REFERENCES main(realurl)
)

Error : `#1071 - Specified key was too long; max key length is 1000 bytes

Why I can't create this table? What should I change to create this table?

It depends. What kind of data are you trying to describe with this table? — , Nov 16 '13 at 03:24
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) — cwd, Feb 24 '15 at 01:38
Refer https://stackoverflow.com/a/52778785/2137210 for solution — Pratik, Oct 12 '18 at 12:21
Remove the key and recreate. Btw URLs can be way longer that 200 chars. — theking2, Dec 26 '22 at 19:26

score 27 · Answer 1 · edited Nov 12 '17 at 09:45

This common problem mostly occurs because by default MySql uses character limit for column names. Which is:

INNODB utf8mb4 VARCHAR(191)

What we need is given below:

INNODB utf8 VARCHAR(255)

To get rid of this issue, I would suggest to use utf8 while creating database along with COLLATE set to utf8_general_ci. I prefer to use below command to create database in MariaDb or MySql:

DROP DATABASE IF EXISTS <YOUR_DATABASE_NAME>;
CREATE DATABASE <YOUR_DATABAE_NAME> DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;

Above command helped me in my specific scenario. I use 'Sequelize' to create tables on run time. When I tried to run my code in Ubuntu after creating database with simple 'CREATE DATABASE ' command, I got this 1071 error.

Running above command with utf8 character set and collate helped me get rid of the error.

score 18 · Accepted Answer · edited May 23 '17 at 11:47

PRIMARY KEY (realurl,catagory) has a size of (200 + 200) * 3 = 1,200 bytes, which is greater than the 1,000 byte limit, as MySQL stores utf8 encoded chars as 3 bytes.

You'll need to reduce the size of the the fields that make up the primary key or you can upgrade MySQL version to the latest release.

Also see this other question: Error: Specified key was too long; max key length is 1000 bytes.

score 9 · Answer 3 · edited Oct 19 '20 at 13:04

9

I solved this issue. Create your database using the following command .

CREATE DATABASE <DB_NAME> CHARACTER SET utf8;

edited Oct 19 '20 at 13:04

Yasar Arafath

605
1
9
30

answered Dec 20 '16 at 10:58

Nids Barthwal

2,205
20
12

2

CREATE DATABASE CHARACTER SET utf8; – HungryCoder Mar 26 '18 at 01:49

score 6 · Answer 4 · answered Nov 16 '13 at 03:35

MySQL has a prefix limitation of 767 bytes in InnoDB, and 1000 bytes in MyISAM. This has never been a problem for me, until I started using UTF-16 as the character set for one of my databases. UTF-16 can use up to 4 bytes per character which means that in an InnoDB table, you can’t have any keys longer than 191 characters.

score 3 · Answer 5 · answered Mar 12 '18 at 11:11

You can drop "your database" and create again with:

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

score 2 · Answer 6 · answered Feb 21 '19 at 09:45

To fix this all you have to do is edit your AppServiceProvider.php file and inside the boot method set a default string length:

use Illuminate\Support\Facades\Schema;

public function boot()
{
    Schema::defaultStringLength(191);
}

Laravel 5.4 made a change to the default database character set, and it’s now utf8mb4 which includes support for storing emojis. This only affects new applications and as long as you are running MySQL v5.7.7 and higher you do not need to do anything.

For those running MariaDB or older versions of MySQL you may hit this error when trying to run migrations:

[Illuminate\Database\QueryException]
SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes (SQL: alter table users add unique users_email_unique(email))

[PDOException]
SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes

score 2 · Answer 7 · answered Aug 12 '19 at 17:02

2

Try adding below settings in your My.INI file for permanent solution -

## Innodb settings to bypass error of max size 737
innodb-file-format=barracuda
innodb-file-per-table=ON
innodb-large-prefix=ON
## Above 3 didnot work so i added below
innodb_default_row_format = 'DYNAMIC'

answered Aug 12 '19 at 17:02

Techifylogic

451
4
3

perfect for local/dev not sure of implications for a prod server - thanks! – Peter Tracey Feb 06 '20 at 14:26

score 1 · Answer 8 · answered Aug 05 '15 at 11:25

1

If you had changed innodb_log_file_size try to restore the previous value.

answered Aug 05 '15 at 11:25

AnkitK

388
3
10

score 1 · Answer 9 · answered Feb 05 '21 at 07:58

1

For MySQL 5.6 and later, use the following:

mysql> SET GLOBAL default_storage_engine = 'InnoDB';

answered Feb 05 '21 at 07:58

Mukesh Chauhan

791
8
9

How to create this table? #1071 - Specified key was too long; max key length is 1000 bytes

9 Answers9