Re: Using MySQL procedures

[giovanni]

On 2/21/23 17:31, Roko Dobovičnik wrote:

Hello everyone,

I've been using OpenSMTPD for few months now and I'm pretty happy with how it 
works and how easy it is to configure. Recently I decided to start using MySQL 
database to store tables with user accounts and aliases so I can easily connect 
other services to the same database. I found a man page TABLE_MYSQL(5) which 
describes how to configure smtpd to so. The problem is that I would like to 
have a bit different database structure than the one described on the man page, 
so I created MySQL stored procedures that can be called in order to fetch data 
needed by OpenSMTPD server. And when I configured server to call these 
procedures it simply crashes (when started with -v flag writes following to the 
log):

credentials[3280425]: debug: (re)connecting
credentials[3280425]: warn: wrong number of columns in resultset
credentials[3280425]: fatal: could not connect
warn: table-proc: imsg_read: Connection reset by peer
lookup: table-proc: exiting

Then I created same configuration as described on the man page and it worked, 
but then I tried to create stored procedure which does the same job as one of 
the queries:

DELIMITER $$
CREATE PROCEDURE get_aliases(IN email_var VARCHAR(255))
BEGIN
     SELECT destination FROM virtuals WHERE email = email_var;
END $$
DELIMITER ;

I am using MySQL views for this purpose and they works without issues, maybe 
this can be useful in your case as well.

 Giovanni

I changed line in configuration file from this:

query_alias SELECT destination FROM virtuals WHERE email=?;

To this, so procedure is called:

query_alias CALL get_aliases(?);

But then, same error occurred again.
So my question is, am I doing something wrong. And can OpenSMTPD be configured 
to call MySQL stored procedures.

Thanks,
Roko Dobovičnik

OpenPGP_signature
Description: OpenPGP digital signature