"Unable to commit transaction" when running a CALL in Sendmail.conf

[scotia70]

I'm bringing this over from cyrusimap/cyrus-imapd#2792 at the urging of @dilyanpalauzov.

I won't recapitulate the entire thread; here's the gist:

Hi,

I'm using cyrus-sasl-sql 2.1.27 on FreeBSD 11 and 12 and builtin sendmail.
I use Cyrus SASL to authenticate SMTP users.
My /usr/local/lib/sasl2/Sendmail.conf looks like:

auxprop_plugin: sql
sql_engine: mysql
sql_user: xxx
sql_passwd: yyy
sql_hostnames: sql-host
sql_database: userdb
sql_select: CALL select_smtp_password('%u')
sql_usessl: no
log_level: 3

When inbound SMTP users perform a LOGIN my auth.log is populated with:

Jun  8 17:48:03 mail-01 sm-mta[93492]: Unable to commit transaction
Jun  8 17:48:03 mail-01 sm-mta[93492]: sql query failed: Commands out of sync; you can't run this command now
Jun  8 17:48:03 mail-01 sm-mta[93492]: sql query failed: Commands out of sync; you can't run this command now
Jun  8 17:48:03 mail-01 sm-mta[93492]: Unable to commit transaction`
for each LOGIN attempt.

Note that the query succeeds and everything works. I'm wondering if using a stored procedure and a CALL (which I do) is interacting poorly with the ./plugins/sql.c code (which I assume is normally called with an SQL SELECT statement).

I have tried two variants of the stored procedure:

START TRANSACTION;
INSERT INTO smtp_auths (name,count,last) VALUES (my_id,'1',NOW()) ON DUPLICATE KEY UPDATE count=count+1, last=NOW();
COMMIT;
SELECT clear FROM userdb WHERE id = my_id AND smtpauth = 'Y';
END

AND

INSERT INTO smtp_auths (name,count,last) VALUES (my_id,'1',NOW()) ON DUPLICATE KEY UPDATE count=count+1, last=NOW();
SELECT clear FROM userdb WHERE id = my_id AND smtpauth = 'Y';
END

I think the final "settings->sql_engine->sql_commit_txn" in ./plugins/sql.c is perhaps the culprit.

[scotia70]

And the fix:

Because of nature of the CALL statement, there is residual return data that needs to be consumed before another query can be made.

See https://bugs.mysql.com/bug.php?id=71044

My fix was to add the line:

mysql_next_result(conn);

after

mysql_free_result(result);

(line 158 of plugins/sql.c).

The change works regardless of the type of query (SELECT or CALL).