Mysql Error 1071 Specified Key Was Too Long

here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss specified key was too long max key length is 767 bytes innodb the workings and policies of this site About Us Learn more

Specified Key Was Too Long Max Key Length Is 1000 Bytes Mysql

about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow

Mysql Innodb Increase Max Key Length

Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each

Index Column Size Too Large. The Maximum Column Size Is 767 Bytes.

other. Join them; it only takes 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` ); I got this error message: #1071 - Specified key was too long; mysql max key length 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 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

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 error 1709 (hy000): index column size too large. the maximum column size is 767 bytes. Overflow the company Business Learn more about hiring developers or posting ads with us innodb_large_prefix Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community django.db.utils.operationalerror: (1071, 'specified key was too long; max key length is 767 bytes') of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up How to create this table? #1071 - Specified key was too long; max key length is http://stackoverflow.com/questions/1814532/1071-specified-key-was-too-long-max-key-length-is-767-bytes 1000 bytes up vote 2 down vote favorite CREATE TABLE mini ( realurl varchar(200) NOT NULL, catagory varchar(200), PRIMARY KEY (realurl,catagory), FOREIGN KEY (realurl) REFERENCES main(realurl) ) Error : `#1071 - Specified key was too long; max key length is 1000 bytes Why I can't create this table? What should I change to create this table? mysql share|improve this question asked Nov 16 '13 at 3:01 Raplus 20113 It http://stackoverflow.com/questions/20014106/how-to-create-this-table-1071-specified-key-was-too-long-max-key-length-is depends. What kind of data are you trying to describe with this table? –duskwuff Nov 16 '13 at 3:24 possible duplicate of #1071 - Specified key was too long; max key length is 767 bytes –cwd Feb 24 '15 at 1:38 add a comment| 3 Answers 3 active oldest votes up vote 2 down vote accepted PRIMARY KEY (realurl,catagory) has a size of (200 + 200) * 3 = 1200 bytes, which is greater then 1000 limit as mysql stores utf8 encoded chars as 3 bytes. Reduce the size of the the fields that make up the primary key or you can upgrade MySQL version to the latest release. Check this Error: Specified key was too long; max key length is 1000 bytes share|improve this answer answered Nov 16 '13 at 3:29 RSaha 465 add a comment| up vote 2 down vote MySQL has a prefix limitation of 767 bytes in InnoDB, and 1000 bytes in MyISAM. This has never been a problem for me, until I started using UTF-16 as the character set for one of my databases. UTF-16 can use up to 4 bytes per character which means that in an InnoDB table, you can’t have any keys longer than 191 characters. share|improve this answer answered Nov 16

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 http://dba.stackexchange.com/questions/76567/how-to-resolve-specified-key-was-too-long-max-key-length-is-767-bytes Overflow the company Business Learn more about 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 https://confluence.atlassian.com/fishkb/mysql-database-migration-fails-with-specified-key-was-too-long-max-key-length-is-1000-bytes-298978735.html 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 max key The best answers are voted up and rise to the top How to resolve: 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 max key length are using MySQL 5.5 on Percona. Also we are using utf8_general_ci and utf8. It throws the following error when creating the 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 h

to MySQL server: Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 1000 bytes at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) Cause There is a known bug with MySQL related to MyISAM, the UTF8 character set and indexes that you can checkhere. Resolution Make sure MySQL is configured with the InnoDB storage engine. Change the storage engine used by default so that new tables will always be created appropriately: set GLOBAL storage_engine='InnoDb'; For MySQL 5.6 and later, use the following: mysql> SET GLOBAL default_storage_engine = 'InnoDB'; And finally make sure that you're following the instructions provided inMigrating to MySQL. Was this helpful? Yes No Thanks for your feedback! Why was this unhelpful? It wasn't accurate It wasn't clear It wasn't relevant Submit feedback Cancel Have a question about this article? See questions about this article Powered by Confluence and Scroll Viewport Atlassian Support Ask the community Provide product feedback Contact technical support Atlassian Privacy Policy Terms of use Security Copyright © 2016 Atlassian Except where otherwise noted, content in this space is licensed under a Creative Commons Attribution 2.5 Australia License.