Bug #84397 Problem with output parameters from stored procedures
Submitted: 4 Jan 2017 5:29 Modified: 12 Jan 2020 4:01
Reporter: Leo 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

[4 Jan 2017 5:29] Leo Carreon
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.
[4 Jan 2017 15:56] MySQL Verification Team
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.
[5 Jan 2017 0:06] Leo Carreon
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?
[5 Jan 2017 0:18] MySQL Verification Team
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.
[5 Jan 2017 4:47] Leo Carreon
It will take me a while to write a minimal test program to reproduce the same problem.
[5 Jan 2017 15:48] MySQL Verification Team
Please reopen this bug report after provided the test case. Thanks.
[5 Jan 2017 23:52] Leo Carreon
Files required to reproduce the problem being reported

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

[5 Jan 2017 23:54] Leo Carreon
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).
[6 Jan 2017 3:22] Leo Carreon
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
[6 Jan 2017 5:12] MySQL Verification Team
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
[6 Jan 2017 6:45] Leo Carreon
Using community-mysql packages as part of Fedora 25.  Used the same packages on Fedora 23.
[10 Jan 2017 9:15] MySQL Verification Team
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.
[1 Feb 2017 6:17] Leo Carreon
Any estimate when this problem will be fixed?
[10 May 2017 4:16] Leo Carreon
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.
[15 Oct 2017 1:31] Leo Carreon
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.
[13 Dec 2017 2:46] Leo Carreon
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.
[12 Jan 2020 4:01] Leo Carreon
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.