Jump to content
ugintl

how to resolve this error

Recommended Posts

Arrrh! Can't install database.
SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes, query was: CREATE TABLE `email_triggers` ( `id` int(10) NOT NULL AUTO_INCREMENT, `enabled` enum('0','1') COLLATE utf8_unicode_ci NOT NULL, `trigger_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `observer` varchar(255) COLLATE utf8_unicode_ci NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `trigger_name_2` (`trigger_name`,`observer`), UNIQUE KEY `trigger_name_3` (`trigger_name`,`observer`), KEY `trigger_name` (`trigger_name`), KEY `observer` (`observer`), KEY `enabled` (`enabled`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Share this post


Link to post
Share on other sites

First, you have 2 identical keys that are both going to have the same problem. They're identical, you only need one, it's only going to slow down the database to store the same information twice. Second, the problem is because the maximum key length on your server is 1000 bytes and you're trying to put 2 UTF8 columns at 255 characters each. It's not a great idea to put a varchar in an index, it will make the index pretty big pretty quick. The problem in your case is that UTF8 is a multi-byte character set, it will use 3 bytes to store a character. So, with 2 columns at 255 characters each you're asking it to store 2 * 255 * 3 = 1530 bytes. You can reduce the column size, or change the index to only index the first 100 characters or so instead of all 255, or change the options on the server.

 

http://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html

 

By default, an index key for a single-column index can be up to 767 bytes. The same length limit applies to any index key prefix. See Section 13.1.11, “CREATE INDEX Syntax”. For example, you might hit this limit with a column prefix index of more than 255 characters on a TEXT or VARCHAR column, assuming a UTF-8 character set and the maximum of 3 bytes for each character. When the innodb_large_prefix configuration option is enabled, this length limit is raised to 3072 bytes, for InnoDB tables that use the DYNAMIC and COMPRESSED row formats.

 

...

 

The InnoDB internal maximum key length is 3500 bytes, but MySQL itself restricts this to 3072 bytes. This limit applies to the length of the combined index key in a multi-column index.

You may also want to upgrade MySQL if you're not on the current version, that manual is for version 5.7.

 

There's more discussion about it here:

 

http://stackoverflow.com/questions/8746207/1071-specified-key-was-too-long-max-key-length-is-1000-bytes

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...