Thanks to everyone who tried to help me with this problem, especially Tony Earnshaw.
The solution is to start MySQL server with --sql-mode="" (empty string) like this: $bindir/mysqld_safe --defaults-file=$defaults_file --sql-mode="" & MySQL by default uses these SQL modes: REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, ANSI Reference: http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html you can check in it your DB by executing: SELECT @@global.sql_mode; or SELECT @@session.sql_mode; ANSI and ANSI_QUOTES modes enforce more strict SQL syntax and prevent strings in double quotes from being entered into DB. "...With ANSI_QUOTES enabled, you cannot use double quotes to quote literal strings, because it is interpreted as an identifier." I "relaxed" MySQL modes and Dspam is working now without a problem. However, I think that it's a Dspam bug which double quotes signature string when it queries dspam_signature_data table. Thanks to everyone who helped me to troubleshoot this problem, Alex On Wednesday 01 August 2007 09:43, Dspam User wrote: > Tony, > Thank you again for your help. > I'm trying to answer your questions: > > On Tuesday 31 July 2007 22:50, Tony Earnshaw wrote: > <snipped> > > > Well, it would seem to be complaining about the dspam_virtual_uids table. > > > > What do you get with 'mysql -> SELECT * FROM `dspam_virtual_uids` WHERE > > 1;'? Do you see two columns, one called uid and the other username? > > Yes, I have these uid and username columns. > Here is the result of this query: > > +-----+-------------+ > > | uid | username | > > +-----+-------------+ > > | 2 | arch2 | > | 1 | dspam-users | > | 3 | test1 | > | 4 | test2 | > | 5 | test3 | > > +-----+-------------+ > > <snipped> > > > Did you build with '--enable-virtual-users'? > > Yes. > Here is my exact configuration for compiling Dspam: > > ./configure \ > --prefix=/usr/local/dspam \ > --sysconfdir=/etc/mail \ > --enable-long-usernames \ > --enable-clamav \ > --enable-syslog \ > --enable-virtual-users \ > --enable-daemon \ > --enable-debug \ > --with-delivery-agent=/usr/sbin/sendmail \ > --with-storage-driver=mysql_drv \ > --with-mysql-libraries=/usr/local/mysql/lib \ > --with-mysql-includes=/usr/local/mysql/include \ > --disable-mysql4-initialization \ > --with-dspam-home=/data/dspam \ > --with-dspam-home-owner=mailfilter \ > --with-dspam-home-group=mailfilter \ > --with-dspam-owner=mailfilter \ > --with-dspam-group=mailfilter \ > --with-logfile=/var/log/dspam.log \ > --with-logdir=/data/dspam > > > > Debugging is enabled. > > > > You might have built with '--enable-debug', but you haven't turned it on > > in dspam.conf: > > > > > > Debug * > > #Debug bob bill > > > > You need the debug log, not this one - it's $DSPAMHOME/log/dspam.debug. > > If you don't have this, turn debugging on in dspam.conf. > > > >... and 'DebugOpt process spam fp innocent' > > I turned Debug on like you recommended: > Debug * > DebugOpt process spam fp innocent > > > These fields are (supposed to be) in dspam_signature_data. What do you > > get with "mysql -> SELECT UID FROM dspam_signature_data WHERE uid = > > '3';"? > > This query produces no results because of dspam_signature_data table is > empty: Empty set (0.01 sec) > > <snipped> > > > You need the debug log, not this one - it's $DSPAMHOME/log/dspam.debug. > > If you don't have this, turn debugging on in dspam.conf. > > Here is the content of dspam.debug after I sent one test message from > outside: > > 24314: [08/01/2007 09:06:22] DSPAM Instance Startup > 24314: [08/01/2007 09:06:22] input args: /usr/local/dspam/bin/dspam > --deliver=innocent --user test1 -i -f [EMAIL PROTECTED] -- > [EMAIL PROTECTED] 24314: [08/01/2007 09:06:22] pass-thru args: > /usr/sbin/sendmail -i -f [EMAIL PROTECTED] -- [EMAIL PROTECTED] > 24314: [08/01/2007 09:06:22] processing user test1 > 24314: [08/01/2007 09:06:22] uid = 80, euid = 80, gid = 80, egid = 80 > 24314: [08/01/2007 09:06:22] loading preferences for user test1 > 24314: [08/01/2007 09:06:22] default preferences empty. reverting to > dspam.conf preferences. > 24314: [08/01/2007 09:06:22] Loading preferences from dspam.conf > 24314: [08/01/2007 09:06:22] using /data/dspam/opt-in/test1.dspam as path > 24314: [08/01/2007 09:06:22] using /data/dspam/opt-out/test1.nodspam as > path 24314: [08/01/2007 09:06:22] sedation level set to: 0 > 24314: [08/01/2007 09:06:22] Whitelist threshold: 10 > 24314: [08/01/2007 09:06:22] [graham] [0.400000] > Received*gw02.biotronik.org+(ext (1frq, 0s, 1i) > 24314: [08/01/2007 09:06:22] [burton] [0.400000] > Received*gw02.biotronik.org+(ext (1frq, 0s, 1i) > 24314: [08/01/2007 09:06:22] [graham] [0.400000] Received*568612803F371 > (1frq, 0s, 0i) > 24314: [08/01/2007 09:06:22] [burton] [0.400000] Received*568612803F371 > (1frq, 0s, 0i) > 24314: [08/01/2007 09:06:22] [graham] [0.400000] X-MIMETrack*Serialize+by > (1frq, 0s, 1i) > 24314: [08/01/2007 09:06:22] [burton] [0.400000] X-MIMETrack*Serialize+by > (1frq, 0s, 1i) > 24314: [08/01/2007 09:06:22] [graham] [0.400000] Date*0700 (1frq, 0s, 1i) > 24314: [08/01/2007 09:06:22] [burton] [0.400000] Date*0700 (1frq, 0s, 1i) > 24314: [08/01/2007 09:06:22] [graham] [0.400000] X-Mailer*Lotus (1frq, 0s, > 1i) 24314: [08/01/2007 09:06:22] [burton] [0.400000] X-Mailer*Lotus (1frq, > 0s, 1i) 24314: [08/01/2007 09:06:22] [graham] [0.400000] Received*ESMTP > (1frq, 0s, 1i) 24314: [08/01/2007 09:06:22] [burton] [0.400000] > Received*ESMTP (1frq, 0s, 1i) 24314: [08/01/2007 09:06:22] [graham] > [0.400000] Content-Disposition*inline (2frq, 0s, 1i) > 24314: [08/01/2007 09:06:22] [burton] [0.400000] Content-Disposition*inline > (2frq, 0s, 1i) > 24314: [08/01/2007 09:06:22] [burton] [0.400000] Content-Disposition*inline > (2frq, 0s, 1i) > 24314: [08/01/2007 09:06:22] [graham] [0.400000] > Message-ID*ON8825732A.005863EF (1frq, 0s, 0i) > 24314: [08/01/2007 09:06:22] [burton] [0.400000] > Message-ID*ON8825732A.005863EF (1frq, 0s, 0i) > 24314: [08/01/2007 09:06:22] [graham] [0.400000] X-MIMETrack*06 (1frq, 0s, > 0i) 24314: [08/01/2007 09:06:22] [burton] [0.400000] X-MIMETrack*06 (1frq, > 0s, 0i) 24314: [08/01/2007 09:06:22] [graham] [0.400000] Received*(ext+de > (1frq, 0s, 1i) > 24314: [08/01/2007 09:06:22] [burton] [0.400000] Received*(ext+de (1frq, > 0s, 1i) > 24314: [08/01/2007 09:06:22] [graham] [0.400000] Received*mailswamp.com> > (1frq, 0s, 1i) > 24314: [08/01/2007 09:06:22] [burton] [0.400000] Received*mailswamp.com> > (1frq, 0s, 1i) > 24314: [08/01/2007 09:06:22] [graham] [0.400000] Content-type*ASCII (2frq, > 0s, 1i) > 24314: [08/01/2007 09:06:22] [burton] [0.400000] Content-type*ASCII (2frq, > 0s, 1i) > 24314: [08/01/2007 09:06:22] [burton] [0.400000] Content-type*ASCII (2frq, > 0s, 1i) > 24314: [08/01/2007 09:06:22] [graham] [0.400000] X-Mailer*Lotus+Notes > (1frq, 0s, 1i) > 24314: [08/01/2007 09:06:22] [burton] [0.400000] X-Mailer*Lotus+Notes > (1frq, 0s, 1i) > 24314: [08/01/2007 09:06:22] [graham] [0.400000] X-MIMETrack*EXT(Release > (1frq, 0s, 1i) > 24314: [08/01/2007 09:06:22] [burton] [0.400000] X-MIMETrack*EXT(Release > (1frq, 0s, 1i) > 24314: [08/01/2007 09:06:22] [graham] [0.400000] Test+Message (2frq, 0s, > 0i) 24314: [08/01/2007 09:06:22] [burton] [0.400000] Test+Message (2frq, > 0s, 0i) 24314: [08/01/2007 09:06:22] [burton] [0.400000] Test+Message > (2frq, 0s, 0i) 24314: [08/01/2007 09:06:22] [burton] [0.400000] > X-Mailer*March+27 (1frq, 0s, 1i) > 24314: [08/01/2007 09:06:22] [burton] [0.400000] Received*for+<test1 (1frq, > 0s, 0i) > 24314: [08/01/2007 09:06:22] [burton] [0.400000] X-Mailer*2005 (1frq, 0s, > 1i) 24314: [08/01/2007 09:06:22] [burton] [0.400000] Received*(ext (1frq, > 0s, 1i) 24314: [08/01/2007 09:06:22] [burton] [0.400000] X-MIMETrack*EXT+DE > (1frq, 0s, 1i) > 24314: [08/01/2007 09:06:22] [burton] [0.400000] X-MIMETrack*14 (1frq, 0s, > 1i) 24314: [08/01/2007 09:06:22] [burton] [0.400000] X-MIMETrack*2007) > (1frq, 0s, 1i) > 24314: [08/01/2007 09:06:22] [burton] [0.400000] Received*Aug (1frq, 0s, > 0i) 24314: [08/01/2007 09:06:22] [burton] [0.400000] Message (2frq, 0s, 0i) > 24314: [08/01/2007 09:06:22] Graham-Bayesian Probability: 0.002278 Samples: > 15 24314: [08/01/2007 09:06:22] Burton-Bayesian Probability: 0.000018 > Samples: 27 24314: [08/01/2007 09:06:22] no factors specified; using > default > 24314: [08/01/2007 09:06:22] Result Confidence: 1.00 > 24314: [08/01/2007 09:06:22] Control: [10 10] [10 11] Delta: [0 1] > 24314: [08/01/2007 09:06:22] total processing time: 0.03001s > 24314: [08/01/2007 09:06:22] saving signature as 3,46b0af7e243141222944467 > 24314: [08/01/2007 09:06:22] libdspam returned probability of 0.002278 > 24314: [08/01/2007 09:06:22] message result: NOT SPAM > 24314: [08/01/2007 09:06:22] delivering message > 24314: [08/01/2007 09:06:22] Opening pipe to LDA: /usr/sbin/sendmail -i -f > [EMAIL PROTECTED] -- [EMAIL PROTECTED] > 24314: [08/01/2007 09:06:22] LDA returned success > 24314: [08/01/2007 09:06:22] DSPAM Instance Shutdown. Exit Code: 0 > > It is working, except that nothing is being entered into > dspam_signature_data table and sql.errors shows this: > > [08/01/2007 09:06:22] 24314: Unknown column '3,46b0af7e243141222944467' in > 'where clause': select signature from dspam_signature_data where uid = 3 > and signature = "3,46b0af7e243141222944467" > > I found this strange effect when I run this query manually and signature is > double quoted "3,46b0af7e243141222944467": > > select signature from dspam_signature_data where uid = 3 and signature = > "3,46b0af7e243141222944467" > > It produces the same error of Unknown column. > > However, when I run this query with single quoted signature > '3,46b0af7e243141222944467' > > select signature from dspam_signature_data where uid = 3 and signature = > '3,46b0af7e243141222944467' > > It produces no errors and returns Empty set (0.00 sec) as it should. > > Apparently, MySQL has a problem with double quotes in this query. > Is there any way to switch double quote to single quote in Dspam queries? > > > The ball's now in your court; at least you've provided most of the info > > that helps in analyzing your problem. If I can't help, someone else > > surely can ;) > > > > Best, > > > > --Tonni > > Tony, thank you again for your willingness to help. > > Alex
