Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!


MySQL HELP!! 1071 - Specified key was too long; max key length is 1000 bytes
New on LowEndTalk? Please Register and read our Community Rules.

All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.

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

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

Comments

  • ricardoricardo Member
    edited April 2015

    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.

  • @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

  • 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 ;
    
  • 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

  • @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!

Sign In or Register to comment.