Mysql Error Code 1071

here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or specified key was too long max key length is 1000 bytes mysql posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss mysql innodb increase max key length Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes

Specified Key Was Too Long; Max Key Length Is 767 Bytes Mysql

a minute: Sign up #1071 - Specified key was too long; max key length is 767 bytes up vote 194 down vote favorite 43 When I executed the following command: ALTER TABLE `mytable` ADD UNIQUE ( `column1` , `column2` );

Mysql Innodb_large_prefix

I got this error message: #1071 - Specified key was too long; max key length is 767 bytes Information about column1 and column2: column1 varchar(20) utf8_general_ci column2 varchar(500) utf8_general_ci I think varchar(20) only requires 21 bytes while varchar(500) only requires 501 bytes. So the total bytes are 522, less than 767. So why did I get the error message? #1071 - Specified key was too long; max key length is 767 bytes mysql byte varchar mysql-error-1071 share|improve this question edited May mysql max key length 23 '11 at 21:32 OMG Ponies 199k37360417 asked Nov 29 '09 at 3:18 Steven 5,2993480112 Because its not 520 bytes, but rather, 2080 bytes, which far exceeds 767 bytes, you could do column1 varchar(20) and column2 varchar(170). if you want a character/byte equiv, use latin1 –Rahly Dec 18 '15 at 0:21 add a comment| 17 Answers 17 active oldest votes up vote 137 down vote accepted 767 bytes is the stated prefix limitation for InnoDB tables - its 1,000 bytes long for MyISAM tables. According to the response to this issue, you can get the key to apply by specifying a subset of the column rather than the entire amount. IE: ALTER TABLE `mytable` ADD UNIQUE ( column1(15), column2(200) ); Tweak as you need to get the key to apply, but I wonder if it would be worth it to review your data model regarding this entity to see if there's improvements that would allow you to implement the intended business rules without hitting the MySQL limitation. share|improve this answer edited Oct 15 '14 at 20:20 The Alchemist 2,6901017 answered Nov 29 '09 at 3:52 OMG Ponies 199k37360417 To apply by specifying a subset of the column rather than the entire amount. A good solution. –Steven Nov 29 '09 at 4:14 @OMGPonies: Do you happen to know, if DB2/MSSQL/Oracle have the same limitation on index size? For example HSQL does not have such limitation... –dma_k N

CI MySQL Sandbox MariaDB data chef Adtech Twitter GitHub RSS Using Innodb_large_prefix to Avoid ERROR 1071 If you've ever tried to add an index that includes a long varchar column

Error 1709 (hy000): Index Column Size Too Large. The Maximum Column Size Is 767 Bytes.

to an InnoDB table in MySQL, you may have seen this mysql index column size too large. the maximum column size is 767 bytes. error: 1 ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes The django.db.utils.operationalerror: (1071, 'specified key was too long; max key length is 767 bytes') character limit depends on the character set you use. For example if you use latin1 then the largest column you can index is varchar(767), but if you use http://stackoverflow.com/questions/1814532/1071-specified-key-was-too-long-max-key-length-is-767-bytes utf8 then the limit is varchar(255). There is also a separate 3072 byte limit per index. The 767 byte limit is per column, so you can include multiple columns (each 767 bytes or smaller) up to 3072 total bytes per index, but no column longer than 767 bytes. (MyISAM is a little different. It has a 1000 byte http://mechanics.flite.com/blog/2014/07/29/using-innodb-large-prefix-to-avoid-error-1071/ index length limit, but no separate column length limit within that). One workaround for these limits is to only index a prefix of the longer columns, but what if you want to index more than 767 bytes of a column in InnoDB? In that case you should consider using innodb_large_prefix, which was introduced in MySQL 5.5.14 and allows you to include columns up to 3072 bytes long in InnoDB indexes. It does not affect the index limit, which is still 3072 bytes as quoted in the manual: 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. Read on for details and examples about innodb_large_prefix. Here are a few pre-requisites for using innodb_large_prefix: At the database level you have to use innodb_file_format=BARRACUDA At the table level you have to use ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED The default file format is still Antelope for backwards compatibility, and the default row format is

log in tour help Tour Start here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about http://dba.stackexchange.com/questions/76567/how-to-resolve-specified-key-was-too-long-max-key-length-is-767-bytes hiring developers or posting ads with us Database Administrators Questions Tags Users Badges Unanswered Ask Question _ Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute: Sign up Here's how it works: Anybody can ask a question Anybody can answer The best answers are voted up and rise to the top How to resolve: max key Specified key was too long; max key length is 767 bytes [duplicate] up vote 1 down vote favorite This question already has an answer here: Specified key was too long; max key length is 1000 bytes in mysql 5.6 5 answers Our server is creating a lot of tables and one of them is the Lock_ table. We are using MySQL 5.5 on Percona. Also we are using utf8_general_ci and utf8. It throws the following error when creating the max key length following index on Lock_ table: ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes Here is the create-table query: create table Lock_ ( uuid_ VARCHAR(75) null, lockId LONG not null primary key, companyId LONG, userId LONG, userName VARCHAR(75) null, createDate DATE null, className VARCHAR(75) null, key_ VARCHAR(200) null, owner VARCHAR(255) null, inheritable BOOLEAN, expirationDate DATE null ); Index query: create unique index IX_DD635956 on Lock_ (className, key_, owner); This problem is happening on one of our prod servers (previously it was standalone mysql and then later moved to percona, but the problem was present in both the editions), but this is working fine on our dev environment. So is there a way we can update the prod environment's index length or something so that it does not throw this error? Thanks mysql index mysql-5.5 percona share|improve this question asked Sep 15 '14 at 12:27 Prakash K 11515 marked as duplicate by ypercubeᵀᴹ, Max Vernon, Paul White♦, RLF, RolandoMySQLDBAmysql Users with the mysql badge can single-handedly close mysql questions as duplicates and reopen them as needed. Sep 15 '14 at 19:59 This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question. Are you sure this create unique index IX_DD635956 ... statement throws the error and not something else? –ypercubeᵀᴹ Sep 15 '14 at 13:0