On 11/27/2016 01:47 PM, John Fawcett wrote:
> On 11/23/2016 10:54 PM, j...@conductive.de wrote:
>> On 2016-11-23 21:57, John Fawcett wrote:
>>> On 11/22/2016 01:35 AM, Joel Linn wrote:
>>>> 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
>>> Joel
>>>
>>> can you provide some more information to reproduce the issue you
>>> mentioned?
>>>
>>> John
>> Sure I can. Have a look at my log:
>>
>> Nov 23 22:42:49 leuchtkanone postfix/smtpd[5863]: connect from
>> divmail1.helmholtz-berlin.de[134.30.104.21]
>> Nov 23 22:42:49 leuchtkanone postfix/smtpd[5863]: warning:
>> mysql:/etc/postfix/sql/recipient-access.cf: table lookup problem
>> Nov 23 22:42:49 leuchtkanone postfix/smtpd[5863]: NOQUEUE: reject:
>> RCPT from divmail1.helmholtz-berlin.de[134.30.104.21]: 451 4.3.5
>> <j...@conductive.de>: Recipient address rejected: Server configuration
>> error; from=<joel.l...@helmholtz-berlin.de> to=<j...@conductive.de>
>> proto=ESMTP helo=<divmail1.helmholtz-berlin.de>
>>
>> I used this empty test procedure (query = CALL ret_empty;):
>>
>> CREATE DEFINER=`vmail`@`localhost` PROCEDURE `ret_empty`()
>> BEGIN
>> END
>>
>> In my procedure I had some code paths return a result and some not.
>> Like I said i'm now using "SELECT 1 FROM dual WHERE false;" for those
>> dead paths, this simulates the behavior of the previous solution (one
>> line query) which always returns a result set, even if it's empty.
> Joel
>
> with MySQL procedures every SELECT statement that is executed produces a
> result set. In my patch I explicitly check to make sure there are no
> multiple result sets, but I do not check for no result. That could be
> improved upon, to give a specific warning that no result sets were
> returned. However, that will always be a lookup failure. The stored
> procedure (based on the way the patch is written) must always return a
> result set even if that result set is empty.
>
> Your solution with returning an empty result set when there is no other
> result (SELECT 1 FROM DUAL WHERE FALSE) looks correct.
>
> John
>
Revised patch to improve error reporting when no result set containing
data is returned
diff -ur postfix-3.2-20161106-orig/proto/mysql_table
postfix-3.2-20161106/proto/mysql_table
--- postfix-3.2-20161106-orig/proto/mysql_table 2016-10-01
15:44:52.000000000 +0200
+++ postfix-3.2-20161106/proto/mysql_table 2016-11-27
10:08:05.535236131 +0100
@@ -289,6 +289,39 @@
# certificate.
# .sp
# This parameter is available with Postfix 2.11 and later.
+# USING MYSQL STORED PROCEDURES
+# .ad
+# .fi
+# With Postfix 3.2 it is possible to call a stored procedure
+# instead of using a SELECT statement in the query, e.g.
+#
+# .nf
+# query = CALL lookup('%s')
+# .fi
+#
+# The preivously described '%' expansions can be used in the
+# parameter(s) to the stored procedure.
+#
+# The stored procedure must return data with a single result
+# set. Multiple result sets are not supported and will
+# produce a warning and no valid result. Stored procedures in
+# mysql produce an additional result set without data which
+# indicates the final status of the stored procedure. If this
+# final status is an error then any previous returned data
+# will not be used.
+#
+# The following is an example of a stored procedure returning
+# data with a single result set:
+#
+# .nf
+# CREATE [DEFINER=`user`@`host`] PROCEDURE
+# `lookup`(IN `param` VARCHAR(255))
+# READS SQL DATA
+# SQL SECURITY INVOKER
+# BEGIN
+# select goto from alias where address=param;
+# END
+# .fi
# OBSOLETE QUERY INTERFACE
# .ad
# .fi
diff -ur postfix-3.2-20161106-orig/src/global/dict_mysql.c
postfix-3.2-20161106/src/global/dict_mysql.c
--- postfix-3.2-20161106-orig/src/global/dict_mysql.c 2016-09-25
17:27:11.000000000 +0200
+++ postfix-3.2-20161106/src/global/dict_mysql.c 2016-11-27
13:56:50.956334035 +0100
@@ -187,6 +187,7 @@
#include <time.h>
#include <mysql.h>
#include <limits.h>
+#include <errno.h>
#ifdef STRCASECMP_IN_STRINGS_H
#include <strings.h>
@@ -519,6 +520,10 @@
{
HOST *host;
MYSQL_RES *res = 0;
+ MYSQL_RES *temp_res = 0;
+ int status=0;
+ int num_rs=0;
+ int sp_error=0;
while ((host = dict_mysql_get_active(dict_mysql)) != NULL) {
#if defined(MYSQL_VERSION_ID) && MYSQL_VERSION_ID >= 40000
@@ -536,9 +541,46 @@
#endif
if (!(mysql_query(host->db, vstring_str(query)))) {
- if ((res = mysql_store_result(host->db)) == 0) {
- msg_warn("mysql query failed: %s", mysql_error(host->db));
+ sp_error = 0;
+ num_rs = 0;
+ res = 0;
+ do {
+ temp_res = mysql_store_result(host->db);
+ /* did statement return data? */
+ if (temp_res) {
+ num_rs++;
+ if (num_rs > 1) {
+ msg_warn("dict_mysql: multiple result sets
returning data are not supported");
+ mysql_free_result(temp_res);
+ temp_res = 0;
+ } else {
+ res = temp_res;
+ }
+ } else {
+ /* no data, check if there were errors */
+ if (mysql_field_count(host->db) == 0) {
+ if (num_rs == 0) {
+ msg_warn("dict_mysql: stored procedure did
not return any result set containing data");
+ } else {
+ if (msg_verbose)
+ msg_info("dict_mysql: successful final
result for stored procedure");
+ }
+ } else {
+ sp_error=1;
+ msg_warn("dict_mysql: unsuccessful final result
for stored procedure: %s", mysql_error(host->db));
+ }
+ }
+ /* more results? -1 = no, >0 = error, 0 = yes (keep
looping) */
+ if ((status = mysql_next_result(host->db)) > 0)
+ msg_warn("mysql query failed (mysql_next_result):
%s", mysql_error(host->db));
+ } while (status == 0);
+ if (status != -1 || sp_error || num_rs != 1) {
plmysql_down_host(host);
+ errno = ENOTSUP;
+ if (res) {
+ mysql_free_result(res);
+ res = 0;
+ }
} else {
if (msg_verbose)
msg_info("dict_mysql: successful query from host
%s", host->hostname);
@@ -587,7 +629,7 @@
dict_mysql->dbname,
host->port,
(host->type == TYPEUNIX ? host->name : 0),
- 0)) {
+ CLIENT_MULTI_RESULTS)) {
if (msg_verbose)
msg_info("dict_mysql: successful connection to host %s",
host->hostname);
