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

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 — user3570615, Jun 10 '15 at 17:40

BK435 · Answer 1 · 2017-05-28 01:43:56Z

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.

How do you do that "At the database level"? Perhaps you mean SET GLOBAL? – Rick James Jun 11 '15 at 0:55 — Rick James, Jun 11 '15 at 0: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 8:40 — Braek, Aug 18 '15 at 8: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 4 '16 at 14:17 — Sasank Mukkamala, Feb 4 '16 at 14:17
@Sasank, Rick was just being picky about the word usage... – BK435 Feb 5 '16 at 0:43 — BK435, Feb 5 '16 at 0:43
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 — Christopher Schultz, Jun 29 '17 at 13:24

Sasank Mukkamala · Answer 2 · 2016-02-04 14:19:33Z

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;

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 — Adam, May 21 '18 at 11:15
Thnx Adam! innodb_default_row_format = DYNAMIC worked for me! +1 – Tobias Gaertner Sep 18 '18 at 7:42 — Tobias Gaertner, Sep 18 '18 at 7:42

mysql建表问题

MySQL error: The maximum column size is 767 bytes

8 Answers