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