1

Topic: Problem with lookup_sql_dsn in amavisd-new

==== Required information ====
- iRedMail version: 0.8.1
- Store mail accounts in which backend (LDAP/MySQL/PGSQL): MySQL
- Linux/BSD distribution name and version: CentOS 6.3
- Related log if you're reporting an issue:
====

I've tried to uncomment the following lines at /etc/amavisd/amavisd.conf:

# Uncomment below two lines to lookup virtual mail domains from MySQL database.
@lookup_sql_dsn =  (
    ['DBI:mysql:database=vmail;host=127.0.0.1;port=3306', 'vmail', 'XXXXXXXXXXXXXXXXXXXXXXXXXXXX']
);

# For Amavisd-new-2.7.0 and later versions. Placeholder '%d' is available in Amavisd-2.7.0+.
#$sql_select_policy = "SELECT domain FROM domain WHERE domain='%d'";

# For Amavisd-new-2.6.x.
# WARNING: IN() may cause MySQL lookup performance issue.
$sql_select_policy = "SELECT domain FROM domain WHERE CONCAT('@', domain) IN (%k)";

The default installation of amavisd-new is amavisd-new-2.6.6-3.el6.noarch, so the last sql_select_policy was used.

Restarted amavisd and:

Aug 16 18:23:46 pardal amavis[3799]: (03799-01) (!)lookup_sql: sql exec: err=16, S1000, DBD::mysql::st bind_param failed: Illegal parameter number at (eval 99) line 164, <GEN21> line 4., 16, Illegal parameter number
Aug 16 18:23:46 pardal amavis[3799]: (03799-01) (!!)TROUBLE in process_request: sql exec: err=16, S1000, DBD::mysql::st bind_param failed: Illegal parameter number at (eval 99) line 164, <GEN21> line 4. at (eval 102) line 281, <GEN21> line 4.
Aug 16 18:23:46 pardal amavis[3799]: (03799-01) (!)Requesting process rundown after fatal error

None message are processed.

2

Re: Problem with lookup_sql_dsn in amavisd-new

ariarantes wrote:

DBD::mysql::st bind_param failed: Illegal parameter number at (eval 99) line 164, <GEN21> line 4., 16, Illegal parameter number

Seems your bind username or password is wrong. Did you check them on command line? e.g.

# mysql -uvmail -p

3

Re: Problem with lookup_sql_dsn in amavisd-new

ZhangHuangbin wrote:
ariarantes wrote:

DBD::mysql::st bind_param failed: Illegal parameter number at (eval 99) line 164, <GEN21> line 4., 16, Illegal parameter number

Seems your bind username or password is wrong. Did you check them on command line? e.g.

# mysql -uvmail -p

Yes I've checked. There is no problem write this kind of password here, only localhost is listen...

/etc/amavisd.conf:

@lookup_sql_dsn =  (
    ['DBI:mysql:database=vmail;host=127.0.0.1;port=3306', 'vmail', 'xxx']
);

# For Amavisd-new-2.6.x.
# WARNING: IN() may cause MySQL lookup performance issue.
$sql_select_policy = "SELECT domain FROM domain WHERE CONCAT('@', domain) IN (%k)";


# mysql -h localhost -u vmail --password=xxx vmail
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3025
Server version: 5.1.61 Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

4

Re: Problem with lookup_sql_dsn in amavisd-new

WARNING: Never write/paste passwords in public forum.

Could you please turn on verbose mode in MySQL to see what SQL command Amavisd actually executed?
To turn on verbose mode, please append this line under [mysqld] section in /etc/my.cnf:

log = /tmp/sql.log

5

Re: Problem with lookup_sql_dsn in amavisd-new

I'm having exactly the same problem.

I'm running Amavis-new 2.6.6. so I uncommented the same lines, with the same result.

You asked the OP to log the queries, but that is rather pointless, because the error is that binding the parameter fails, so it's the construction of the query in the DB layer that fails, it isn't even passed on for execution.

To be sure, I did a tcpdump + pt_query_digest to look at the queries run (our setup uses a central DB server, so query logging there is not an option).

How to fix this, I don't currently have any anti-spam measures active on my domains, which is quite an issue for some as we receive hunderds of spam mails a day for those domains...

6

Re: Problem with lookup_sql_dsn in amavisd-new

Did anybody solve this problem? Same here:

Apr 22 19:29:29 server amavis[9664]: (09664-01) (!)lookup_sql: sql exec: err=16, S1000, DBD::mysql::st bind_param failed: Illegal parameter number at (eval 115) line 168., ,
Apr 22 19:29:29 server amavis[9664]: (09664-01) (!!)TROUBLE in process_request: sql exec: err=16, S1000, DBD::mysql::st bind_param failed: Illegal parameter number at (eval 115) line 168. at (eval 118) line 324.
Apr 22 19:29:29 server amavis[9664]: (09664-01) (!)Requesting process rundown after fatal error

