View Issue Details
|ID||Project||Category||View Status||Date Submitted||Last Update|
|0008583||phplist application||All Other||public||21-11-06 13:31||08-08-11 21:30|
|Target Version||2.10.X||Fixed in Version||2.10.15|
|Summary||0008583: Unable to install with UTF-8 database using MySql 4.1|
|Description||Mysql limit the index lenght to 1000 bytes.|
The index created for email and name exceed 1000 bytes.
|Steps To Reproduce||MySql 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
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
|Additional Information||Quick 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)
|Tags||No tags attached.|
||it might be useful to post a link in the forums to this issue, so people can find it.|
|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?|
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 ********
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?
For those that do not want or need UFT-8, Samtherobot suggests setting the database to latin1, using this sql:
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:
ALTER DATABASE databasename DEFAULT CHARACTER SET latin1
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:
ALTER DATABASE databasename DEFAULT CHARACTER SET utf8
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.
||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.|
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.
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?
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)",""),
"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.
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 (http://www.yourdomain.com/lists/admin/). 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.
||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.|
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.
reduced the column sizes a bit. Didn't test it, but it's most likely to solve the issue.