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
diff -ur postfix-3.2-20160612_old/proto/mysql_table
postfix-3.2-20160612/proto/mysql_table
--- postfix-3.2-20160612_old/proto/mysql_table 2016-02-12 21:25:01.000000000
+0100
+++ postfix-3.2-20160612/proto/mysql_table 2016-07-06 23:59:53.334710109
+0200
@@ -277,6 +277,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-20160612_old/src/global/dict_mysql.c
postfix-3.2-20160612/src/global/dict_mysql.c
--- postfix-3.2-20160612_old/src/global/dict_mysql.c 2016-06-12
01:49:29.000000000 +0200
+++ postfix-3.2-20160612/src/global/dict_mysql.c 2016-07-06
23:01:47.657423280 +0200
@@ -187,6 +187,7 @@
#include <time.h>
#include <mysql.h>
#include <limits.h>
+#include <errno.h>
#ifdef STRCASECMP_IN_STRINGS_H
#include <strings.h>
@@ -518,6 +519,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
@@ -535,9 +540,42 @@
#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 (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);
@@ -586,7 +624,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);
