[Sqlgrey-users] Key too long?

[Daniel McDonald]

I¹m trying to bring up a new instance of sqlgrey, and it is unhappy creating
the from_awl table:

Apr 6 17:09:49 ma sqlgrey: dbaccess: warning: couldn't do query: CREATE
TABLE from_awl (sender_name varchar(64) NOT NULL, sender_domain varchar(255)
NOT NULL, src varchar(39) NOT NULL, first_seen timestamp NOT NULL, last_seen
timestamp NOT NULL, PRIMARY KEY (src, sender_domain, sender_name)): ,
reconnecting to DB

I tried to run this command interactively and was returned:
mysql> use sqlgrey
Database changed
mysql> CREATE TABLE from_awl (sender_name varchar(64) NOT NULL,
sender_domain varchar(255) NOT NULL, src varchar(39) NOT NULL, first_seen
timestamp NOT NULL, last_seen timestamp NOT NULL, PRIMARY KEY (src,
sender_domain, sender_name));
ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes

That error didn¹t make much sense to me, as 64+255+39 <1000


mysql> quit
Bye

Sqlgrey is version 1.7.6, from a locally rebuilt SRPM on Mandriva Enterprise
Server 5.1

$ rpm -qi sqlgrey
Name : sqlgrey Relocations: (not relocatable)
Version : 1.7.6 Vendor: Austin Energy
Release : 2mdvmes5.1 Build Date: Thu 01 Apr 2010
08:57:20 AM CDT
Install Date: Thu 01 Apr 2010 09:18:18 AM CDT Build Host:
Group : System/Servers Source RPM:
sqlgrey-1.7.6-2mdvmes5.1.src.rpm
Size : 147603 License: GPL
Signature : (none)
URL : http://sqlgrey.sourceforge.net
Summary : Postfix grey-listing policy service
Description :
SQLgrey is a Postfix grey-listing policy service with
auto-white-listing written in Perl with SQL database as storage
backend. Greylisting stops 50 to 90 % junk mails (spam and virus)
before they reach your Postfix server (saves BW, user time and CPU
time).

Mysql is version 5.0.89, using the distro supplied RPM:
$ rpm -qi mysql-max
Name : mysql-max Relocations: (not relocatable)
Version : 5.0.89 Vendor: Mandriva
Release : 0.1mdvmes5 Build Date: Sun 17 Jan 2010
09:43:11 AM CST
Install Date: Thu 01 Apr 2010 07:31:50 AM CDT Build Host:
titan.mandriva.com
Group : System/Servers Source RPM:
mysql-5.0.89-0.1mdvmes5.src.rpm
Size : 8645155 License: GPL
Signature : DSA/SHA1, Sun 17 Jan 2010 11:25:07 AM CST, Key ID
9aa8d0d022458a98
Packager : Mandriva Linux Security Team <***@mandriva.com>
URL : http://www.mysql.com
Summary : MySQL - server with extended functionality


Clues welcome!

[Kyle Lanclos]

Post by Daniel McDonald
ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes
That error didn't make much sense to me, as 64+255+39 <1000

This is probably because Unicode is involved. See also:

http://www.xaprb.com/blog/2006/04/17/max-key-length-in-mysql/

64+255+39 = 358 > 333.

--Kyle