Niles,
My
first guess is that you are attempting to use the DB_BARE_APPEND variable
inapropriatly.
In
both the incoming and outgoing filters it is expected that your SQL query be one
that returns rows (that is a select query), so by trying to use an insert query
you're not giving TMDA what it needs. I don't know the code, but my guess
is that TMDA is either failing when it attempts to fetch data from a non select
query, or failing because the substitution variables that you are using not
defined at the time the to-sql rule is run. Most likely the latter,
because I know %(sender) and %(recipient) aren't defined since I've tried to use
them, but if they were not there, the former might be a
concern.
To use
the DB_BARE_APPEND variable all you should need to do is define it, then
whenever TMDA encounters a bare=append rule in your
outgoing filter it will attempt to execute that query.
For
example in my config file I have:
ACTION_OUTGOING = "bare=append"
DB_BARE_APPEND = """INSERT INTO MyLists (ListName, Address, Source) VALUES ('WhiteList', %(recipient)s, 'BARE APPEND')"""
DB_BARE_APPEND = """INSERT INTO MyLists (ListName, Address, Source) VALUES ('WhiteList', %(recipient)s, 'BARE APPEND')"""
SQL_WHITELIST = """ \
SELECT Address, \
Action, \
OutAction \
FROM MyLists \
WHERE ListName in ('WhiteList','Friends','Family', \
'Work','BareWhite','Confirmed') \
AND %(criteria)s \
ORDER BY Action DESC"""
SELECT Address, \
Action, \
OutAction \
FROM MyLists \
WHERE ListName in ('WhiteList','Friends','Family', \
'Work','BareWhite','Confirmed') \
AND %(criteria)s \
ORDER BY Action DESC"""
(Note:
for whatever reason I have to use line continuation characters '\' in my SQL scripts if I want them to span multiple lines
in my config file, but that line continuation characters do not work in my
DB_BARE_APPEND and DB_CONFIRM_APPEND variables which need to be all on one line
<shrug>)
And in
my outgoing filter I have:
to-sql
-addr_column=Address -action_column=OutAction "${SQL_WHITELIST}" tag
envelope dated=10d from bare
Such
that when I send an email not caught by any of my other outgoing filters it gets
added to my WhiteList with NULL values for Action (used by my incoming filtres)
and OutAction (used by my outgoing filters). The next time around that I
send email to that address the to-sql rule above will catch the address and tag
it appropriately.
Long
story short try using a SELECT query with your to-sql filter
rule.
Dave
-----Original Message-----My problem:
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Niles Ingalls
Sent: Friday, July 16, 2004 3:25 PM
To: [EMAIL PROTECTED]
Subject: tmda-ofmipd db_bare_append issues
I'm receiving an error from tmda-ofmipd, related to my
DB_BARE_APPEND sql query.
Here's the relevant information from /etc/tmdarc
DB_BARE_APPEND = """INSERT INTO tmda VALUES( %(sender)s, %(recipient), 'ok', '')"""
Here's my output filter:
to-sql -addr_column=preference "${DB_BARE_APPEND}" bare
Here's the error from tmda-ofmipd when run:
/usr/local/tmda-1.0.3/bin/tmda-ofmipd -d -f -S /usr/exim/exim_outgoing.php -R imap localhost
Error: failure delivering message to command "['/usr/local/tmda-1.0.3/bin/tmda-inject', '[EMAIL PROTECTED]']" (command "['/usr/local/tmda-1.0.3/bin/tmda-inject', '[EMAIL PROTECTED]']" exited 1 (Traceback (most recent call last):
File "/usr/local/tmda-1.0.3/bin/tmda-inject", line 457, in ?
main()
File "/usr/local/tmda-1.0.3/bin/tmda-inject", line 431, in main
[from_address])
File "./TMDA/FilterParser.py", line 1030, in firstmatch
File "./TMDA/FilterParser.py", line 845, in __search_sql
File "/usr/lib/python2.3/site-packages/MySQLdb/cursors.py", line 95, in execute
return self._execute(query, args)
File "/usr/lib/python2.3/site-packages/MySQLdb/cursors.py", line 114, in _execute
self.errorhandler(self, exc, value)
File "/usr/lib/python2.3/site-packages/MySQLdb/connections.py", line 33, in defaulterrorhandler
raise errorclass, errorvalue
KeyError: 'sender'))
error: uncaptured python exception, closing channel <__main__.SMTPChannel connected 66.17.219.194:51152 at 0x4033feec> (exceptions.IOError:tmda-inject failed! [/usr/lib/python2.3/asyncore.py|read|69] [/usr/lib/python2.3/asyncore.py|handle_read_event|390] [/usr/lib/python2.3/asynchat.py|handle_read|136] [./tmda-ofmipd|found_terminator|837] [./tmda-ofmipd|process_message|1132])
tmda-ofmipd is crashing on any substituted variables,
(username, hostname, sender, fromheaders) but the sql
query runs file if I hard-code the sender & recipient
address & my other SQL queries run fine when accessed from
tmda-filter.
Any help is greatly appreciated
Niles Ingalls
My configuration:
Slackware Linux 9.1
TMDA 1.0.3
Exim 4.34 (using Virtual Domains)
MySQL 3.23.58
courier-imap 3.0.5
Python-2.3.4
Spamassassin-2.63
Amavis
_____________________________________________ tmda-users mailing list ([EMAIL PROTECTED]) http://tmda.net/lists/listinfo/tmda-users
