MySQL Bugs: #84397: Problem with output parameters from stored procedures

Bug #84397	Problem with output parameters from stored procedures
Submitted:	4 Jan 2017 5:29	Modified:	12 Jan 2020 4:01
Reporter:	Leonides Carreon	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: C API (client library)	Severity:	S1 (Critical)
Version:	8.0.18	OS:	Fedora (Fedora 31)
Assigned to:		CPU Architecture:	x86

Description:
I built a set of MySQL C++ classes using Fedora 23 which contains MySQL 5.6.29. The C++ classes use the MySQL C API to access the MySQL server on a separate machine. All my classes to do with connections, statements, resultsets, prepared statements and stored procedures are all working fine in this environment. I even have test cases to do with in, out and in/out parameters of stored procedures.

I have now migrated my C++ classes to Fedora 25 containing MySQL 5.7.17. Now I'm having problems with output parameters and encountering an error with the following message:

HY000, 2014, Commands out of sync; you can't run this command now

which occurs when my code attempts to call mysql_stmt_next_result() after a recent call to mysql_stmt_execute() when processing output parameters. This sequence of calls was not a problem on MySQL 5.6.29 but it is now on MySQL 5.7.17.

Unfortunately, I cannot give you a copy of my source code due to its complexity. I have been busy reviewing the MySQL 5.7.17 C API documentation in relation to output parameters to determine if there were any obvious changes.

At this stage, I'm open to suggestions because I haven't been able to pin-point what is causing the problem considering that it was working in a previous version of MySQL.

How to repeat:
This is the stored procedure that I'm using:

DROP PROCEDURE IF EXISTS test_in_out_3;

DELIMITER //

CREATE PROCEDURE test_in_out_3
(
val1 SMALLINT,
val2 SMALLINT UNSIGNED,
OUT val3 SMALLINT,
OUT val4 SMALLINT UNSIGNED
)
BEGIN
SELECT sshort, ushort INTO val3, val4 FROM testtbl WHERE sshort = val1 AND ushort = val2;
END //

DELIMITER ;

Unfortunately, like I mentioned in my description I am unable to give you a simplified working version of my code to illustrate the sequence of MySQL C API calls.

We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug.

Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/

http://dev.mysql.com/doc/refman/5.7/en/commands-out-of-sync.html

Thank you for your interest in MySQL.

I still believe this is a regression in MySQL 5.7.17 as compared to MySQL 5.6.19 because my code which is calling a stored procedure with output parameters and is therefore expecting to detect an output parameter resultset.  However, no output parameter resultset is detected thus leading to the commands out of sync message.  So where did the expected output parameter resultset end up to?

Thank you for the feedback. Then please provide a complete test case sql script with (table/SP) and the a client code to run against 5.6 and 5.7. Thanks.

It will take me a while to write a minimal test program to reproduce the same problem.

Please reopen this bug report after provided the test case. Thanks.

Files required to reproduce the problem being reported

Attachment: bug-mysql-84397.zip (application/x-zip-compressed, text), 3.30 KiB.

I have supplied the test program which reproduces the problem I'm reporting in this bug report.

Just to repeat, the program works successfully on Fedora 23 (MySQL 5.6.19) but fails on Fedora 25 (MySQL 5.7.17).

This is the output of the test program for Fedora 23:
Number of parameters = 2
Number of output parameters detected = 1
Output parameter name = val2
Output parameter type = 254
Output parameter length = 3
Output parameter value = N

This is the output of the test program for Fedora 25:
Number of parameters = 2
Error: mysql_stmt_next_result() HY000,2014,Commands out of sync; you can't run this command now

Hi,

thanks for the test case. One question, how did you install mysql on that F25? From Oracle YUM repo or from a tgz or tar with rpm's or ?

all best
Bogdan

Using community-mysql packages as part of Fedora 25.  Used the same packages on Fedora 23.

Hi,

confirmed on F25 even with F25 libraries .. 

[root@localhost ~]# vi bug84397.cpp
[root@localhost ~]# g++ -m64 -L/usr/lib64/mysql/ -lmysqlclient bug84397.cpp
[root@localhost ~]# ./a.out
Number of parameters = 2
Error: mysql_stmt_next_result() HY000,2014,Commands out of sync; you can't run this command now
[root@localhost ~]# g++ --version
g++ (GCC) 6.3.1 20161221 (Red Hat 6.3.1-1)
Copyright (C) 2016 Free Software Foundation, Inc.
This is free software; see the source for copying conditions.  There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.

Any estimate when this problem will be fixed?

My Fedora 25 just recently had their community-mysql packages upgraded to 5.7.18 and this problem still exists.  I have been doing more investigation and have discovered that the issue is that the mysql->server_status does not have the SERVER_PS_OUT_PARAMS flag set for the result set containing the values of the output parameters.  I have checked the result sets and one of them contains the values for the output parameters but no server_status flag is set.  This is exactly the same problem described in Bug#86119.

Has this problem been fixed?  I'm asking because this problem was marked as a duplicate of Bug #86119 which was also marked as a duplicate of Bug #79662.  However, I don't have access to Bug #79662 thus I couldn't find out if it has been resolved.  If it has been resolved, which version was the fix released in?

Btw, I have tested if this has been resolved in Fedora 26 but it doesn't seem like it.

I have tested this on Fedora 27 and lo and behold it isn't fixed yet.  How many iterations of Fedora before this gets fixed.  Last time it was working was on Fedora 23.

I have tested this issue with Community MySQL 8.0.18 on Fedora 31 and it appears that the issue has not been resolved.  The client side is still not receiving the flag (mysql->server_status & SERVER_PS_OUT_PARAMS) which indicates that a resultset contains output parameter values.  This flag not being set causes an out-of-sync operation as per the previous comments in this problem report.