Bug 130802 – gld-1.6 with MySQL 5: ERROR 1071 (42000) at line 1: Specified key was too long; max key length is 1000 bytes

• Product: Gentoo Linux
• Status: RESOLVED FIXED

[Description Alexander Skwar 2006-04-21 22:57:17 UTC]

I tried to create the mysql tables needed for gld by using the /usr/share/gld/sql/tables.mysql script:

CREATE TABLE greylist (
  ip char(16) NOT NULL default '',
  sender char(242) NOT NULL default '',
  recipient char(242) NOT NULL default '',
  first int(11) NOT NULL default '0',
  last int(11) NOT NULL default '0',
  n int(11) NOT NULL default '0',
  PRIMARY KEY  (ip,sender,recipient)
) TYPE=MyISAM COMMENT='greylist';


CREATE TABLE whitelist (
  mail char(242) NOT NULL default '',
  comment char(242) NOT NULL default '',
  PRIMARY KEY  (mail)
) TYPE=MyISAM;

This fails:

askwar@hetzner /etc/mail/spamassassin $ mysql  mail < /usr/share/gld/sql/tables.mysql
ERROR 1071 (42000) at line 1: Specified key was too long; max key length is 1000 bytes


I'm using MySQL 5.0.19.


askwar@hetzner /etc/mail/spamassassin $ emerge --info
Portage 2.1_pre7-r5 (hardened/x86/2.6, gcc-3.4.6, glibc-2.3.6-r3, 2.6.16-hardened-r3.01 i686)
=================================================================
System uname: 2.6.16-hardened-r3.01 i686 AMD Athlon(tm) XP 2000+
Gentoo Base System version 1.12.0_pre17
ccache version 2.4 [enabled]
dev-lang/python:     2.3.5-r2, 2.4.2-r1
sys-apps/sandbox:    1.2.17
sys-devel/autoconf:  2.13, 2.59-r7
sys-devel/automake:  1.4_p6, 1.5, 1.6.3, 1.7.9-r1, 1.8.5-r3, 1.9.6-r2
sys-devel/binutils:  2.15.92.0.2-r10, 2.16.1-r2
sys-devel/libtool:   1.5.22
virtual/os-headers:  2.6.11-r3
ACCEPT_KEYWORDS="x86 ~x86"
AUTOCLEAN="yes"
CBUILD="i686-pc-linux-gnu"
CFLAGS="-march=athlon-xp -O2 -pipe -fomit-frame-pointer"
CHOST="i686-pc-linux-gnu"
CONFIG_PROTECT="/etc /etc/mail/dspam /usr/NX/etc /usr/NX/home /usr/kde/2/share/config /usr/kde/3/share/config /usr/share/X11/xkb /usr/share/config /var/qmail/control /var/run/dspam"
CONFIG_PROTECT_MASK="/etc/eselect/compiler /etc/gconf /etc/revdep-rebuild /etc/terminfo /etc/texmf/web2c /etc/env.d"
CXXFLAGS="-march=athlon-xp -O2 -pipe -fomit-frame-pointer"
DISTDIR="/Gentoo/Portage/distfiles"
EMERGE_DEFAULT_OPTS="--alphabetical"
FEATURES="autoconfig buildpkg ccache distlocks metadata-transfer sandbox sfperms strict"
GENTOO_MIRRORS="        http://ftp-stud.fht-esslingen.de/pub/Mirrors/gentoo/    http://linux.rz.ruhr-uni-bochum.de/download/gentoo-mirror/      ftp://gentoo.itdnet.net/gentoo/         http://ftp.gentoo.or.kr/        http://distfiles.gentoo.org/ "
LDFLAGS="-Wl,-O1"
LINGUAS="de"
MAKEOPTS="-j2"
PKGDIR="/Gentoo/Portage/packages"
PORTAGE_TMPDIR="/Gentoo/Portage/build"
PORTDIR="/Gentoo/Portage/tree"
PORTDIR_OVERLAY="/Gentoo/Portage/local-tree/misc /Gentoo/Portage/local-tree/overlays/nx /Gentoo/Portage/local-tree/overlays/gentoo-de"
SYNC="rsync://rsync.de.gentoo.org/gentoo-portage"
USE="3dnow 3dnowext 7zip acl apache2 async bash-completion berkdb bzip2 cap caps ccache checkpath chroot cracklib crypt cyrus dcc discard-path dlloader ecc erandom exif extensions firefox glep glibc-omitfp hardened hardenedphp hpn iconv idea idled idn imagemagick imap imlib imlib2 jikes jpeg kdeenablefinal linuxthreads-tls logrotate lynxkeymap maildir mime mmap mmx mmxext mode-owner moznoirc mozsvg multislot nls no-old-linux noaudio nocd nodrm nolvm1 nopop3d offensive pam pam-mysql pcre pdf php pic png posix postfix prelude pyzor razor readline recode reiserfs sasl sendfile server sftplogging sguil sharedmem sse ssl static svg sysvipc szip tcpd threads tiff tokenizer tools unicode userlocales utf8 vhosts vim-pager x86 xfs xinetd zlib elibc_glibc input_devices_void kernel_linux linguas_de userland_GNU video_cards_dummy video_cards_none"
Unset:  ASFLAGS, CTARGET, INSTALL_MASK, LANG, LC_ALL

[Comment 1 Jakub Moc (RETIRED) 2006-04-21 23:29:35 UTC]

http://bugs.mysql.com/bug.php?id=4541

You can setup the db with latin-1 character set instead of utf-8 as a workaround. No idea when MySQL will remove this stupid limitation.

[Comment 2 Alexander Skwar 2006-04-22 01:04:10 UTC]

Created attachment 85165 [details]
Replacement for /usr/share/gld/sql/tables.mysql

The attached SQL file works fine with MySQL 5.

[Comment 3 Tom Martin (RETIRED) 2006-05-03 10:17:50 UTC]

I committed this with the latest version bump.

Thanks for the report.