MySQL error: The maximum column size is 767 bytes

Question

When I run a program which does something with MySQL, I got this error message:

2015-06-10 15:41:12,250 ERROR app.wsutils 419 INCRON: Error: ('HY000', '[HY000] [MySQL][ODBC 5.2(w) Driver][mysqld-5.7.7-rc-log]Index column size too large. The maximum column size is 767 bytes. (1709) (SQLExecDirectW)')

I Googled a little bit and found this error might be related to the innodb_large_prefix option. However, I am using MySQL 5.7.7 RC, which has already set innodb_large_prefix to be "ON" (checked in MySQL Workbench), allowing up to 3072 bytes. I am not sure if that is the problem with innodb_large_prefix or not.

Anyway, does anyone have an idea how to fix this problem?

I have no idea... do some creations of tables or calculations? I am just a front end user... — user3570615, Jun 10 '15 at 17:40
Does this answer your question? [#1071 - Specified key was too long; max key length is 767 bytes](https://stackoverflow.com/questions/1814532/1071-specified-key-was-too-long-max-key-length-is-767-bytes) — miken32, Dec 03 '19 at 01:55

score 55 · Answer 1 · answered Dec 12 '21 at 06:41

55

From Wamp Version 3.2.6
Just edit this file: C:\wamp64\bin\mysql\mysql8.0.27\my.ini Change the config for innodb-default-row-format value
from innodb-default-row-format=compact
to innodb-default-row-format=dynamic
Restart mysql

answered Dec 12 '21 at 06:41

Python

699
5
5

4

This is the simplest answer, although the question was asked over 6 years ago. – Thomas Cayne Jan 19 '22 at 23:05
Awesome, worked just great. Wamp Server on Windows using MySQL 5.7.x on command line restoring a big dump – Enrique Dec 01 '22 at 20:15
Thanks, worked smooth for me using wamp with mysql 8.0 – Camilo Lizarazo Jan 18 '23 at 00:20
This is such a timesaver. I had no idea the creators built this in.. but of course they did! ;) – Herbert Van-Vliet Feb 03 '23 at 00:09

score 37 · Answer 2 · answered Feb 04 '16 at 14:19

37

With the help of the answer given by BK435, I did the following and solved the problem.

set global innodb_file_format = BARRACUDA;
set global innodb_large_prefix = ON;
create table test (........) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

answered Feb 04 '16 at 14:19

Sasank Mukkamala

1,504
13
22

17

As of MariaDB 10.2.2, of course :) You can also set `SET GLOBAL innodb_default_row_format = 'DYNAMIC';` – Adam May 21 '18 at 11:15
Thnx Adam! innodb_default_row_format = DYNAMIC worked for me! +1 – Tobias Gaertner Sep 18 '18 at 07:42
This approach also worked for me. I was exporting from Enterprise MySQL and attempting to import into MariaDB 10.0.x. ... on a side note, the import worked perfectly fine on MariaDB 10.3.x (for whatever reason). – Mike Broyles Aug 14 '19 at 11:54
Makes sure your MySQL version is greater than 5.5 or none of these settings will have an effect. – Shadoath Oct 17 '19 at 22:20
Thanks @Adam. It also worked for me in 10.3.17-MariaDB – rodrigobb Jul 16 '20 at 19:59

BK435 · Answer 3 · 2017-05-28T01:43:56.933

22

Your column that you are trying to index is too large and your settings must not be correct for innodb_large_prefix. There are a couple prerequisites parameters that also have to be set in order for innodb_large_prefix to work correctly.

You can check to make sure that innodb_large_prefix is set by running:

show global variables like 'innodb_lar%';

Here are a couple prerequisites for using innodb_large_prefix:

You need to set your global variable innodb_file_format=BARRACUDA

to check settings run: show global variables like 'innodb_fil%';

At the table level you have to use ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED

for Innodb, rows are stored in COMPACT format (ROW_FORMAT=COMPACT) by default.

edited May 28 '17 at 01:43

answered Jun 10 '15 at 21:27

BK435

3,076
3
19
27

How do you do that "At the database level"? Perhaps you mean `SET GLOBAL`? – Rick James Jun 11 '15 at 00:55
You better configure these settings in my.cnf or else they are stored in memory and hence lost when restarting MySQL/MariaDB. – Braek Aug 18 '15 at 08:40
@RickJames - Yes doing this at a database level is enough. Infact, doing it at the particular table level also will solve the problem. – Sasank Mukkamala Feb 04 '16 at 14:17
3

