Bug #58700 Severe API breakage in the protocol in MySQL 5.5
Submitted: 3 Dec 2010 14:38 Modified: 20 Nov 2013 0:36
Reporter: Andrey Hristov Email Updates:
Status: Closed Impact on me:
None 
Category:Connectors: mysqlnd ( PHP ) Severity:S1 (Critical)
Version:5.5 OS:Any
Assigned to: CPU Architecture:Any

[3 Dec 2010 14:38] Andrey Hristov
Description:
 MySQL 5.5 introduces the possibility to update the values of variable bound at the client side, when using the binary protocol. The data is sent as additional result set just before the last result set, which has special meaning - result code from the execution of the call to the procedure. So far, so good.
However, if a procedure throws 3 result sets to the user, the client code would expect 4 result sets. This won't be true with 5.5 and thus backwards compatibility with existing applications will be broken. They might misbehave, crash, do something wrong. If the application is closed source the user that uses it doesn't know whether he can use MySQL 5.5 safely. System administrators need to recheck the code they have, whether it is compatible with this.
The problem arises from the always on, default, for sending this result set. This is the bug, the contract with the client is broken.

How to repeat:
Use some packet sniffer and simple program that uses a SP, which has IN, INOUT and OUT parameters. For example:
CREATE PROCEDURE `p1`(IN p_in INT, OUT p_out INT, INOUT p_inout INT) BEGIN SET p_in = 100, p_out = 200, p_inout = 300; END//

Suggested fix:
Introduce a new flag to be used with client_capabilities, which is negotiated during the connection establishment handshake. I think we are out of free flag values (because the field is 16bit wide in the protocol), but probably some flag that is associated with a constant, which is no more used can be recycled. The client needs to negotiate that it knows about this capability of the server and only then the server should send this result set.
[13 Dec 2010 11:04] Lars Thalmann
On Fri, Dec 03, 2010 at 07:13:45PM +0300, Alexander Nozdrin wrote:
> Right, this is WL#4435.
>
> Right now, it's not clear where the bug is: a bug in the server,
> in the client, in the protocol or a misuse.
>
> I need more information:
>   - server version
>   - client application / library
>   - exact queries
>   - sniffed data
[15 Dec 2010 21:17] Sveta Smirnova
Thank you for the report.

Andrey, please provide information requested by Lars or at least example of C or PHP code.
[16 Dec 2010 13:24] Ulf Wendel
From - Tue Dec 14 11:18:17 2010
X-Mozilla-Status: 0013
X-Mozilla-Status2: 00000000
Received: from [192.168.1.100] (/77.0.229.90)
	by default (Oracle Beehive Gateway v4.0)
	with ESMTP ; Tue, 14 Dec 2010 02:17:54 -0800
Message-ID: <4D074445.3000305@oracle.com>
Date: Tue, 14 Dec 2010 11:17:41 +0100
From: Andrey Hristov <andrey.hristov@oracle.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9.2.12) Gecko/20101027 Thunderbird/3.1.6 ThunderBrowse/3.3.4
MIME-Version: 1.0
To: Alexander Nozdrin <alexander.nozdrin@oracle.com>
CC: Ulf Wendel <ulf.wendel@oracle.com>, 
 Lars Thalmann <lars.thalmann@oracle.com>,
 Rafal Somla <rafal.somla@sun.com>, 
 Staale Deraas <staale.deraas@oracle.com>,
 dbg-runtime@sun.com, Davi Arnaut <davi.arnaut@oracle.com>
Subject: Re: BUG#58700
References: <20101203152146.GA1573@akipp.com> <4CF90D98.6090700@Sun.COM> <4CF91739.1090705@oracle.com> <4CFCA514.4070109@oracle.com> <4D05E31C.7030704@oracle.com> <4D05E96C.4060508@Sun.COM> <4D05EE10.9060307@oracle.com> <4D05F118.9060509@Sun.COM> <20101213110125.GG30978@akipp.com> <4D0617EB.6070500@oracle.com> <4D061F96.7020109@oracle.com> <4D062216.20405@oracle.com> <4D062795.9030007@oracle.com> <4D063261.9010309@oracle.com> <4D06366B.6020801@oracle.com> <4D064E85.5070106@oracle.com>
In-Reply-To: <4D064E85.5070106@oracle.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

