130802 2006-04-21 22:57 0000 gld-1.6 with MySQL 5: ERROR 1071 (42000) at line 1: Specified key was too long; max key length is 1000 bytes 2006-05-03 10:17:50 0000 1 1 1 Unclassified Gentoo Linux Ebuilds 2006.0 All Linux RESOLVED FIXED http://bugs.mysql.com/bug.php?id=4541 P2 normal --- 1 askwar@digitalprojects.com slarti@gentoo.org jakub@gentoo.org net-mail@gentoo.org askwar@digitalprojects.com 2006-04-21 22:57:17 0000 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 jakub@gentoo.org 2006-04-21 23:29:35 0000 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. askwar@digitalprojects.com 2006-04-22 01:04:10 0000 Created an attachment (id=85165) Replacement for /usr/share/gld/sql/tables.mysql The attached SQL file works fine with MySQL 5. slarti@gentoo.org 2006-05-03 10:17:50 0000 I committed this with the latest version bump. Thanks for the report. 85165 2006-04-22 01:04 0000 Replacement for /usr/share/gld/sql/tables.mysql gld.mysql.sql text/plain Q1JFQVRFIFRBQkxFIGBncmV5bGlzdGAgKAoJYGlwYCBDSEFSKCAxNiApIENIQVJBQ1RFUiBTRVQg YXNjaWkgQ09MTEFURSBhc2NpaV9nZW5lcmFsX2NpIE5PVCBOVUxMIENPTU1FTlQgJ0lQIG9mIFNl bmRpbmcgSG9zdCcsCglgc2VuZGVyYCBDSEFSKCAyNDIgKSBDSEFSQUNURVIgU0VUIGxhdGluMSBD T0xMQVRFIGxhdGluMV9nZW5lcmFsX2NpIE5PVCBOVUxMIENPTU1FTlQgJ0FkZHJlc3Mgb2YgU2Vu ZGVyJywKCWByZWNpcGllbnRgIENIQVIoIDI0MiApIENIQVJBQ1RFUiBTRVQgbGF0aW4xIENPTExB VEUgbGF0aW4xX2dlbmVyYWxfY2kgTk9UIE5VTEwgQ09NTUVOVCAnQWRkcmVzcyBvZiBSZWNpcGll bnQnLAoJYGZpcnN0YCBJTlQoIDExICkgTk9UIE5VTEwgREVGQVVMVCAnMCcgQ09NTUVOVCAnRGF0 ZSwgd2hlbiB0aGlzIFNlbmRlciBoYXMgZmlyc3QgYmVlbiBzZWVuJywKCWBsYXN0YCBJTlQoIDEx ICkgTk9UIE5VTEwgREVGQVVMVCAnMCcgQ09NTUVOVCAnRGF0ZSwgd2hlbiB0aGlzIHNlbmRlciBo YXMgbGFzdCBiZWVuIHNlZW4nLAoJYG5gIElOVCggMTEgKSBOT1QgTlVMTCBERUZBVUxUICcwJyBD T01NRU5UICdTZXF1ZW5jZSBudW1iZXInLAoJIFBSSU1BUlkgS0VZICggYGlwYCAsIGBzZW5kZXJg ICwgYHJlY2lwaWVudGAgICkKKSBFTkdJTkUgPSBNWUlTQU0gQ0hBUkFDVEVSIFNFVCBsYXRpbjEg Q09MTEFURSBsYXRpbjFfZ2VuZXJhbF9jaSBDT01NRU5UID0gJ0dMRCBHcmV5bGlzdCBUYWJsZSc7 CgpDUkVBVEUgVEFCTEUgYHdoaXRlbGlzdGAgKAoJYG1haWxgIENIQVIoIDI0MiApIENIQVJBQ1RF UiBTRVQgbGF0aW4xIENPTExBVEUgbGF0aW4xX2dlbmVyYWxfY2kgTk9UIE5VTEwgQ09NTUVOVCAn QWRyZXNzIHdoaWNoIGlzIHdoaXRlbGlzdGVkJywKCWBjb21tZW50YCBDSEFSKCAyNDIgKSBDSEFS QUNURVIgU0VUIHV0ZjggQ09MTEFURSB1dGY4X2dlbmVyYWxfY2kgTk9UIE5VTEwgQ09NTUVOVCAn QSBjb21tZW50LCBhYm91dCB3aHkgdGhpcyBhZHJlc3MgaXMgd2hpdGVsaXN0ZWQnLAoJUFJJTUFS WSBLRVkgKCBgbWFpbGAgKQopIEVOR0lORSA9IE1ZSVNBTSBDSEFSQUNURVIgU0VUIGxhdGluMSBD T0xMQVRFIGxhdGluMV9nZW5lcmFsX2NpIENPTU1FTlQgPSAnR0xEIFdoaXRlbGlzdCBUYWJsZSc7 Cg==