7

Re: Problem with lookup_sql_dsn in amavisd-new

BigJens wrote:

Apr 22 19:29:29 server amavis[9664]: (09664-01) (!!)TROUBLE in process_request: sql exec: err=16, S1000, DBD::mysql::st bind_param failed: Illegal parameter number at (eval 115) line 168. at (eval 118) line 324.

"bind_param failed", do you have correct SQL bind host/username/password?

8

Re: Problem with lookup_sql_dsn in amavisd-new

yes, a connection to the database via console is okay

9

Re: Problem with lookup_sql_dsn in amavisd-new

No idea yet, sorry. sad

10 (edited by harro.verton 2013-04-23 22:36:32)

Re: Problem with lookup_sql_dsn in amavisd-new

There is nothing wrong with the database, config or connection. Everything runs fine.

There error is: "DBD::mysql::st bind_param failed: Illegal parameter number".

This means that in the code a call is made to the bind() function of the MySQL driver, with an incorrect parameter number. For example, when a ->bind_param(2, 'formatstring') is called, but the prepared SQL statement doesn't have a placeholder for a second parameter.

Which probably means there's a discrepancy between the SQL in the amavisd config and what amavisd thinks it should be:

# For Amavisd-new-2.7.0 and later versions. Placeholder '%d' is available in Amavisd-2.7.0+.
#$sql_select_policy = "SELECT domain FROM domain WHERE domain='%d'";

# For Amavisd-new-2.6.x.
# WARNING: IN() may cause MySQL lookup performance issue.
#$sql_select_policy = "SELECT domain FROM domain WHERE CONCAT('@', domain) IN (%k)";

I'm running 2.6.6., so I uncommented the second one. It contains one placeholder (%k). but it looks like amavis expects something else here...

Looking in the postfix docs, I see this mentioned:

$sql_select_policy = 'SELECT "Y" as local, 1 as id FROM domain WHERE CONCAT("@",domain) IN (%k)';

Altough this only has one bind variable too (and the same %k), so that can't be the solution.

11

Re: Problem with lookup_sql_dsn in amavisd-new

Sorry for digging this out, but anyone made further progress here? I have exactly the same problem.

I recently noticed that there's plenty of spam delivered normally and not put to junk folder for not configured domains.

And I would really like amavisd to look up domains in MySQL.

Running amavisd-new-2.6.5 (20110407) @ Ubuntu 12.04.2 LTS here.

Or is there any elegant method to run a newer version of amavisd under Ubuntu (maybe self compiled) ?

12

Re: Problem with lookup_sql_dsn in amavisd-new

Hi @ted,

How about config Amavisd to read local domains from a plain text file, and dump all virtual mail domains from SQL/LDAP to this text file every 5 minutes (or 10 minutes, etc, it's up to you) with cron job?

13

Re: Problem with lookup_sql_dsn in amavisd-new

Thanks Zhang, that's a good idea too! I am using this variant for now.

Still, I'd prefer to have it perfect smile

14

Re: Problem with lookup_sql_dsn in amavisd-new

ted wrote:

Thanks Zhang, that's a good idea too! I am using this variant for now.

Still, I'd prefer to have it perfect smile

Personally, i think this is "perfect" solution (read a plain text file, dump virtual mail domains in it).

*) @lookup_sql_dsn is used to lookup Amavisd's SQL database, it can be different than @storage_sql_dsn.
*) The "problem" with @lookup_sql_dsn is, Amavisd requires several SQL tables (e.g. wblist, policy) in this database. If you config @lookup_sql_dsn to lookup our "vmail" database, you must create other required tables in "vmail" db too. It's not a good idea to create them in our "vmail" database.

15

Re: Problem with lookup_sql_dsn in amavisd-new

Here's a work around.  Create a view of vmail.domain in amavisd.  For example:

mysql -u root -p
msyql> create view amavisd.vmail_domian as select domain from vmail.domain;
mysql> GRANT SELECT ON `vmail` . * TO 'amavisd'@'localhost';
mysql> \q