On 12/13/2010 05:49 PM, Alexander Nozdrin wrote:
> Hi all,
> 
> Andrei and I discussed this problem on IRC, and here is the outcome:
> 
> 1. The server uses CLIENT_MULTI_RESULTS capability
> to decide if the client allows multiple result sets or not.
> This flag works both for text and binary protocols.
> 
> 2. The server uses CLIENT_PS_MULTI_RESULTS capability
> to decide if the client is able to handle optional extra result set
> for OUT-parameters or not.
> 
> 3. This mapping is confusing, the name CLIENT_PS_MULTI_RESULTS
> is misleading.
> 
> 4. The reason of introducing CLIENT_PS_MULTI_RESULTS with a such
> semantic is now forgotten. Most likely, it's that we initially
> wanted to separate MULTI_RESULTS capabilities for text and binary
> protocols, then we came to release that such difference would be
> confusing and not needed for applications, then we changed the
> semantic and left the name.
> 
> 5. At WL#4435 design time, the server was able to send multiple result
> sets using binary protocol, but libmysql was not able to handle them.
> This was a libmysql limitation, which was extrapolated to other
> connectors.
> 
> What can be done now:
> 
> - Alternative #1:
> 
>   CLIENT_PS_MULTI_RESULTS can be renamed to something
>   like CLIENT_SUPPORT_OUT_PARAM_RESULT. The value of the flag
>   should remain the same.
> 
>   CLIENT_PS_MULTI_RESULTS will be "deprecated": for a few future
>   releases it will exist along with the new name, and then it will
>   be removed.
> 
>   mysqlnd should be modifed to remove CLIENT_PS_MULTI_RESULTS from
>   its capabilities.
> 
>   Here we need minor modifications in 5.5. We need to modify mysqlnd,
>   but hopefully the users haven't run into that problem, since there
>   is no complains so far.

yes, this is the easier, maybe we should do this.

> - Alternative #2:
> 
>   Change the semantic of CLIENT_PS_MULTI_RESULTS and introduce
>   a new flag (like CLIENT_SUPPORT_OUT_PARAM_RESULT).
> 
>   In this case, the server should be modified more deeper; mysqlnd and
>   its users will not experience any change.
> 
>   However, it's not clear why we should have two different capabilities
>   for text and binary protocols (CLIENT_MULTI_RESULTS and
>   CLIENT_PS_MULTI_RESULTS) -- it's not clear why should anyone want to
>   have CLIENT_MULTI_RESULTS set on and CLIENT_PS_MULTI_RESULTS -- off.

I can name one situation. old libmysqls, who had problems with multiple
PS result sets. They get stuck, if one prepare/execute-s a CALL with
multiple result sets. As these libmysql's can't grok it, 5.5 should not
send multiple result sets, even without the OUT RSet. However, I'm not
sure if this is technically possible.

For a client implementation to speak the text protocol and the binary
protocol are completely different things. The dataflow is different,
although for result sets not so much.

Let's go with #1, by renaming the flag to something else/add additional
name for it and deprecate the old name.

Best,
Andrey
[16 Dec 2010 15:10] Andrey Hristov
Well, if I tell you "cat" and I think "cat", but you meant "dog", whose fault is it? Proper naming should have been used in first place, don't blame the consumer for the wrong data on the packaging.
[17 Dec 2010 10:40] Andrey Hristov
Davi,
Alik shoulnd't have expressed concerns that it is a bug in mysqlnd, that is.
[10 Jan 2011 8:30] Ulf Wendel
AFAIK mysqlnd supports all funny flags - want to say: category is wrong.
[12 Jan 2011 8:49] Alexander Nozdrin
As we have discussed in email, mysqlnd "supports all funny flags"
in the way that it has CLIENT_PS_MULTI_RESULTS in its code.
The semantic of the "support" is wrong, so should be fixed.