MySQL Forums
Forum List  »  InnoDB

Re: innodb_large_prefix and ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.
Posted by: Tom Robinson
Date: May 02, 2014 12:07AM

REPOSTING HERE

Hi,

I recently installed a product that uses MySQL in the backend but it failed to install when building an index due to the following error:

ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.

The OS is CentOS 6.5 and I have tried on Oracle MySQL 5.5 and 5.6. The mysql and product DBs are using character set UTF8.

Reading the documentation, querying the database and googling I find that MySQL UTF8 can use up to three (3) byes per character. The table column for the product had been defined as NAME VARCHAR(256), so indexing on it would consume 768 bytes (one too many for the prefix_length and hence the error).

Ok, so I can define the column as NAME VARCHAR(255) or create the index as NAME(255) and all is good.

I also read that, if set, innodb_large_prefix allows larger prefixes (up to 3072 bytes) so I thought I'd try that instead of hacking the product's database creation scripts.

I can't get that to work! I have tried setting innodb_large_prefix but it has no affect on the index creation. On a column larger than VARCHAR(255) it still issues the error:

ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.

I have set (as per the doco http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_large_prefix):

innodb_large_prefix | ON
innodb_file_format | Barracuda
innodb_file_format_check | ON
innodb_file_format_max | Barracuda
innodb_file_per_table | ON

SHOW TABLE STATUS indicates that the row_format for the table that defines the column I'm attempting to index is 'Compact'. Does that mean the same as Compressed?

My question is, how can I use the innodb_large_prefix option? Does this apply to VARCHAR or only to another column type?

Kind regards,
Tom

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.