user_blacklist_data: Database error 1071 [fixed]

Questions & Problems about Installing or Upgrading Version 2 phpList
Forum rules
Please help the volunteers to help you by supplying the version of phpList you are using, browser & version and if possible, a link to your phpList installation. This is for Version 2 of phpList (the orange one).

user_blacklist_data: Database error 1071 [fixed]

Postby shnoulle » 2:30pm, Mon 29 Jan, 2007

Hello,

I resolve the problem, but i on't understand why i had this.

I made first a fresh install of phplist without modification on config.php. (just database neme/user and pass)

This installation was OK, but my DB was in latin1_swedish .


I remove this install, because i need 2 list , one in french and 1 in brazilian , make my DB utf8 by default.

I want to use the same DB for the 2 install:
I first put an -fr at the prefic , but i had this installation problem (only with user_blacklist_data)
Code: Select all
Database error 1071 while doing query Specified key was too long; max key length is 1000 bytes


I remove some code from prefix and finally try with: fr_ and fr_user_. But have the same problem.

I create table manually
Code: Select all
CREATE TABLE `fr_user_blacklist_data` (
  `email` varchar(255) NOT NULL default '',
  `name` varchar(100) default NULL,
  `data` text,
  UNIQUE KEY `email` (`email`)
) TYPE=MyISAM;
And all seem OK.

What is the difference ????
Did someone had the same problem ?
shnoulle
PL Nut
 
Posts: 21
Joined: 10:37am, Mon 29 Jan, 2007
Location: Roubaix, France

Postby H2B2 » 5:31pm, Wed 31 Jan, 2007

Take a look at this report: http://mantis.phplist.com/view.php?id=8583
H2B2
Moderator
 
Posts: 7188
Joined: 1:51am, Wed 15 Mar, 2006

Postby hardwired » 10:03pm, Wed 16 May, 2007

The reason for the error is because phplist is creating the table "user_blacklist_data" with a combined index key of email(255) and name(100).

That is a total of 355 characters, however if you are running a database in utf, each characters count as 3bytes, thus 355x3 bytes = 1065 bytes which is over mySQL's 1000bytes key limit, thus the error :
Database error 1071 while doing query Specified key was too long; max key length is 1000 bytes

Here is some pointers on key calculations :
http://www.xaprb.com/blog/2006/04/17/ma ... -in-mysql/

Either skip the key "emailnameidx" when re-creating the table or shorten the length on one of the field e.g. email, like this :

CREATE TABLE phplist_user_blacklist_data (email varchar(233) not null unique,name varchar(100) not null,data text,index emailidx (email),index emailnameidx (email,name))

However I do not know how would this affect phplist.
Give it a try...
hardwired
phpLister
 
Posts: 10
Joined: 3:34am, Mon 05 Mar, 2007

Postby Guest » 9:23pm, Tue 23 Dec, 2008

To make this easier for newbies:

After downloading, (before uploading!) search for the following line in lists/admin/structure.php

Code: Select all
"user_blacklist_data" => array(


This is line 67 in version 2.10.8.

Find the entry for the email address which should be directly below the line above (line 68 in recent version):

Code: Select all
"email" => array("varchar(255) not null unique","Email"),


and change it to this:

Code: Select all
"email" => array("varchar(233) not null unique","Email"),


You will notice the shortened email field, therefore avoiding the error on initialization.

I haven't tested the full outcome of this but it seems to be fine to far.

All you do now is save this structure.php file and upload with the /lists/ folder as per initial install procedures.

When you initialize the DB the error shouldn't show.

Hope that helps.
Guest
 

Postby john12 » 2:18am, Fri 20 Mar, 2009

Anonymous wrote:To make this easier for newbies:

After downloading, (before uploading!) search for the following line in lists/admin/structure.php

Code: Select all
"user_blacklist_data" => array(


This is line 67 in version 2.10.8.

Find the entry for the email address which should be directly below the line above (line 68 in recent version):

Code: Select all
"email" => array("varchar(255) not null unique","Email"),


and change it to this:

Code: Select all
"email" => array("varchar(233) not null unique","Email"),


You will notice the shortened email field, therefore avoiding the error on initialization.

I haven't tested the full outcome of this but it seems to be fine to far.

All you do now is save this structure.php file and upload with the /lists/ folder as per initial install procedures.

When you initialize the DB the error shouldn't show.

Hope that helps.



This method works perfectly!!!
thank YOU
john12
phpLister
 
Posts: 7
Joined: 2:57am, Sun 01 Mar, 2009

Re: user_blacklist_data: Database error 1071 [fixed]

Postby defro » 5:08pm, Thu 30 Jul, 2009

Thanks you. It's works well !
defro
phpList newbie
 
Posts: 1
Joined: 4:46pm, Thu 30 Jul, 2009

Re: user_blacklist_data: Database error 1071 [fixed]

Postby deadstar » 10:12pm, Wed 27 Apr, 2011

thanks so much for this fix. changing the field from 255 to 233 characters worked perfectly for me.
deadstar
phpList newbie
 
Posts: 1
Joined: 10:08pm, Wed 27 Apr, 2011

Re: user_blacklist_data: Database error 1071 [fixed]

Postby LedNick » 9:27am, Wed 17 Aug, 2011

Here is some pointers on key calculations :
http://www.xaprb.com/blog/2006/04/17/ma ... -in-mysql/

good link. i'll need that for future calculations
LedNick
phpList newbie
 
Posts: 4
Joined: 10:12am, Thu 11 Aug, 2011


Return to Installing & Upgrading

Who is online

Users browsing this forum: No registered users and 0 guests