Amavis/Perl problems.

[Wiesemarc]

Everytime amavis are using mysql, i get this error:

TROUBLE in process_request: sql exec: err=1064, S1000, DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%K)) ORDER BY users.priority ASC' at line 1 at (eval 40) line 153, <GEN20> line 5. at (eval 44) line 264, <GEN20> line 5.


This is what i have written in amavis.conf (the old one) and in the new /etc/amavis/conf.d/50-user

$sql_policy = 'SELECT * FROM aliases,policy'.
' WHERE (aliases.policy_id=policy.id) AND (aliases.alias IN (%K))'.
' ORDER BY aliases.priority ASC';
$sql_wblist =
'SELECT wb'.
' FROM wblist LEFT JOIN mailaddr ON wblist.sid=mailaddr.id'.
' WHERE (wblist.rid=?) AND (mailaddr.email IN (%k))'.
' ORDER BY mailaddr.priority ASC';

%sql_clause = (
'sel_policy' => \$sql_policy,
'sel_wblist' => \$sql_wblist,
'sel_adr' => 'SELECT id FROM maddr WHERE email=?',
'ins_adr' => 'INSERT INTO maddr(email, domain) VALUES (?,?)',
'ins_msg' => 'INSERT INTO msgs (mail_id, secret_id, am_id, time_num, time_iso, sid, policy, client_addr, size, host) VALUES (?,?,?,?,?,?,?,?,?,?)',
'upd_msg' => 'UPDATE msgs SET content=?, quar_type=?, dns_sent=?, spam_level=?, message_id=?, from_addr=?, subject=?, WHERE mail_id=?',
'ins_rcp' => 'INSERT INTO msgrcpt (mail_id, rid, ds, rs, bl, wl, bspam_level, smtp_resp) VALUES (?,?,?,?,?,?,?,?)',
'ins_quar' => 'INSERT INTO quarantine (mail_id, chunk_ind, mail_text) VALUES (?,?,?)',
'sel_quar' => 'SELECT mail_text FROM quarantine WHERE mail_id=? ORDER BY chunk_ind',
);



I have copied the hole database from the old server and check the tables to see if they work.
As i think it's the %K there are missing something.

Hope someone knows the problem.

Best Regards

[falko]

This is what i have written in amavis.conf (the old one) and in the new /etc/amavis/conf.d/50-user

$sql_policy = 'SELECT * FROM aliases,policy'.
' WHERE (aliases.policy_id=policy.id) AND (aliases.alias IN (%K))'.
' ORDER BY aliases.priority ASC';
$sql_wblist =
'SELECT wb'.
' FROM wblist LEFT JOIN mailaddr ON wblist.sid=mailaddr.id'.
' WHERE (wblist.rid=?) AND (mailaddr.email IN (%k))'.
' ORDER BY mailaddr.priority ASC';

%sql_clause = (
'sel_policy' => \$sql_policy,
'sel_wblist' => \$sql_wblist,
'sel_adr' => 'SELECT id FROM maddr WHERE email=?',
'ins_adr' => 'INSERT INTO maddr(email, domain) VALUES (?,?)',
'ins_msg' => 'INSERT INTO msgs (mail_id, secret_id, am_id, time_num, time_iso, sid, policy, client_addr, size, host) VALUES (?,?,?,?,?,?,?,?,?,?)',
'upd_msg' => 'UPDATE msgs SET content=?, quar_type=?, dns_sent=?, spam_level=?, message_id=?, from_addr=?, subject=?, WHERE mail_id=?',
'ins_rcp' => 'INSERT INTO msgrcpt (mail_id, rid, ds, rs, bl, wl, bspam_level, smtp_resp) VALUES (?,?,?,?,?,?,?,?)',
'ins_quar' => 'INSERT INTO quarantine (mail_id, chunk_ind, mail_text) VALUES (?,?,?)',
'sel_quar' => 'SELECT mail_text FROM quarantine WHERE mail_id=? ORDER BY chunk_ind',
);


Is it maybe another query than the ones you posted that is failing? Because I can't find ORDER BY users.priority ASC in the queries you posted...

[Wiesemarc]

Yes, that was what i found out..
As it is, the DB had changed and the sql has to be updated.

And i don't know where the tutorial i used is, but is not one of yours, sorry.

Next check DB and sql :rolleyes: