Mysql Error 1071 Key Too Long

here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings mysql innodb increase max key length and policies of this site About Us Learn more about Stack Overflow specified key was too long max key length is 767 bytes innodb the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation

Mysql Max Key Length

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 other. Join them;

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

it only takes a minute: Sign up Mysql::Error: Specified key was too long; max key length is 1000 bytes up vote 16 down vote favorite 4 script/generate acts_as_taggable_on_migration rake db:migrate causes Mysql::Error: Specified key was too long; max key length is 1000 bytes: CREATE INDEX `index_taggings_on_taggable_id_and_taggable_type_and_context` ON `taggings` (`taggable_id`, `taggable_type`, `context`) What should I do? Here is my database encoding: error 1709 (hy000): index column size too large. the maximum column size is 767 bytes. mysql> SHOW VARIABLES LIKE 'character\_set\_%'; +--------------------------+--------+ | Variable_name | Value | +--------------------------+--------+ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | +--------------------------+--------+ 7 rows in set (0.00 sec) mysql sql ruby-on-rails indexing mysql-error-1071 share|improve this question edited May 23 '11 at 21:32 OMG Ponies 199k37360417 asked Aug 15 '10 at 20:15 amaseuk 1,18231537 Provide more information first. What is being migrated? –Pekka 웃 Aug 15 '10 at 20:23 github.com/mbleigh/acts-as-taggable-on –amaseuk Aug 15 '10 at 20:25 github.com/mbleigh/acts-as-taggable-on/blob/master/generator‌s/… –amaseuk Aug 15 '10 at 20:26 1 Similar problem here community.engineyard.com/discussions/problems/… that got resolved but don't know how. –amaseuk Aug 16 '10 at 15:07 add a comment| 5 Answers 5 active oldest votes up vote 40 down vote accepted This is solely a MySQL issue - MySQL has different engines - MyISAM, InnoDB, Memory... MySQL has different limits on the amount of space you can use to define

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

Innodb_large_prefix

company Business Learn more about hiring developers or posting ads with us Stack Overflow mysql max key length is 3072 bytes Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 django.db.utils.operationalerror: (1071, 'specified key was too long; max key length is 767 bytes') million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up #1071 - Specified key was too long; max key length is 1000 bytes up vote 25 down vote favorite 13 http://stackoverflow.com/questions/3489041/mysqlerror-specified-key-was-too-long-max-key-length-is-1000-bytes I know questions with this title have been answered before, but please do read on. I've read thoroughly all the other questions/answers on this error before posting. I am getting the above error for the following query: CREATE TABLE IF NOT EXISTS `pds_core_menu_items` ( `menu_id` varchar(32) NOT NULL, `parent_menu_id` int(32) unsigned DEFAULT NULL, `menu_name` varchar(255) DEFAULT NULL, `menu_link` varchar(255) DEFAULT NULL, `plugin` varchar(255) DEFAULT NULL, `menu_type` int(1) DEFAULT NULL, `extend` varchar(255) DEFAULT NULL, http://stackoverflow.com/questions/8746207/1071-specified-key-was-too-long-max-key-length-is-1000-bytes `new_window` int(1) DEFAULT NULL, `rank` int(100) DEFAULT NULL, `hide` int(1) DEFAULT NULL, `template_id` int(32) unsigned DEFAULT NULL, `alias` varchar(255) DEFAULT NULL, `layout` varchar(255) DEFAULT NULL, PRIMARY KEY (`menu_id`), KEY `index` (`parent_menu_id`,`menu_link`,`plugin`,`alias`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Does anyone have idea why and how to fix it? The catch is - this same query works perfectly on my local machine, and worked as well on my previous host. Btw.it's from a mature project - phpdevshell - so I'd guess these guys know what they are doing, although you never know. Any clue appreciated. I'm using phpMyAdmin. mysql phpmyadmin mysqldump share|improve this question asked Jan 5 '12 at 16:42 CodeVirtuoso 2,10883255 add a comment| 5 Answers 5 active oldest votes up vote 54 down vote accepted As @Devart says, the total length of your index is too long. The short answer is that you shouldn't be indexing such long VARCHAR columns anyway, because the index will be very bulky and inefficient. The best practice is to use prefix indexes so you're only indexing a left substring of the data. Most of your data will be a lot shorter than 255 characters anyway. You can declare a prefix length per column as you define the index. For example: ... KEY `index` (`parent_menu_id`,`menu_link`(50),`plugin`(50),`alias`(50)) ... But what's the best prefix length for

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 hiring developers http://dba.stackexchange.com/questions/76567/how-to-resolve-specified-key-was-too-long-max-key-length-is-767-bytes 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 https://confluence.atlassian.com/fishkb/mysql-database-migration-fails-with-specified-key-was-too-long-max-key-length-is-1000-bytes-298978735.html 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: Specified key was max key 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 following index on Lock_ table: max key length 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:02 What is the charset of the table and o

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.