View Issue Details

IDProjectCategoryView StatusLast Update
0008583phpList 3 applicationGeneralpublic08-08-11 20:30
Reporterdanieleint Assigned To 
Status resolvedResolutionfixed 
Platform-OS-OS Version-
Product Version2.10.3 
Target Version2.10.XFixed in Version2.10.15 
Summary0008583: Unable to install with UTF-8 database using MySql 4.1
DescriptionMysql limit the index lenght to 1000 bytes.
The index created 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 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:
Additional InformationQuick and diry fix (don't know the consequences), create the dabase manually and:
a) Use a shorter name.
b) Skip the creation of the index index emailnameidx (email,name)
TagsNo tags attached.


related to 0003721 closed phplist 2.10.x 
related to 0010609 resolvedmichiel error on creating database 



21-11-06 22:58

administrator   ~0021255

it might be useful to post a link in the forums to this issue, so people can find it.


03-08-07 19: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?


18-02-08 21:09

manager   ~0040988

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 fixed (the mysql devs do not consider this to be a bug, but a "limitation") you pretty much have three options:

   1. Change your default database character encoding (which is IMHO a bad idea, especially if you have stuff running already)
   2. Have a look at the structure.php file and create the table by hand but set its engine to "InnoDB" instead of the default "MyISAM"
   3. The third option is pretty much a dirty hack, but is the easiest to do. just edit the "initialise.php" script in the admin directory and look for the following line (should be line 30):
      $query .= "\n)";
      change it to
      $query .= "\n) ENGINE = InnoDB";
      of course, this changes all phplist tables to use the InnoDB engine, but well ... harddrives are cheap

******** END QUOTE ********


02-03-08 01:23

reporter   ~0041952

phpList is **supposed** to work with Joomla, which is UFT8 by default and won't work otherwise.

One of these two apps is out of sync. I'm on MySQL5, site running on J! and phpList just ain't happening.

It's my fave list manager and I'm now in a quandry - do i dump J! and go for Drupal, or is there a viable alternative to phpList? Does phpList work on Drupal6?


06-04-08 13:53

manager   ~0044260

For those that do not want or need UFT-8, Samtherobot suggests setting the database to latin1, using this sql:

======START QUOTE========

The easiest solution is to make sure your database is defaulted to latin1 as the character set for any new tables. To find out what the current default is run this SQL code:
   SHOW CREATE DATABASE databasename

If the encoding shows up as utf8, run the following SQL code on the same table:

Then run the phplist initialize. If you had run it and it failed, make sure you delete the tables before initializing again.

If you want to change the default encoding back to utf8 for future tables, run this code:

======END QUOTE========


08-04-08 01:32

administrator   ~0044316

I think there's a good argument for getting the app to work in utf-8 primarily.

Hernan, can you investigate conversion to UTF-8? It would probably be best to use a multi-lingual one that has non-english words, as that's mostly where the problems start to appear.


30-04-08 23:01

reporter   ~0046087

This has a really simple fix. Just change the email field to 233 instead of 255. (233+100)*3=999 which fits within the key length restriction. 233 characters should be plenty long to hold someone's address. I've never seen one longer than about 75 or 80.


01-06-08 08:18

reporter   ~0048401

You can also reduce the size of the index by only indexing the first X characters of each field.

In this case,

CREATE INDEX emailnameidx ON phplist_user_blacklist_data ( email, name );

CREATE INDEX emailnameidx ON phplist_user_blacklist_data ( email(233), name );

233 indexed characters is even probably overkill for an email address.



14-09-08 08:51

reporter   ~0050234

Database error 1146 while doing query Table 'netradio.phplist_user_blacklist_data' doesn't exist

using Godaddy as a web host and a sql 5.x database. Is there a fix yet?



16-04-09 14:02

reporter   ~0050621

Regarding PHPList: this is my first time attempting to use it. But, based on the above comments, I've gotten it to install without the error by modifying line 72 in /lists/admin/structure.php from this...

"index_2" => array("emailnameidx (email,name)",""), this...

"index_2" => array("emailnameidx (email(233),name)",""),

...and then re-initalising it. Voila, no error.

Not sure if this is kosher, or if it introduces some other problem down the line. We'll see.


28-04-09 16:44

reporter   ~0050628

Also my first attempt at deploying PHPList, and based on the above comments I edited /lists/admin/structure.php but took the approach of reducing the size of the email field of the user_blacklist_data table - to 233 - as follows:

Line 68 was: "email" => array("varchar(255) not null unique","Email"),

Changed it to: "email" => array("varchar(233) not null unique","Email"),

I then dropped all PHPList tables from the DB I'm using and ran the database initialization ( Initialization ran fine, and the DB check confirmed all tables. I even checked the DB through phpMyAdmin and confirmed all tables were created.

Thanks for sharing the knowledge.


07-06-09 17:51

reporter   ~0050675

Hi, faced the same problem here. So after reading paulworks comments, I decided to go for a more consistend way: I changed simply all email entries from 255 to 233 by changing structure.php.... Worked so far for me, and I have no memory overruns! Just my 2 cents.


07-06-09 22:47

reporter   ~0050676

Good thinking mma.

I took your approach and changed all "email" fields in all tables in the structure.php file from 255 to 233 characters, dumped my old tables and reinitialized the DB. I think your approach is a good, clean, thorough fix with a little added insurance... should have thought of it myself.



08-08-11 20:30

administrator   ~0051392

reduced the column sizes a bit. Didn't test it, but it's most likely to solve the issue.