edit /etc/amavisd/amavisd.conf and bottom of file make the following changes:
@storage_sql_dsn = (
    ['DBI:mysql:database=amavisd;host=127.0.0.1;port=3306', 'amavisd', 'xxxxxxxxxxxxxxx'],
);
# Uncomment below two lines to lookup virtual mail domains from MySQL database.
#@lookup_sql_dsn =  (
#    ['DBI:mysql:database=vmail;host=127.0.0.1;port=3306', 'vmail', 'xxxxxxxxxxxxxxxx'],
#);
# For Amavisd-new-2.7.0 and later versions. Placeholder '%d' is available in Amavisd-2.7.0+.
$sql_select_policy = "SELECT domain FROM vmail_domain WHERE domain='%d'";

It worked for me.

16

Re: Problem with lookup_sql_dsn in amavisd-new

Perfect. Maybe we should achieve this in iRedMail by default.

17

Re: Problem with lookup_sql_dsn in amavisd-new

I spoke too soon.  My last post does not work.
After digging through the amavisd code a bit, I've determined that the $sql_select_policy query will not work as an easy wholesale way to enable all domains to be scanned.  The reason being is that this query is intended to return a list of policy values.  The default query coded into amavisd actually looks like this:

$sql_select_policy =
    'SELECT users.*, policy.*, users.id'.
    ' FROM users LEFT JOIN policy ON users.policy_id=policy.id'.
    ' WHERE users.email IN (%k) ORDER BY users.priority DESC';

The simplest and easiest way to ensure that all email domains are scanned and tagged for spam is to use:

@local_domains_maps = ( ["."]);

Sorry for the bad info in my original post.

18

Re: Problem with lookup_sql_dsn in amavisd-new

Thanks for your digging. smile

19

Re: Problem with lookup_sql_dsn in amavisd-new

I think that this may possibly work: http://un.codiert.org/2008/10/amavisd-s … al-domain/
need some help testing it

*It'd require a MySQL View as such:*

CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `root`@`localhost` 
    SQL SECURITY DEFINER
VIEW `domains` AS
    SELECT 
        `vmail`.`domain`.`domain` AS `domain`
    FROM
        `vmail`.`domain` 
    UNION SELECT 
        `domain`.`alias_domain` AS `alias_domain`
    FROM
        `vmail`.`alias_domain` `domain`

With this, we'll probably need a few more tweaks to now tighten security.
Incoming Mail [from Remote to Local] is tagged as "Passed CLEAN {RelayedInternal}, MYUSERS LOCAL"
Outgoing Mail [from Local to Remote] is tagged as " Passed CLEAN {RelayedInternal}, MYNETS/MYUSERS LOCAL"

So MyNets should be the only directive with Originating on, and less scanning. I think..?
We can also remove DKIM Checking for outgoing mails, as it is a bit useless it always just marks them as PASS, JUST GENERATED ASSUMED GOOD, which makes no effect down the line just adds useless headers.

20

Re: Problem with lookup_sql_dsn in amavisd-new

Adding to the above, I managed to improve it one step further.
Replace the first line with the second, as well as adding that MySQL Query shown above marks all incoming e-mail as LOCAL and outgoing as MYNETS LOCAL. I've no clue what happened to MyUSERS now but do we really need it? We have can now bypass on MyNets. I'll keep playing.
Best part this already has fixed one thing, it no longer DKIM Checks Outgoing mails! smile
Edit: Scrap that, its not checking DKIM on anything now hmm I'm guessing DKIM was defined using MyUSERS somehow which doesn't get called anymore. I'll keep playing

#@local_domains_maps = 1;
@local_domains_maps = ( [".$mydomain"] );

21

Re: Problem with lookup_sql_dsn in amavisd-new

Okay I think I've fixed it, the MYSQL View I was using in the first place was wrong, a bit of MariaDB Logging led me to that. Use the following instead, and also do the @local_domains_maps change I mentioned above, I have yet to fix the Policy Banks and then will include my whole config file as text and a file along with the MySQL View.

CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `root`@`localhost` 
    SQL SECURITY DEFINER
VIEW `amavisd`.`domains` AS
    SELECT 
        CONCAT("@", `vmail`.`domain`.`domain`) AS `domain`
    FROM
        `vmail`.`domain` 
    UNION SELECT 
        CONCAT("@", `domain`.`alias_domain`) AS `alias_domain`
    FROM
        `vmail`.`alias_domain` `domain`

It DKIM Verifies all incoming mail, even if its Local=>Local, but not Local=>Remote, so *=>Local, which is perfect smile

22

Re: Problem with lookup_sql_dsn in amavisd-new

This VIEW should work well. I will improve iRedMail with this change.

23

Re: Problem with lookup_sql_dsn in amavisd-new

UPDATE: this VIEW is ok, but we need to modify '$sql_select_policy' setting in Amavisd to make it return per-recipient policy (records in `amavisd.users` and `amavisd.policy`). Still no good idea how to improve default $sql_select_policy setting.