MySQL Bugs: #22333: Connector/NET is not treating Blob fields as binary

Bug #22333	Connector/NET is not treating Blob fields as binary
Submitted:	14 Sep 2006 3:51	Modified:	22 Sep 2006 2:04
Reporter:	John Wiegley	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	1.0.7	OS:	MacOS (Mac OS/X 10.4.7)
Assigned to:		CPU Architecture:	Any
Tags:	BINARY, BLOB, field, value

Description:
In mysqlclient/Types/MySqlValue.cs, you'll find this code at the bottom of GetMySqlValue:

				case MySqlDbType.Set:
				case MySqlDbType.Enum:
				case MySqlDbType.String:
				case MySqlDbType.VarChar: 
					return new MySqlString(null, type);

				case MySqlDbType.Blob:
				case MySqlDbType.MediumBlob:
				case MySqlDbType.LongBlob:
				default:
					if (binary) return new MySqlBinary( null, type );
					return new MySqlString( null, type );

What this means is that BLOB fields are only regarded as Binary if the BINARY flag was set for the database field.  However, according to the MySQL documentation (and my own experiments), the BINARY flag can only be applied to textual field.  You can say "TEXT BINARY", but you can't say "BLOB BINARY", since BLOBs are binary by definition.

How to repeat:
Create a table with a BLOB field.  Try to insert something into it using Connector/NET.  You should get an error saying that such an insert can only be applied to a binary field.

Suggested fix:

I think the code above was meant to be as follows:

				case MySqlDbType.Set:
				case MySqlDbType.Enum:
				case MySqlDbType.String:
				case MySqlDbType.VarChar: 
					if (binary) return new MySqlBinary( null, type );
					return new MySqlString(null, type);

				case MySqlDbType.Blob:
				case MySqlDbType.MediumBlob:
				case MySqlDbType.LongBlob:
				default:
					return new MySqlBinary( null, type );

I am reporting this because this is exactly what I had to do locally in order to store binary photo images into my MySQL database using Connector/NET.

Hi John. I'm a bit puzzled with this report. What is your problem and where's the test case demonstarting it? What is your client platform and what is your server platform? Are you running connector/NET on Mac (mono)? If on Windows, what is your NET framework?

I took first example from manual and it works on MySQL 5.0.24BK on WinXP Pro SP2 localhost with NET 2.0:

            MySql.Data.MySqlClient.MySqlConnection conn;
            MySql.Data.MySqlClient.MySqlCommand cmd;
            conn = new MySql.Data.MySqlClient.MySqlConnection();
            cmd = new MySql.Data.MySqlClient.MySqlCommand();
            string SQL;
            int FileSize;
            byte[] rawData;
            FileStream fs;
            conn.ConnectionString = "server=127.0.0.1;uid=root;pwd=;database=test;";
            try
            {
                fs = new FileStream(@"c:\somefile.ext", FileMode.Open, FileAccess.Read);
                FileSize = (int)fs.Length ;
                rawData = new byte[FileSize];
                fs.Read(rawData, 0, FileSize);
                fs.Close();
                conn.Open();
                cmd.Connection = conn;

                SQL = "DROP TABLE IF EXISTS bug22333";
                cmd.CommandText = SQL;
                cmd.ExecuteNonQuery();

                SQL = "CREATE TABLE bug22333(file_id SMALLINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY," +
                    "file_name VARCHAR(64) NOT NULL,"+
                    "file_size MEDIUMINT UNSIGNED NOT NULL,"+
                    "file MEDIUMBLOB NOT NULL);";
                cmd.CommandText = SQL;
                cmd.ExecuteNonQuery();

                SQL = "INSERT INTO bug22333 VALUES(NULL, ?FileName, ?FileSize, ?File)";
                cmd.CommandText = SQL;
                cmd.Parameters.Add("?FileName", "16934-bigintuns.jpg");
                cmd.Parameters.Add("?FileSize", FileSize);
                cmd.Parameters.Add("?File", rawData);
                cmd.ExecuteNonQuery();
                MessageBox.Show("File Inserted into database successfully!",
                "Success!", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                conn.Close();
            }
            catch (MySql.Data.MySqlClient.MySqlException ex)
            {
                MessageBox.Show("Error " + ex.Number + " has occurred: " + ex.Message,
                "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

As for connector/NET code you refer to 
  default:
	if (binary) return new MySqlBinary( null, type );
	return new MySqlString( null, type );
binary flag is SET for BLOB fileds, at least in my debugger:
type	Blob	MySql.Data.MySqlClient.MySqlDbType
unsigned	false	bool
binary	true	bool  <<<

Hello Tonci,

I encountered this bug using Visual Web Developer 2005 on Windows XP Professional (sorry that the OS information was wrong in the bug report).

I have a MediumBlob field in my database for holding photos.  I actually single-stepped through MySqlValue.cs, and I found that the binary flag is *not set* for that field.  This is what necessitated my change.  Without the change, I was getting an exception upon trying to insert the data about the field not being binary (this error was coming from Connector/NET).

This is the only platform I've seen this problem on.  Maybe this is a MySql 5.0 bug on Windows?

John

John, there's no such bug that I'm aware of. Please correct/add all information regarding this report and reopen it. I will also need your test case.