Database Error 1071 While Doing Query
Contents |
version roll out and QA » phpList, maintainer needed » UI lite phpList plugins » phpList API » rssmanager phpList.org My View | View Issues | Change Log | RoadmapView Issue Details[Jump error 1071 specified key was too long max key length is 767 bytes to Notes] [Print] IDProjectCategoryView StatusDate SubmittedLast Update0008583phplistAll Otherpublic21-11-06 13:3108-08-11 21:30ReporterdanieleintPrioritynormalSeverityminorReproducibilityalwaysStatusresolvedResolutionfixedPlatform-OS-OS Version-Product Version2.10.3Target Version2.10.XFixed in error 1709 (hy000): index column size too large. the maximum column size is 767 bytes. Version2.10.15Summary0008583: Unable to install with UTF-8 database using MySql 4.1DescriptionMysql limit the index lenght to 1000 bytes. The index created error 1071 42000 specified key was too long max key length is 1000 bytes for email and name exceed 1000 bytes. Steps To ReproduceMySql shell: create database php_list_test charset utf8; From web interface: Database error 1146 while doing query Table 'php_list_test.phplist_user_blacklist_data' doesn't exist Initialising table user_blacklist_data Database specified key was too long max key length is 1000 bytes mysql error 1071 while doing query Specified key was too long; max key length is 1000 bytes ... failed The offending query is: CREATE TABLE phplist_user_blacklist_data ( email varchar(255) not null unique,name varchar(100) not null,data text,index emailidx (email),index ema ilnameidx (email,name) See also: http://bugs.mysql.com/bug.php?id=4541 [^] Additional InformationQuick and diry fix (don't know the consequences), create the dabase manually and: a) Use a shorter name. b) Skip the
Innodb_large_prefix
creation of the index index emailnameidx (email,name) TagsNo tags attached.Attached Files Relationships related to0003721closedphplist 2.10.x related to0010609resolvedmichielerror on creating database Relationships Notes (0021255) michiel (manager) 21-11-06 22:58 it might be useful to post a link in the forums to this issue, so people can find it. (0030052) user1308 03-08-07 20:09 The bug is still in mysql 5, should we do something about it more than advice to not use UTF-8? Or maybe change "name" to be varchar(66)? I don't know what for the name is other than to store the "reason" word when user unsubscribe? (0040988) h2b2 (manager) 18-02-08 21:09 This fix was posted on the forum by rojaro with regard to the "Database error 1071 while doing query Specified key was too long; max key length is 1000 bytes" error message: ******** QUOTE ******** The "max key length" limitation is a known problem of MySQL. Your database is configured to use UTF-8 by default, which means that MySQL reserves 3 bytes per char instead of one byte per char. Since the max key length limit is hardcoded to 1000 bytes for MyISAM tables regardless of the currently configured charset and is not going to be fixe
Does iTest 4.2 support MySQL 5.6 for external database ? I didn't see this error using older MySQL. I encountered "ERROR 1071 (42000): Specified key was
Mysql Max Key Length
too long; max key length is 767 bytes" during external database setup. C:\Program mysql innodb increase max key length Files\MySQL\MySQL Server 5.6\bin>mysql -u admin -p Enter password: *** Welcome to the MySQL monitor. Commands end with ; or \g. mariadb max key length Your MySQL connection id is 25 Server version: 5.6.10-log MySQL Community Server (GPL) Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation https://mantis.phplist.org/view.php?id=8583 and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> mysql> create database itestdb; Query OK, 1 row affected (0.00 sec) mysql> use itestdb; Database changed mysql> source 104746415.MySqlSchema.sql Query OK, 0 rows affected (0.08 sec) Query OK, 0 rows affected (0.05 sec) Query OK, 0 rows affected (0.25 https://forums.spirent.com/questions/19993/mysql-56-error-1071-specified-key-was-too-long.html sec) Records: 0 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected, 1 warning (0.27 sec) Records: 0 Duplicates: 0 Warnings: 1 ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes Query OK, 0 rows affected (0.28 sec) Thank you People who like this Close 0 Comment 10 |950 characters needed characters left characters exceeded ▼ Viewable by all users Viewable by moderators Viewable by moderators and the original poster Advanced visibility Viewable by all users 1 Reply · Add your reply Sort: Answer by udaya · Mar 01, 2013 at 04:48 PM Hi Yas, I have my set up with both 5.1 and 5.5 and not seeing any issues. I am suspecting there may be some issues with 5.6 with colmn data type defination due to extra large( not sure). Regards, Udaya Comment EricGregMiller People who like this Close 1 · Share 10 |950 characters needed characters left characters exceeded ▼ Viewable by all users Viewable by moderators Viewable by moderators and the original poster Advanced visibility Viewable by all users Your answer Hint: You can notify a user about this post by typing @username Attachments: Up to 2 attachments
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 http://stackoverflow.com/questions/1814532/1071-specified-key-was-too-long-max-key-length-is-767-bytes company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 http://softmemos.blogspot.com/2009/01/phplist-installation-and.html 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 767 bytes up vote 192 down vote favorite 43 max key When I executed the following command: ALTER TABLE `mytable` ADD UNIQUE ( `column1` , `column2` ); 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 max key length 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 198k36356415 asked Nov 29 '09 at 3:18 Steven 5,2543480112 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 198k36356415 T
2009 PHPlist 2.10.7 installation, configuration and troubleshooting I installed PHPList 2.10.7 using Fantastico Deluxe with my UTF-8 MySQL. On the first install it gave me errors telling that phplist_users_users table does not exist.'phplist_user_blacklist_data' database errorI went to 'main page'->'setup'->'Database Initialization' and re-built the DB. This time it was almost OK. Another error remains, saying'phplist_user_blacklist_data'Database error 1071 while doing query Specified key was too long; max key length is 1000 bytesstill appeared.Looking how to fix that I found this thread:http://forums.phplist.com/viewtopic.php?t=8150and like it was suggested changed 'email' field size in admin/support.php from 255 to 233, then re-run DB init.This solved the issue.Database error 1146However, the work was far of being done yet.Next issue - a DB error again, now when I tried to import first entries from a CSV file.Database error 1146 while doing query Table 'phplist . user_group' doesn't existTwo ways to solve it are discussed in this thread http://forums.phplist.com/viewtopic.php?t=21789I found that for me it will be easier to add a DB table with phpMyAdmin using this SQL from the thread above:CREATE TABLE `user_group``userid` int(11) NOT NULL auto_increment,`groupid` int(11) NOT NULL,PRIMARY KEY (`userid`)ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1The import errors disappeared.Cannot delete usersI imported nearly 2,000 entries, but I found that phpList is unable to delete these users once imported! I wanted to fix a few things in Excel / CSV and re-import the list again, but could not do it since I could not remove the existing users from the PHPlist UI."delete all unsubscribed users" function just did nothing!PHPList also did not show the list of users, - the search did not work at all, so I could not even remove them one by one.No users show up in searchFor the search users / show users I found that the solutionhttp://forums.phplist.com/viewtopic.php?t=22619works - the users are shown now (i used