Bug #62435 not declaring variables returns a byte array instead of a varchar in .Net app

Submitted: 14 Sep 2011 17:39 Modified: 16 Sep 2011 16:48
Reporter: Eduardo Ceballos Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S2 (Serious)
Version:5.1.49, 5.1.60 OS:Linux (Debian)
Assigned to: CPU Architecture:Any
Tags: .net, 5.1.49, declaring, stored procedures, variables

[14 Sep 2011 17:39] Eduardo Ceballos
Description:
We changed from a MYSQL5.0.26 to a MYSQL5.1.49 server on a Debian server.

The stored procedure worked ok on the 5.0.26 but when i moved to 5.1.49 the problem appeared.

Description of the problem:
===========================
.Net won't understand the result of the Stored procedure as a varchar, while setting a variable inside of a stored procedure without declaring the variable before. It receives a Byte array containing the the chars of the varchar that should had been returned.

This is the section of the SProcedure that isn't working in 5.1.49 for .net:
----------------------------------------------------
CREATE DEFINER=`bancaria`@`%` PROCEDURE `pruebados`(par1 varchar(2))
BEGIN  
  set @a = lpad('1',2,'0');
  select @a  as "a";
END;
----------------------------------------------------

This is the app in .net:
----------------------------------------------------

                Dim sqlCommand As New MySqlCommand
                sqlCommand.Connection = ConectarToMySql(True)
                sqlCommand.CommandType = CommandType.StoredProcedure
                sqlCommand.CommandText = "pruebados"
                sqlCommand.Parameters.AddWithValue("par1", "1")
                Dim adapt = New MySqlDataAdapter(sqlCommand)
                adapt.Fill(dt)
----------------------------------------------------
The result in dt is 'System.Byte[]' which contains a 48,49 instead of '01' as it should.

If I change the Stored procedure by adding the declaration of the variable, it works ok:
----------------------------------------------------
CREATE DEFINER=`bancaria`@`%` PROCEDURE `pruebados`(par1 varchar(2))
BEGIN  
  declare a  varchar(2);
  set a = lpad('1',2,'0');
  select a  as "a";
END;

How to repeat:
CREATE DEFINER=`bancaria`@`%` PROCEDURE `pruebados`(par1 varchar(2))
BEGIN  
  set @a = lpad('1',2,'0');
  select @a  as "a";
END;
----------------------------------------------------

This is the app in .net:
----------------------------------------------------

                Dim sqlCommand As New MySqlCommand
                sqlCommand.Connection = ConectarToMySql(True)
                sqlCommand.CommandType = CommandType.StoredProcedure
                sqlCommand.CommandText = "pruebados"
                sqlCommand.Parameters.AddWithValue("par1", "1")
                Dim adapt = New MySqlDataAdapter(sqlCommand)
                adapt.Fill(dt)
----------------------------------------------------
The result in dt is 'System.Byte[]' which contains a 48,49 instead of '01' as it should.

Suggested fix:
it can be fixed by declaring all the variables before using them, but i would like to know why this is happening.
[16 Sep 2011 16:19] Valeriy Kravchuk
This change is easy to demonstrate. Compare 5.1:

macbook-pro:5.1 openxs$ bin/mysql -uroot --column-type-info test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.60 Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> call pruebados('');
Field   1:  `a`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       MEDIUM_BLOB
Collation:  latin1_swedish_ci (8)
Length:     16777215
Max_length: 2
Decimals:   31
Flags:      

+------+
| a    |
+------+
| 01   |
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

to 5.0:

macbook-pro:5.0 openxs$ bin/mysql -uroot -T test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.95-debug Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> call pruebados('');
Field   1:  `a`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     8192
Max_length: 2
Decimals:   31
Flags:      

+------+
| a    |
+------+
| 01   | 
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

So, indeed, we see that user variable in 5.1 is returned as BLOB while it is returned as string in 5.0. 

I do not see this incompatible change documented at http://dev.mysql.com/doc/refman/5.1/en/upgrading-from-previous-series.html.
[16 Sep 2011 16:48] Eduardo Ceballos
So how do I have to proceed? the problem is really a bug?
thank you

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.041 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.