MySQL HELP!! 1071 - Specified key was too long; max key length is 1000 bytes

WillGrehem

Hi guys,

I try to move my blesta install. All table imported succesfully but i got this problem:

CREATE TABLE IF NOT EXISTS `permissions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `group_id` int(10) unsigned NOT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `alias` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `action` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `plugin_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `category_id` (`group_id`),
  KEY `plugin_id` (`plugin_id`),
  KEY `alias` (`alias`,`action`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=198 ;

ERROR:

1071 - Specified key was too long; max key length is 1000 bytes

ricardo

I think certain UTF8 characters can occupy 4 bytes, so your alias KEY can potentially have values that exceed 1000 bytes.

A dirty workaround is to specify a KEY length, such as 250. It's quite unlikely you'll have collisions on values based on the last 5 bytes. Alternatively reduce the size of the alias field to 250 bytes.

WillGrehem

@ricardo said:
I think certain UTF8 characters can occupy 4 bytes, so your alias KEY can potentially have values that exceed 1000 bytes.

A dirty workaround is to specify a KEY length, such as 250. It's quite unlikely you'll have collisions on values based on the last 5 bytes. Alternatively reduce the size of the alias field to 250 bytes.

same problem

Profforg

WillGrehem said: same problem

CREATE TABLE IF NOT EXISTS `permissions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `group_id` int(10) unsigned NOT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `alias` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `action` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `plugin_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `category_id` (`group_id`),
  KEY `plugin_id` (`plugin_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=198 ;

zhuanyi

Does alias have to be a primary key? I think you should modify Id to be unique for each plugin+alias combination and have another id to group alias together

WillGrehem

@Profforg said:
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=198 ;

Hoops.
Correct work

Thank you very much guys

@ricardo said:
I think certain UTF8 characters can occupy 4 bytes, so your alias KEY can potentially have values that exceed 1000 bytes.

A dirty workaround is to specify a KEY length, such as 250. It's quite unlikely you'll have collisions on values based on the last 5 bytes. Alternatively reduce the size of the alias field to 250 bytes.

Thank you!