1

I want to count the alias usage of a postfix smtp server. Aliases are queried via MySQL using the postfix directive 'virtual_alias_maps' in main.cf. 

user = user
password = pwd
hosts = 127.0.0.1
dbname = dbname
query = SELECT destination FROM view_aliases WHERE email='%s'

Lookups work smoothly and there's nothing to complain about. What I need now is, to increase a counter (column in the MySQL table) any time postfix looks up an alias. I tried to chain the query like:

query = UPDATE view_aliases SET hitcount = hitcount+1 WHERE email='%s'; SELECT destination FROM view_aliases WHERE email='%s'

but that leads into an error message and a lookup problem of postfix:

Mar  7 11:04:01 host postfix/trivial-rewrite[32546]: warning: mysql query 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 'SELECT destination FROM view_aliases WHERE email='mydomain.com'' at line 1

Is there a way to create a hitcounter for postfix alias usage like I need it?

Update: According to the hint of ethrbunny, I tried to write the following procedure (this time in a test database): 

DELIMITER & 
CREATE PROCEDURE mailbox(IN recipient VARCHAR(255)) 
BEGIN
    SELECT * FROM test WHERE email=recipient; 
    UPDATE test SET value=value+1 WHERE email=recipient; 
END & 
DELIMITER ;

When I call the procedure with CALL mailbox('test@test.com') I get a Thread stack overrun error message from the server...

Thomas M.
  • 600
  • 1
  • 6
  • 14
  • Could you call a stored procedure? – ethrbunny Mar 07 '13 at 12:04
  • Hmm. How can I pass %s to the procedure? – Thomas M. Mar 07 '13 at 13:46
  • Just speculating. Looks like postfix is treating your statement with printf.. so could you do 'query = call myproc( %s )'? – ethrbunny Mar 07 '13 at 14:04
  • I am not that MySQL expert, so I tried to write the following procedure: `DELIMITER ;; CREATE DEFINER=`root@localhost PROCEDURE mailbox(IN recipient TEXT) BEGIN. SELECT * FROM test WHERE email=recipient; UPDATE test SET value=value+1 WHERE email=recipient; END;; DELIMITER ;` When I call the procedure with `CALL mailbox('test@test.com')` I get a Thread stack overrun error message from the server... – Thomas M. Mar 07 '13 at 14:35
  • Can you call it directly from the mysql client? Also - can you update your question to include the procedure? It's hard to visualize in a comment. – ethrbunny Mar 07 '13 at 15:49
  • I tried that directly from a MySQL client and not via Postfix as I want create a working query before testing it on my mailserver... – Thomas M. Mar 07 '13 at 16:53
  • What is 'BEGIN.' - why the '.'? Also - make your delimiter something like '&' or '!' – ethrbunny Mar 07 '13 at 17:07
  • Sorry my fault: The "." was a C&P failure, so in my db procedure there is no ".". I changed the `DELIMITER`, but that makes no difference. – Thomas M. Mar 08 '13 at 16:14
  • Do you get the error if you hardcode the email address in and then call? – ethrbunny Mar 08 '13 at 16:22
  • Same problem, when hardcoding the email address into the procedure :-( – Thomas M. Mar 13 '13 at 09:25
  • 1. is the error msg still the same? 2. are you doing 'select*' or 'select username'? should be the latter. – ethrbunny Mar 13 '13 at 12:50
  • I was doing a `select *`, but I tried it also with a `select email` (there is no username column in my test-table). I got the same error message. – Thomas M. Mar 19 '13 at 12:09
  • Have you looked at this: http://stackoverflow.com/questions/10376725/how-do-i-resolve-a-mysql-thread-stack-overrun – ethrbunny Mar 19 '13 at 12:29
  • Holy crap, thanks for that link. Now I am able to call the procedure in my test environment without any problems. The result is as expected. I will test that on my postfix server the next few days. Thank you! – Thomas M. Mar 22 '13 at 07:45

0 Answers0