mysql change innodb_large_prefix

user_4452759 0
Viewed 94484

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.

utf8mb4 innodb mysql
7 Answers

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.

user_41917160

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

user_114002620

I'm using Mysql 8.0^, I got this error while trying to do PHP artisan migrate on laravel 9 To solve it go to Wamp server hover on Mysql, open and edit my.ini step 1 Change default-storage-engine=MYISAM to default-storage-engine=InnoDB that's the final setting

SAVE IT AND RESTART ALL SERVICES

user_195129720
user_45956750
Related