In some cases where I was trying to `CONVERT TO CHARACTER SET`, I was using MySQL 5.5 where the default `ROW_FORMAT` for InnoDB was `COMPACT`. Using `ALTER TABLE foo ROW_FORMAT=DYNAMIC, CONVERT TO CHARACTER SET charset` did the trick. – Christopher Schultz Jun 29 '17 at 13:24
2

If you, like me, need to change your mysqldump file to add `ROW_FORMAT=DYNAMIC` to each create table statement, try the following: ```sed -i 's/ENGINE=InnoDB/ENGINE=InnoDB ROW_FORMAT=DYNAMIC/g' database_dump.sql``` – disperse Dec 15 '17 at 16:05
Thanks @ChristopherSchultz, I wasn't aware MySQL 5.5 was configured like this and your suggestion did the trick! – Jiminy Cricket Apr 02 '18 at 04:59
Hey @disperse, thanks a lot. This did the trick for me. Saved me many hours of work trying to upgrade mysql too, which is what others 'ordered' me to do. – Julius Jun 27 '19 at 11:41

score 17 · Answer 4 · answered Aug 12 '19 at 16:34

I was using MariaDB version 10.1.38 and used all of the below given commands but it did not work -

set global innodb_large_prefix = ON;
Query OK, 0 rows affected (0.00 sec)

set global innodb_file_per_table = ON;
Query OK, 0 rows affected (0.00 sec)

set global innodb_file_format = Barracuda;
Query OK, 0 rows affected (0.00 sec)

SET GLOBAL innodb_default_row_format = 'DYNAMIC';

Because after you restart your MySQL (or MariaDB), these settings will not reflect back using the command at the mysql prompt: show variables like 'innodb%';

Then I edited My.ini and added these settings in the file at below location- C:\xampp\mysql\bin\my.ini

## 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'

source:https://www.experts-exchange.com/questions/28675824/Why-am-I-unable-to-turn-innodb-large-prefix-ON-successfully-Every-time-I-reboot-mySql-on-my-Ubuntu-VPS-it-resets-to-OFF.html

The addition of `innodb_default_row_format = 'DYNAMIC'` did the trick for me. Thanks. — TryHarder, Jan 25 '21 at 06:07

score 9 · Answer 5 · answered Jun 07 '20 at 06:34

9

For me using Mariadb 10.1.31, just add this while you login in Mysql CLI:

SET GLOBAL innodb_file_format = Barracuda;

SET GLOBAL innodb_file_per_table = ON;

SET GLOBAL innodb_large_prefix = ON;

SET GLOBAL innodb_default_row_format = 'DYNAMIC';

answered Jun 07 '20 at 06:34

Game Terserah

309
3
5

SET GLOBAL innodb_default_row_format = 'DYNAMIC'; might be enough – Alon Laniado May 30 '22 at 07:54

score 6 · Answer 6 · edited Jun 24 '22 at 03:30

6

You need to change the innodb-default-row-format variable to dynamic.

If you are using phpMyAdmin, navigate to variables and search for row format.
If you are using WampServer, navigate to my.ini file like: C:\wamp64\bin\mysql\mysql8.0.27\my.ini

For more info, visit MySQL Manual

edited Jun 24 '22 at 03:30

Nirav Bhoi

559
9
17

answered Mar 02 '22 at 18:54

IgniteCoders

4,834
3
44
62

score 5 · Answer 7 · edited Feb 07 '22 at 07:32

5

Just add the following options to my.cnf

    [mysqld]
    innodb_file_format=Barracuda
    innodb_file_per_table=1
    innodb_large_prefix=1

Then, restart mysql server the problem will be resolved.

edited Feb 07 '22 at 07:32

Atai Ambus

89
1
2
10

answered Feb 23 '17 at 10:46

Son Nguyen

195
1
10

2

You forgot to provide value to `innodb_large_prefix` – Pavlo Zhukov Sep 07 '18 at 08:55
1

I confirm this solution works for MariaDB 10.1.44, thanks! – Cristian Romanescu Mar 03 '20 at 14:28

score 4 · Answer 8 · answered Dec 05 '22 at 17:58

4

Go to mysql config in this file my.cnf

change this line

innodb-default-row-format=compact

to

innodb-default-row-format=dynamic

answered Dec 05 '22 at 17:58

julien AWONGA

41
3

