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

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 2

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 3

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