View Issue Details

IDProjectCategoryView StatusLast Update
0010609phplist applicationInstallationpublic23-05-12 04:03
Reporterpdc124 
PrioritynormalSeveritymajorReproducibilityalways
Status resolvedResolutionfixed 
Product Version 
Target Version4.0.xFixed in Version2.11.7 
Summary0010609: error on creating database
DescriptionInitialising table user_blacklist_data
Database error 1071 while doing query Specified key was too long; max key length is 1000 bytes
... failed
Initialising table list... ok
Initialising table listrss... ok
Initialising table listuser... ok
Additional Informationjust down loaded, installing and configuring . When i try just the SQL line it fails on I get this error
SQL query:

CREATE TABLE `phplist_user_blacklist_data` (
INSERT INTO `phplist_user_blacklist_data`
VALUES (
'billgates@microsoft.com', 'reason', 'Im not really that interested in your newsletter anymore. Sorry.'
);

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO `phplist_user_blacklist_data` VALUES ('billgates@microsoft.com','rea' at line 2


mysql> SHOW COLUMNS FROM phplist_user_blacklist;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| email | varchar(255) | NO | PRI | | |
| added | datetime | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql>

I think something is missing from the table structure
TagsNo tags attached.

Relationships

related to 0008583 resolvedmichiel Unable to install with UTF-8 database using MySql 4.1 

Activities

douman

08-01-08 02:59

reporter   ~0038970

I get the same error when trying to initialize. I have done several startups and in each one I have to create the user_blacklist_data table by hand. (Not a too big a deal)

It looks like there has been no developer response to this July 2007 note, does no one else have this problem?

me: godaddy linux hosting MySQL5 PHP5

h2b2

18-02-08 21:12

manager   ~0040990

Related to http://mantis.phplist.com/view.php?id=8583

postmanpat

02-03-08 01:07

reporter   ~0041951

Loads of people are getting that error, me included. At the bottom, it says upgrade and all that, as though it can't recognise an old version of PHP or MySQL. Mine are both up to date, so I think it's phpList that's out of date.

Can we bump this one up and as the mods to supply something sensible in reply?

My system is debian etch and I'm loading phpList onto an existing MySQL database running Joomla! using prefix pli.

Are you finding that making the table user_blacklist_data works for you? Empty, or are you having to populate it?

serval2412

31-03-08 21:39

reporter   ~0043882

I've installed phplist on a server with php5 and Mysql 5 with MyIsam in UTF-8-general-ci, no pb.

The pb, according to the post above, was due to the length of the varchar fields. (email + name)
(255 + 100) * 3 (in UTF-8) > 1000, pb with MyIsam.

Now, its : (255 + 60) * 3 < 1000, it should be no pb.

You can reduce the length of the second field (name) without risk to 15 instead of 60, (*) here's why :

I've made greps on the code, the only function where there's an insert into user_blacklist_data is addUserToBlackList in commonlib/lib/userlib.php

In the field "name", there can be only 2 values :
- "reason"
- REMOTE_ADDR

So max length is 11, you can put 15 to let a margin.


* : in admin/structure.php, you change the line 69 :
"name" => array("varchar(60) not null","Name of Dataitem"),

on that line :
"name" => array("varchar(15) not null","Name of Dataitem")

michiel

23-05-12 04:02

manager   ~0051593

I bet Mysql can handle larger keys in the meantime, but I've changed the structure.php file anyway, to reduce the size. Haven't come across this error myself, and have been using Mysql 5 for several years now.