Hey Guys,
this issue has decayed a bit but I now finally found the time (and the
nerves) to integrate the fix in my system.
I'm running Ubuntu 16.04 and trying not change to many things and be
able to have clean comparison I applied the patch to the apt sources and
only replaced the postfix-mysql package (ubuntu is using 3.1.0 it seems)
with my own.
I introduced some stored procedures and from my tests I conclude it
works. I will see in the next couple of days if there are issues I
overlooked.
I ran into an issue where I was returning no result set using
"smtpd_recipient_restrictions = check_recipient_access mysql:/[...]".
I'm not sure if that's an dict_mysql issue or caused by design upstream.
I overcame that by doing "select 1 from dual where false" when I don't
have anything else to return, which basically is an empty result set.
Thanks very much for your work,
Joel
On 2016-07-07 00:46, John Fawcett wrote:
On 07/04/2016 09:58 PM, j...@conductive.de wrote:
Quoting John Fawcett <j...@voipsupport.it>:
I can propose a code submission to add stored procedure support
(based
on the proof of concept code from 2008), but the biggest part will be
doing the testing and non regression testing not the actual coding.
I believe the best approach to adding stored procedure support is to
iterate over multiple result sets and if there are exactly two and
the
last one is the status result of the stored procedure and it is
successful then allow the lookup against the first result set. In all
other cases there should be an error for multiple result sets. In the
context of Postfix don't think it is useful to have more than one
result
set generated by multiple queries or more than two result sets
(including the status one) from a stored procedure.
John
That is exactly what I had on my mind.
The specific error message would enable admins to exactly see where
the trouble originates.
Also consuming all results still allows subsequent lookups to succeed
normally.
I would of course volunteer to "test" the change per se but I'm afraid
my knowledge of postfix's inner workings converges to zero, rendering
my testing unrepresentative at last :/
Although the subset of postfix interfacing with mysql is quite small.
Joel
Hi
here is my proposed submission to add mysql stored procedure support to
Postfix. As per Wietse's comments in the following thread
http://postfix.1071664.n5.nabble.com/fatal-table-lookup-problem-with-Postfix-lookup-call-to-MySQL-StoredProcedure-td13240.html
"I would be grateful if that support came with test cases for single
and multi-result queries, including non-error and error results so
that we can run the test whenever something in the code is changed."
One further thing to point out and that may need to be adjusted still:
An error is returned whenever:
- the check for further result sets gives an error instead of yes (0)
or
no (-1)
- the stored procedure final result set with the status is not as
expected (mysql_field_count returns 0)
- there is more than one result set containing data.
While testing with postmap and multiple result sets I was able to
generate the following error message from postmap.c
"postmap: fatal: table mysql:./mysql-test2.cf: query error: Success"
The final "Success" comes from %m parameter reporting the errno. Since
no system call error has been returned, but it is an application error
I
have forced errno to ENOTSUP. If instead the "Success" status is deemed
correct, then the statement setting errno and the inclusion of errno.h
can be elimianted without changing the basic functioning of the patch.
I am available to make any further adjustments needed. I look forward
to
feedback from Joel or other interested people about more extensive
testing.
John
