MySQL Bugs: #25605: BINARY and VARBINARY is returned as a string

Bug #25605	BINARY and VARBINARY is returned as a string
Submitted:	13 Jan 2007 0:37	Modified:	9 Mar 2007 13:34
Reporter:	Kornél Pál	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:		OS:
Assigned to:		CPU Architecture:	Any

Description:
BINARY and VARBINARY fields are returned as strings.

How to repeat:
Query BINARY or VARBINARY fields.

Suggested fix:
I expect BINARY and VARBINARY fields to be returned as byte arrays because they have no encoding and they are useful to store small images or hash codes.

MySqlValue.cs:
case MySqlDbType.Float: return new MySqlSingle();
case MySqlDbType.Double: return new MySqlDouble();
case MySqlDbType.Set:
case MySqlDbType.Enum:
case MySqlDbType.String:
case MySqlDbType.VarChar: return new MySqlString(type, true);
case MySqlDbType.Blob:
case MySqlDbType.MediumBlob:
case MySqlDbType.LongBlob:
default:
if (binary) return new MySqlBinary(type, true);
return new MySqlString(type, true);

I suggest not to break at MySqlDbType.VarChar that will allow string types to be represented as a byte array when they are binary.

Not enough information was provided for us to be able to handle this bug. Please re-read the instructions at http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Hi Kornél and thanks for your report.
Can you please attach:
  - c/NET version
  - NET FW (mono) version
  - MySQL server version
  - Small but *complete* test case

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

The bug is in the concept of handling binary fields. So it only depends on Connector/Net. Currently I am using Mono 1.2.3, MySQL 5.0.26, Connector/Net 5.0.3, on Linux and on the same server. The bug is in file Driver/Source/Field.cs on line 228 of Connector/Net 5.0.3 and in filemysqlclient/types/MySqlValue.cs on line 165 of Connector/Net 1.0.9.

Hi Kornél.

C:\mysql507\bin>mysql -T -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.0.34-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT BINARY 'something' AS BinaryData;
Field   1:  `BinaryData`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  binary (63)
Length:     9
Max_length: 9
Decimals:   31
Flags:      NOT_NULL BINARY

+------------+
| BinaryData |
+------------+
| something  |
+------------+
1 row in set (0.01 sec)

mysql>

From what I see in cl client, server returns Type as "VAR_STRING" with BINARY flag set but the result in c/NET is
-		dataReader	{MySql.Data.MySqlClient.MySqlDataReader}	MySql.Data.MySqlClient.MySqlDataReader
-		fields	{Dimensions:[1]}	MySql.Data.MySqlClient.MySqlField[]
-		[0]	{MySql.Data.MySqlClient.MySqlField}	MySql.Data.MySqlClient.MySqlField

mySqlDbType	VarChar	MySql.Data.MySqlClient.MySqlDbType
IsBinary	true	bool.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/20222

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/20223

Fixed in 5.0.4 and 1.0.10

An entry has been added to the 1.0.10 and 5.0.4 changelogs.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/21055

Thsi was previous fixed in 5.0.4 but that fix was found to be not appropriate, rolled out, and reworked for 5.0.5

Changelog entry in 5.0.4 moved to 5.0.5