score 3 · Answer 9 · edited Jan 23 '22 at 20:22

3

This worked for me:

From Wamp Version 3.2.6
Just edit this file: C:\wamp64\bin\mysql\mysql8.0.27\my.ini
Change the config for innodb-default-row-format value
from innodb-default-row-format=compact
to innodb-default-row-format=dynamic
Restart mysql

edited Jan 23 '22 at 20:22

ZygD

22,092
39
79
102

answered Jan 23 '22 at 14:38

Amavi d'almeida

31
1

1

No need to repost what was already answered in another answer. – ZygD Jan 23 '22 at 20:23

score 1 · Answer 10 · answered Feb 16 '18 at 13:17

I had the same error despite having innodb_large_prefix configured correctly.

The issue was in used collation. My db had default collation set to utf8mb4_bin (you can check it in phpmyadmin "Operations" tab for database). It means it uses 4 bytes per char, while utf8 collation (e.g. utf8_unicode_ci) uses 3 bytes per char.

in this case you can either use different collation e.g. by adding DEFAULT CHARSET=utf8 at the end of the CREATE TABLE statement, or limit the index size by using just a part of the column value like KEY 'identifier' (column1(5),column2(10)).

See also related question: #1071 - Specified key was too long; max key length is 767 bytes

score 1 · Answer 11 · answered Jul 11 '18 at 21:32

In my case (MySQL version 5.6) the issue was that I was trying to create a table with a column that can have up to 256 characters (the db uses utf8 collation), so 3 bytes per 1 utf8 character = 256*3=768 bytes. The fix was to simply have 255 characters instead of 256.

I could also set innodb_large_prefix, like others suggest, but in my case it was easier to just have fewer symbols.

score 1 · Answer 12 · answered Sep 02 '21 at 06:24

1

Just like @sasank-mukkamala said , for me only adding ROW_FORMAT=DYNAMIC to create command did it rightly.

create table `NameOfTheTable` (........) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;

answered Sep 02 '21 at 06:24

Hesam Moosapour

510
5
12

score 1 · Answer 13 · answered Jan 09 '22 at 19:11

1

Im using Mysql 5.7

Click PHPMyAdmin (home) Click Variables On innodb default row formart, click edit then enter 2, then click save It will show variable value as dynamic Import table again

answered Jan 09 '22 at 19:11

Grey Hound

11
1

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jan 10 '22 at 03:01

score 0 · Answer 14 · answered Aug 28 '18 at 08:17

0

I had this problem because I tried to create a String primary key with varchar(254). Easy to overlook sometimes.. So double check your index type and length as well :)

answered Aug 28 '18 at 08:17

Simon Lippens

121
1
4

score 0 · Answer 15 · answered Feb 26 '21 at 23:56

0

In case someone is working with MySQL 5.6 the only solution that I found was to update to MySQL 5.7 and setting my.cnf as mentioned in previous comments (https://stackoverflow.com/a/57465002/2300390).

answered Feb 26 '21 at 23:56

Carlos Marmolejo

125
1
2
11

score -3 · Answer 16 · edited May 23 '17 at 10:31

-3

I had the same error but on a different issue. I got this error while importing a data (data and schema) script. Deleting the Unique Index fixed the issue for me.

Answer taken from this link

edited May 23 '17 at 10:31

Community

1
1

answered Jan 15 '17 at 07:29

Himanshu Patel

754
8
13

score -6 · Answer 17 · edited Jun 20 '18 at 10:21

-6

Set the below system variables:

innodb_buffer_pool_size.................................... 702545920
innodb_file_format......................................... Barracuda
innodb_file_format_check................................... ON
innodb_file_format_max..................................... Barracuda
innodb_file_per_table...................................... ON
innodb_large_prefix........................................ ON
innodb_log_file_size....................................... 50331648

Also, make sure when you create your schema you create it as Latin1. That is what finally fixed me.

edited Jun 20 '18 at 10:21

PeterJ

3,705
28
51
71

answered Feb 10 '16 at 20:00

T Wheeler

29
1
6

3

Creating your schema in latin1 is probably not a good idea for most sites that require internationalization. The reason that worked for you is because latin1 requires fewer bytes to store teh the same length varchar. However, latin1 won't be compatible with sites that need to store utf8 or utf8mb4 characters. – jsears Mar 31 '16 at 16:54

MySQL error: The maximum column size is 767 bytes

17 Answers17

Linked

Related