$sql_select_white_black_list not working on amavis 2.4.2-6.1

hi,

i've got a debian etch server, with amavis 2.4.2-6.1 and
postfix 2.3.8-2.

Reading throught different manual pages on the Internet i've
managed to 
set up all the configuration i wanted except one thing, the
white-black 
lists.
I want them to work on the mysql server, and i've been
already trying 
for 2 days with no luck at all.

I create the following tables:

************

CREATE TABLE `wblist` (
  `rid` int(10) unsigned NOT NULL,
  `sid` int(10) unsigned NOT NULL,
  `wb` varchar(10) NOT NULL,
  PRIMARY KEY  (`rid`,`sid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


CREATE TABLE `mailaddr` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `priority` int(11) NOT NULL default '7',
  `email` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 ;


************

I put some values into each table.

Then, i've tried so many "selects" into de amavis
configuration, none of 
them working.

For example:

$sql_select_white_black_list =
'SELECT wb FROM wblist LEFT JOIN mailaddr ON
wblist.sid=mailaddr.id'.
' WHERE (wblist.rid=?)'
. ' AND (mailaddr.email IN (%k))'.
' ORDER BY mailaddr.priority DESC';

or just...

$sql_select_white_black_list = 'SELECT "B" FROM
wblist';      which to 
my undesrtanding that would mark anything as BLACKMAILED.

Anyway... whatever i put as a select... i get this log
messages....


*********************

Jan 16 11:24:04 postfix01.glusterproves
/usr/sbin/amavisd-new[4223]: 
(04223-01) wbl: checking sender <jordicdmon.com>
Jan 16 11:24:04 postfix01.glusterproves
/usr/sbin/amavisd-new[4223]: 
(04223-01) lookup_sql_field(id) (WARN: no such field in the
SQL table), 
"dummypostfix.busties.glusterproves.cdmon.com"
result=undef
Jan 16 11:24:04 postfix01.glusterproves
/usr/sbin/amavisd-new[4223]: 
(04223-01) lookup => undef, 
"dummypostfix.busties.glusterproves.cdmon.com" does
not match
Jan 16 11:24:04 postfix01.glusterproves
/usr/sbin/amavisd-new[4223]: 
(04223-01) wbl: (SQL) recip 
<dummypostfix.busties.glusterproves.cdmon.com>, 0
matches
Jan 16 11:24:04 postfix01.glusterproves
/usr/sbin/amavisd-new[4223]: 
(04223-01) lookup 
(blacklist_recip<dummypostfix.busties.glusterproves.cdmon.com>) => 
undef, "dummypostfix.busties.glusterproves.cdmon.com" does
not match

********************

Is there anything i am missing? Is there any other option i
have to set 
to enable this feature?

Thank you.

------------------------------------------------------------
-------------
This SF.net email is sponsored by: Microsoft
Defy all challenges. Microsoft(R) Visual Studio 2008.
http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
_______________________________________________
AMaViS-user mailing list
AMaViS-userlists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/amavis-user

AMaViS-FAQ:http://www.amav
is.org/amavis-faq.php3
AMaViS-HowTos:http://www.amavis.org/ho
wto/

Jordi,

> i've got a debian etch server, with amavis 2.4.2-6.1
and postfix 2.3.8-2.
> Reading throught different manual pages on the Internet
i've managed to
> set up all the configuration i wanted except one thing,
the white-black
> lists.
> I want them to work on the mysql server, and i've been
already trying
> for 2 days with no luck at all.
>
> I create the following tables:
> CREATE TABLE `wblist` (...
> CREATE TABLE `mailaddr` (...


> I put some values into each table.
>
> Then, i've tried so many "selects" into de
amavis configuration, none of
> them working.
>
> For example:
>
> $sql_select_white_black_list =
> 'SELECT wb FROM wblist LEFT JOIN mailaddr ON
wblist.sid=mailaddr.id'.
> ' WHERE (wblist.rid=?)'
> . ' AND (mailaddr.email IN (%k))'.
> ' ORDER BY mailaddr.priority DESC';

are you supplying a correct user.id  (to be matched with
wblist.rid)?

> or just...
>
> $sql_select_white_black_list = 'SELECT "B"
FROM wblist';
> which to my undesrtanding that would mark anything as
BLACKMAILED.

...if there are any records in the wblist table.

> Jan 16 11:24:04 postfix01.glusterproves
/usr/sbin/amavisd-new[4223]:
> (04223-01) wbl: checking sender <jordicdmon.com>
> Jan 16 11:24:04 postfix01.glusterproves
/usr/sbin/amavisd-new[4223]:
> (04223-01) lookup_sql_field(id) (WARN: no such field in
the SQL table),

How is your table 'users' set up? It appears it is lacking
the 'id' field.
This table lists your recipients (individually or just their
domain(s)).

  Mark

------------------------------------------------------------
-------------
This SF.net email is sponsored by: Microsoft
Defy all challenges. Microsoft(R) Visual Studio 2008.
http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
_______________________________________________
AMaViS-user mailing list
AMaViS-userlists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/amavis-user

AMaViS-FAQ:http://www.amav
is.org/amavis-faq.php3
AMaViS-HowTos:http://www.amavis.org/ho
wto/

Hi,

i'll check it out again and let you know something.

Thank you.

En/na Mark Martinec ha escrit:
> Jordi,
>
>   
>> i've got a debian etch server, with amavis
2.4.2-6.1 and postfix 2.3.8-2.
>> Reading throught different manual pages on the
Internet i've managed to
>> set up all the configuration i wanted except one
thing, the white-black
>> lists.
>> I want them to work on the mysql server, and i've
been already trying
>> for 2 days with no luck at all.
>>
>> I create the following tables:
>> CREATE TABLE `wblist` (...
>> CREATE TABLE `mailaddr` (...
>>     
>
>
>   
>> I put some values into each table.
>>
>> Then, i've tried so many "selects" into
de amavis configuration, none of
>> them working.
>>
>> For example:
>>
>> $sql_select_white_black_list =
>> 'SELECT wb FROM wblist LEFT JOIN mailaddr ON
wblist.sid=mailaddr.id'.
>> ' WHERE (wblist.rid=?)'
>> . ' AND (mailaddr.email IN (%k))'.
>> ' ORDER BY mailaddr.priority DESC';
>>     
>
> are you supplying a correct user.id  (to be matched
with wblist.rid)?
>
>   
>> or just...
>>
>> $sql_select_white_black_list = 'SELECT
"B" FROM wblist';
>> which to my undesrtanding that would mark anything
as BLACKMAILED.
>>     
>
> ...if there are any records in the wblist table.
>
>   
>> Jan 16 11:24:04 postfix01.glusterproves
/usr/sbin/amavisd-new[4223]:
>> (04223-01) wbl: checking sender <jordicdmon.com>
>> Jan 16 11:24:04 postfix01.glusterproves
/usr/sbin/amavisd-new[4223]:
>> (04223-01) lookup_sql_field(id) (WARN: no such
field in the SQL table),
>>     
>
> How is your table 'users' set up? It appears it is
lacking the 'id' field.
> This table lists your recipients (individually or just
their domain(s)).
>
>   Mark
>
>
------------------------------------------------------------
-------------
> This SF.net email is sponsored by: Microsoft
> Defy all challenges. Microsoft(R) Visual Studio 2008.
> http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
> _______________________________________________
> AMaViS-user mailing list
> AMaViS-userlists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/amavis-user

> AMaViS-FAQ:http://www.amav
is.org/amavis-faq.php3
> AMaViS-HowTos:http://www.amavis.org/ho
wto/
>   


------------------------------------------------------------
-------------
This SF.net email is sponsored by: Microsoft
Defy all challenges. Microsoft(R) Visual Studio 2008.
http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
_______________________________________________
AMaViS-user mailing list
AMaViS-userlists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/amavis-user

AMaViS-FAQ:http://www.amav
is.org/amavis-faq.php3
AMaViS-HowTos:http://www.amavis.org/ho
wto/