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?

Answer 1

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.

Answer 2

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.

Answer 3

Go to mysql config in this file my.cnf

change this line

innodb-default-row-format=compact

to

innodb-default-row-format=dynamic

Answer 4

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;

Answer 5

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;

Answer 6

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).

Answer 7

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

Answer 8

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

Answer 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';

Answer 10

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 :)

Answer 11

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.

Answer 12

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.

Answer 13

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

Answer 14

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

Answer 15

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

Answer 16

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

Answer 17

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