Jump to content

how to resolve this error


ugintl

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

Link to comment
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

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...