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
Triage: Needs Triage: D2 (Serious)

[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