MySQL Bugs: #6271: Blobs data corrupted depending on the order of insert clause ?

Bug #6271	Blobs data corrupted depending on the order of insert clause ?
Submitted:	26 Oct 2004 21:04	Modified:	19 Nov 2004 15:02
Reporter:	Frédéric Bruot	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	1.0.0.22708	OS:	Windows (XP Pro)
Assigned to:	Reggie Burnett	CPU Architecture:	Any

Description:
Depending on the order of columns used in Insert clause, specially when you are using blob (binary or text), data in the following columns blob are corrupted. Sometimes NUnit V 2.1.4 hang when you test the code. But it's with a big sample. I can repeat easyly the same problem, without hanging NUnit but with data corruption.

(sorry for my english) but you can easyly repeat the pb with the small code. This small code doesn't make a crash of NUnit, but it's the same problem.

<PLATEFORM>
MySqlConnector .Net : 1.0.0.22708
.Net Framework 1.1.4322 SP1
MySql : 5.0.1-alpha-debug via TCP/IP
</>

Sample 1. A sample who does not work see the results in the blob field. Corrupted.
Sample 2. The same sample. I've just permuted the fields in the Insert clause. See the result. It works.

Note : The type of xpDOSG_Avatar variable is byte[]... I've loaded a jpeg file of 13K before calling this function. Loading is ok. Don't worry on that.

How to repeat:
/**** SAMPLE 1 . This code doesn't work see the result in MySqlQuery Browser ****/

// Drop the table if exists
execSQL("DROP TABLE IF EXISTS Test2");
// Create the table again
execSQL("CREATE TABLE `Test2` (id INT unsigned NOT NULL auto_increment, `xpDOSG_Name` text COMMENT 'Le nom du dossier',`xpDOSG_Desc` text COMMENT 'Une description sur ce dossier',"+
				"`Avatar` MEDIUMBLOB COMMENT 'Image du dossier', `dtAdded` DATETIME, `dtTime` TIMESTAMP, PRIMARY KEY(id)) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Table générale des dossiers'");

			string sql = "INSERT INTO `Test2` (`xpDOSG_Name`,`dtAdded`, `xpDOSG_Desc`,`Avatar`, `dtTime`) VALUES(?name, ?dt, ?desc, ?Avatar, NULL)";
			MySqlCommand cmd = new MySqlCommand(sql, m_Cnx);
			cmd.Prepare();

			DateTime dt = DateTime.Now;
			dt = dt.AddMilliseconds( dt.Millisecond * -1 );

			
			cmd.Parameters.Add( "?name", "Ceci est un nom");
			cmd.Parameters.Add( "?desc", "Ceci est une description facile à plantouiller");
			cmd.Parameters.Add( "?avatar",xpDOSG_Avatar); 
			cmd.Parameters.Add( "?dt", dt);
			int count = cmd.ExecuteNonQuery();

/*** SAMPLE 2 WHO WORK AS WELL SEE THE ORDER OR FIELDS IN CLAUSE ***/

execSQL("DROP TABLE IF EXISTS Test2");
			execSQL("CREATE TABLE `Test2` (id INT unsigned NOT NULL auto_increment, `xpDOSG_Name` text COMMENT 'Le nom du dossier',`xpDOSG_Desc` text COMMENT 'Une description sur ce dossier',"+
				"`Avatar` MEDIUMBLOB COMMENT 'Image du dossier', `dtAdded` DATETIME, `dtTime` TIMESTAMP, PRIMARY KEY(id)) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Table générale des dossiers'");

			string sql = "INSERT INTO `Test2` (`xpDOSG_Name`,`dtAdded`, `Avatar`, `xpDOSG_Desc`,`dtTime`) VALUES(?name, ?dt, ?Avatar, ?desc, NULL)";
			MySqlCommand cmd = new MySqlCommand(sql, m_Cnx);
			cmd.Prepare();

			DateTime dt = DateTime.Now;
			dt = dt.AddMilliseconds( dt.Millisecond * -1 );

			
			cmd.Parameters.Add( "?name", "Ceci est un nom");
			cmd.Parameters.Add( "?desc", "Ceci est une description facile à plantouiller");
			cmd.Parameters.Add( "?avatar",xpDOSG_Avatar); 
			cmd.Parameters.Add( "?dt", dt);
			int count = cmd.ExecuteNonQuery();

Suggested fix:
I've tested differents orders in the cmd.Parameters.Add because i've thought that the bug was that the lib read it's collection by Index and not by name. But it's not the problem. The problem is really in the order of field in the clause Insert depending on their own type of data. Specially with blob.
Sorry to not help you more but it's my second day testing MySql and at the beginning i've searched a lot because i thought that the problem came from me.
So, pb with the .Net connector or pb with MySql engine ? Sorry to give you directly this feedback.

Bests regards

>>>> DU TO THE MySqlCommand.Prepare Method.
If you execute the "bugous" code without using the Prepare Method it work.

using  your sample 1, I was able to verify that one of the characters in the desc column changes upon insert.  I performed this test with beta 2 of Connector/Net so the behavior may be different than what you are seeing.

Hi Reggie,
Thanks for your quickly reply. 
Can you tell me when we can download the Beta 2 release ? 

Many thanks and bests regards

Frédéric

It's available now

Sorry i haven't see it...My eyes are a bit tired.
I download it, make a test and give you a feedback quickly.

With later

Hi Reggie,
Now it work as well with or without the prepare statement. Blob are no more truncated.
But it's another problem, du (i think) with encoding.
If you run the same test you'll see this value for the desc field:

1. Without the prepare statement : "Ceci est une description facile à plantouiller", that is the correct value.

2. With the prepare statement : "Ceci est une description facile Ã  plantouiller".

Note the difference between "à" and "Ã".

Kill the frogies !!! ;-))

Yes I see that too and I am working on finding the problem.

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

I'm not saying that the problem with the encoding is expected behavior but saying that the Connector/Net is doing what it is supposed to do.  We have found the problem to be a small bug in our implementation of prepared statements.  You should see this fix in 4.1.8

Hi Reggie

Just to confirm you that all work as well now with the lastest patches available.
The 1.0.2 Gamma patch erase all the problems i've notified : blob truncature, pb with french chars...

All my tests run fine now. 

Many thanks for the team and you.

Bests regards

Thanks for following up