When I try to start sqlgrey (tested both versions on a stable (x86) and on a unstable (~x86) machine) I get the following error message: 2006/10/22-00:14:12 sqlgrey (type Net::Server::Multiplex) starting! pid(19011) Binding to TCP port 2501 on host localhost Setting gid to "1014 1014" Setting uid to "103" Use of uninitialized value in concatenation (.) or string at /usr/sbin/sqlgrey line 227. 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 sh: mail: command not found mail: child exited with value: 127 create_from_awl_table error at /usr/sbin/sqlgrey line 176. Even if I comment out the call to mail it refuses to start. (Only the "sh: mail ..." and "mail: child..." lines are missing.)
confirmed, it's missing a DEPEND="virtual/mailx". this package need a program called "mail" in his path, obtain it try: emerge -av1 virtual/mailx it should install mail-client/mailx that provide "/bin/mail" program I'll fix this in the tree tomorrow, jakub bash me if I forgot this ;-)
Still doesn't work. The "unitialized value" stays, it doesn't provide any error message, can't do the query and mail doesn't accept the parameters. 2006/10/22-11:04:37 sqlgrey (type Net::Server::Multiplex) starting! pid(14659) Binding to TCP port 2501 on host localhost Setting gid to "1014 1014" Setting uid to "103" Use of uninitialized value in concatenation (.) or string at /usr/sbin/sqlgrey line 227. 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 mail: You must specify direct recipients with -s, -c, or -b mail: child exited with value: 1 create_from_awl_table error at /usr/sbin/sqlgrey line 176.
PS: It also tries to send a mail even though I didn't set an admin_mail.
It's getting funny. I tried to execute that SQL query manually in PHPMyAdmin and got following response: #1071 - Specified key was too long; max key length is 1000 bytes If I remove "src VARCHAR(39)" from the query it works. (Now I know at least why the query failed. Even though it is misterious to me why 39 is greater than 1000...)
Sorry for the spamming... Seems like MySQL counts all 3 keys together, which can't be greater in size than 1000 bytes. Still leaves the misterium why 64+255+39 = 358 > 1000... Is this really a MySQL bug?
use this 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 DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `last_seen` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (`src`,`sender_domain`(100),`sender_name`) ); Explanation: (a) In MySQL-5.0 each character in a __utf8__ index is 3 byte wide, (src=39, sender_domain=255, sender_name=64) * 3 = 1074 Bytes shortening sender_domain index part to 100 would suffice, make the index smaller and faster, BUT you loose a UNIQUE costraint on part of the table (less than an issue in the real world imho) (b) domain names can be up to 64 chars (less in most countryes) the total length of a domain can be up to 255 accordingly to RFC 2181. most of the time it will be a mail.mydomain.info ~= 64+ 4+1 +4+1 = 75 chars with an already big domain name. UPSTREAM thoughts ?
by the way the DEPEND has been added and it's already in CVS, will be available to emerge --sync soon
(In reply to comment #6) > use this 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 DEFAULT CURRENT_TIMESTAMP ON UPDATE > CURRENT_TIMESTAMP, > `last_seen` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', > PRIMARY KEY (`src`,`sender_domain`(100),`sender_name`) > ); > > Explanation: > > (a) > In MySQL-5.0 each character in a __utf8__ index is 3 byte wide, > (src=39, sender_domain=255, sender_name=64) * 3 = 1074 Bytes > shortening sender_domain index part to 100 would suffice, make the index > smaller and faster, BUT you loose a UNIQUE costraint on part of the table (less > than an issue in the real world imho) Can't we solve the problem by forcing MySQL to use ASCII (or at least a one-byte/char encoding) for the database (should be faster and idn although not widely implemented should work with it anyway)? > > (b) > domain names can be up to 64 chars (less in most countryes) the total length > of a domain can be up to 255 accordingly to RFC 2181. > most of the time it will be a mail.mydomain.info ~= 64+ 4+1 +4+1 = 75 chars > with an already big domain name. > > UPSTREAM thoughts ? I'd like to maintain the UNIQUE constraint if possible. If not, I'll have to find a solution which maintains compatibility with both PostgreSQL and SQLite. I'm not sure SQLite can handle indexes on substrings... If we could solve the problem by setting the encoding to ASCII at database creation (like the PostgreSQL's "createdb -E ASCII" I'm familiar with) this would avoid another range of multi-RDBMs support problems. I just checked MySQL 5.0 docs and it seems to me that adding a MySQL 5 specific paragraph to the doc telling the user to create the SQLgrey's database with: CREATE DATABASE sqlgrey CHARACTER SET latin1; should do it. I'll add it upstream in the doc, but it would be wise to add it to the install-time instructions in the ebuild too.
(In reply to comment #8) > (In reply to comment #6) > > use this 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 DEFAULT CURRENT_TIMESTAMP ON UPDATE > > CURRENT_TIMESTAMP, > > `last_seen` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', > > PRIMARY KEY (`src`,`sender_domain`(100),`sender_name`) > > ); > > > > Explanation: > > > > (a) > > In MySQL-5.0 each character in a __utf8__ index is 3 byte wide, > > (src=39, sender_domain=255, sender_name=64) * 3 = 1074 Bytes > > shortening sender_domain index part to 100 would suffice, make the index > > smaller and faster, BUT you loose a UNIQUE costraint on part of the table (less > > than an issue in the real world imho) > > Can't we solve the problem by forcing MySQL to use ASCII (or at least a > one-byte/char encoding) for the database (should be faster and idn although not > widely implemented should work with it anyway)? $ telnet vivtekk 25 Trying t.x.y.z... Connected to mail.g3nt8.org. Escape character is '^]'. 220 vivtekk.longitekk.org ESMTP Postfix helo λλλ.com 250 vivtekk.longitekk.org mail from:francesco@λλλ.com 501 Bad address syntax mail from:francesco@xn--wxaaa.com 250 Ok rcpt to:francesco@λλλ.com 501 Bad address syntax rcpt to:francesco@xn--wxaaa.com 554 <francesco@xn--wxaaa.com>: Relay access denied quit 221 Bye Connection closed by foreign host. prepare_cached(SELECT now()) statement handle DBI::st=HASH(0xd0544783a50) still Active at /usr/sbin/sqlgrey line 247 grey: new: 85.18.21.124(85.18.21.124), francesco@xn--wxaaa.com -> francesco@xn--wxaaa.com prepare_cached(SELECT now()) statement handle DBI::st=HASH(0xd0544783a50) still Active at /usr/sbin/sqlgrey line 247 grey: early reconnect: 85.18.21.124(85.18.21.124), francesco@xn--wxaaa.com -> francesco@xn--wxaaa.com never end to learn ;) > > > > > (b) > > domain names can be up to 64 chars (less in most countryes) the total length > > of a domain can be up to 255 accordingly to RFC 2181. > > most of the time it will be a mail.mydomain.info ~= 64+ 4+1 +4+1 = 75 chars > > with an already big domain name. > > > > UPSTREAM thoughts ? > > I'd like to maintain the UNIQUE constraint if possible. If not, I'll have to > find a solution which maintains compatibility with both PostgreSQL and SQLite. > I'm not sure SQLite can handle indexes on substrings... > > If we could solve the problem by setting the encoding to ASCII at database > creation (like the PostgreSQL's "createdb -E ASCII" I'm familiar with) this > would avoid another range of multi-RDBMs support problems. > > I just checked MySQL 5.0 docs and it seems to me that adding a MySQL 5 specific > paragraph to the doc telling the user to create the SQLgrey's database with: > CREATE DATABASE sqlgrey CHARACTER SET latin1; > should do it. yes it will > > I'll add it upstream in the doc, but it would be wise to add it to the > install-time instructions in the ebuild too. > yes I will All this tomorrow, after a good night of sleep Thank for your precious help Lionel
emerge --config now create a "latin1" database and an ewarn has been added. Also opened a stable request but that's another bug
