MySQL error: The maximum column size is 767 bytes

问题:

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 relating 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?

回答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.

回答2:

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;

回答3:

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

回答4:

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.

回答5:

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

回答6:

Just add the following options to my.cnf

[mysqld]
innodb_file_format = Barracuda
innodb_file_per_table = 1
innodb_large_prefix

Then, restart mysql server the problem will be resolved.

回答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

回答8:

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.