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...

Re: Problem with lookup_sql_dsn in amavisd-new

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'";

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?

*) @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.

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'";

Re: Problem with lookup_sql_dsn in amavisd-new

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:

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.

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! Edit: Scrap that, its not checking DKIM on anything now I'm guessing DKIM was defined using MyUSERS somehow which doesn't get called anymore. I'll keep playing

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.

Re: Problem with lookup_sql_dsn in amavisd-new

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.