Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes

Question

I have a migration file for my php project. I am getting this error

Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes

I do understand this issue is due to varchar(512) and charset - UTF8 but how do I solve this? I cannot change in my migration script. Can I change some properties in my.ini of MySql?

CREATE TABLE if not exists usermaster (
  user_id bigint(20) NOT NULL,
  username varchar(512) NOT NULL,
  PRIMARY KEY (user_id,username),
   KEY fk_um_idx (user_id),
  CONSTRAINT fk_um_idx FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Otherwise I had to change the charset of the table from `utf8mb4` to `utf8` — RiggsFolly, Apr 05 '18 at 12:31
?? `MySQL to 10.1.31` I think you mean to say you are using `mariaDB` and not MySQL — RiggsFolly, Apr 05 '18 at 12:31
Yes after upgrade it is MariaDB. I was getting the same error in MySQL 5.7. Therefore, I had to upgrade to check. — , Apr 05 '18 at 12:34
Latest MySQL 5.7.whatever can be used as max key length is 3000+ characters — RiggsFolly, Apr 05 '18 at 12:36
Yes that is what I read in another post but it is still not working out. — , Apr 05 '18 at 12:41
Then you had better show us the schema definition for this table and its columns — RiggsFolly, Apr 05 '18 at 12:44

score 0 · Answer 1 · answered Apr 05 '18 at 12:56

The following comment on a duplicate topic gives 2 possibilities if you insist on keeping the length of the username on 512: https://stackoverflow.com/a/41298282/5987517

I think the best solution in our case is to change your encoding from UTF-8 to latin1_general_ci

Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes

1 Answers1