Home

Database error 1071 when installing PHPList

Tue, 24/06/2008 - 11:27 — Finn

Tyring to install PHP list on a server with MySQL 5 resulted in the following error 

Database error 1071 while doing query Specified key was too long; max key length is 1000 bytes

This arises  because PHPList is trying to create the table "user_blacklist_data" with a combined index key of email(255) and name(100).

That is a total of 355 characters. If you are running a database in utf, each characters count as 3 bytes. So 355 x 3 bytes = 1065 bytes which is over mySQL's 1000 bytes key limit.

There are various solutions suggested on the PHPList forumms, the simplist of which for us was the following:

Locate the file lists/admin/structure.php, locate the "user_blacklist_data" array, and change the following line (line 68 in version 2.10.5) from:

"email" => array("varchar(255) not null unique","Email"),

to 

"email" => array("varchar(233) not null unique","Email"),

This  reduces the total to 333 x 3 = 999bytes.

Initialising the database subsequently runs smoothly.

Great!

Thanks to the post from hardwired  on this thread: http://forums.phplist.com/viewtopic.php?t=8150