mysql change innodb_large_prefix

Question

I just setup debian 8.3 on a VM and installed xampp after this Tutorial. Everything is working, until I tried to create a new table:

create table testtable
(
  id int(10) not null auto_increment,
  firstname varchar(255) collate utf8mb4_german2_ci not null,
  lastname varchar(255) collate utf8mb4_german2_ci not null,
  primary key (id),
  unique key (lastname)
)engine = innodb default charset=utf8mb4, collate=utf8mb4_german2_ci

I got the error: #1709 - Index column size too large. The maximum column size is 767 bytes. Then I found out this comes from the prefix limitation which is limited to 767Byte in Innodb and I can fix this by set the innodb_large_prefix in the my.cnf file. But I can't find the file, its not under /etc/ and theres no /etc/mysql/-folder, the only my.cnf I found is in /opt/lampp/etc/, however, after I added the innodb_large_prefix=1 to the file and restarted lampp. I stil get the same error. What did I do wrong?

edit: SELECT version() returns 5.6.14, so innodb_large_prefix should be supported.

edit2: I know I can work around this by only set part of the the key as index to get under 767Byte. But I want to know here how to config the mysql correctly.

Rick James · Accepted Answer · 2018-08-25T19:26:23.627

15

Between 5.6.3 and 5.7.7 (that is if you are running MySQL 5.6 or MariaDB 10.0), there are 4 steps:

SET GLOBAL innodb_file_format=Barracuda;
SET GLOBAL innodb_file_per_table=ON;
ROW_FORMAT=DYNAMIC; -- or COMPRESSED (goes on end of CREATE)
innodb_large_prefix=1

Note

SELECT * FROM information_schema.INNODB_SYS_TABLESPACES;

will provide the file_format and row_format. Some other I_S tables provide clues of file_per_table.

edited Aug 25 '18 at 19:26

answered Mar 07 '16 at 18:37

Rick James

106,233
9
103
171

1

Its not very clear where I should put these, in the `my.cnf` file I found in the `/opt/lampp/etc/` folder? I saw some suggestions where they put `ROW_FORMAT=DYNAMIC` in the `engine` line, but it did't solve the problem for me. – yangsunny Mar 09 '16 at 07:51
1

running `SHOW GLOBAL VARIABLES LIKE 'innodb_fil%'` give me the result that `innodb_file_per_table` is on already. So I added the line `innodb_file_format=Barracuda` (without SET GLOBAL) and `innodb_large_prefix=1`to the `my.cnf` file and `ROW_FORMAT=DYNAMIC` to the end of CREATE. And its working! Thank you very much. One thing, will the file_format change have any effect on my existing data? – yangsunny Mar 09 '16 at 08:11
No. All of those settings are captured as a table is created. (You could change existing tables using ALTER.) I added a Note. – Rick James Mar 09 '16 at 19:01
2

Dear Mr. Downvoter: If there is a flaw in my Answer, please tell me; I'll fix it. – Rick James Nov 05 '17 at 18:24

score 11 · Answer 2 · answered Aug 09 '18 at 19:49

11

I'm using Mysql 5.6.17 with WAMP Server I solved the problem by editing the my.ini file Find the category [mysqld] there add the following instructions

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

Don't forget to save the changes and restart all services.

answered Aug 09 '18 at 19:49

Vladimir Salguero

4,156
2
30
39

Does this change the file format of exisiting Tables? – Wolfgang Blessen May 19 '21 at 09:26

score 8 · Answer 3 · answered Aug 12 '19 at 16:52

8

For a permanent solution, pls add following in your mariadb My.INI file-

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

I was using 10.1.38

answered Aug 12 '19 at 16:52

Techifylogic

301
3
1

Thank you. This is the only answer that worked for me :) Spent a whole day on this. – Kalesh Kaladharan Oct 19 '19 at 17:57
Maybe you should link to *why* this is: https://dev.mysql.com/doc/refman/5.6/en/innodb-row-format.html – NoBugs Nov 16 '19 at 06:52
I spent about 12hrs trying to figure out the 1071 Specified key was too long; max key length is 767 bytes error and also tried so many options over there including the Schema::defaultStringLength(191); and this solution fix the issue, THANKS!. And by the way don't forget to restart MySQL and Apache Server :-) – yehanny May 04 '20 at 16:51

score 4 · Answer 4 · edited Sep 28 '17 at 00:19

4

Go to your xampp and add this query:

`mysql>` set global innodb_file_format = `BARRACUDA`;
`mysql>` set global innodb_large_prefix = `ON`;

edited Sep 28 '17 at 00:19

RDFozz

205
1
7

answered Mar 30 '17 at 08:55

Robert Hilson

65
1

score 2 · Answer 5 · edited May 17 '17 at 10:22

2

mysql> set global innodb_file_format = `BARRACUDA`;
mysql> set global innodb_large_prefix = `ON`;

edited May 17 '17 at 10:22

Petter Friberg

19,652
9
51
94

answered May 17 '17 at 10:20

Arsath

31
1

4

Hi and welcome to SO! Although the code may speak for itself providing some details would help improve the quality of your answer! – mrun May 17 '17 at 10:49
1

Welcome to Stack Overflow! Thank you for this code snippet, which may provide some immediate help. A proper explanation [would greatly improve](//meta.stackexchange.com/q/114762) its educational value by showing *why* this is a good solution to the problem, and would make it more useful to future readers with similar, but not identical, questions. Please [edit] your answer to add explanation, and give an indication of what limitations and assumptions apply. – Toby Speight May 17 '17 at 15:36

mysql change innodb_large_prefix

5 Answers5

Linked