MySQL Bugs: #36093: It's not possible to modify a mediumblob field in a transaction.

Bug #36093	It's not possible to modify a mediumblob field in a transaction.
Submitted:	15 Apr 2008 14:50	Modified:	30 Apr 2008 11:21
Reporter:	Joan Parera	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	5.1.5 or 5.2.1	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When I try to update a mediumblob field with a large file, more than 5MB, in a transaction on a remote BD, I get the error: "There is already an open DataReader associated with this Connection which must be closed first."

The update works right if the file is less than 5MB or if I connect to local Bd or if I don't use a transaction or if I use a .NET connector Version 1.0.10.

How to repeat:
byte[] vFile = System.IO.File.ReadAllBytes(@"C:\5-10.pdf"); // Length=6542143
string query = "UPDATE fitxersannexes SET fitxer=?fitxer WHERE id=762";

MySqlConnection myConnection = new MySqlConnection("Database=MyDb;Data Source=www.remotehost.com;Port=3306;User Id=MyUser;Password=MyPassword");

myConnection.Open();

MySqlTransaction myTrans = myConnection.BeginTransaction();

MySqlCommand myCommand = new MySqlCommand(query, myConnection, myTrans);

myCommand.Parameters.Add(new MySqlParameter("?fitxer", vFitxer));

myCommand.ExecuteNonQuery(); // It takes 1:42 minutes

myTrans.Commit(); // The error throws here

Try setting the CommandTimeout to zero on your command object.  It might be timing out.

Hi Joan. Did you tried Reggie's suggestion? What was the result?

Hi Tonci & Reggie.

I didn't try the Reggie's suggestion, because I think CommandTimeout=0 it's not valid value. On the other hand, I get the error in the commit, if the problem was the timeout, probably I get the error in execute.

Salut

Joan, never the less you could have tried.

Anyway, what is your MySQL server version? What is the .NET fw version? And, most important of all, what is your max_allowed_packet set to?

Tonci, I’ve tried, but I can’t.

Setting the value in my.cnf file ->> connect_timeout=0 in [mysqld]. I get connect_timeout=2 when a check the value ->> (show variables like 'connect_timeout').

Changing the connection string ->> 
MySqlConnection("Database=MyDb;Data Source=www.remotehost.com;Port=3306;User Id=MyUser;Password=MyPassword;connect timeout=0 ");

I get error: "Unable to connect to any of the specified MySQL hosts."

Server version     : 5.0.45
.NET fw            : 2.0
max_allowed_packet : 33553408 <-32M

Joan, thanks for info provided, so it's not about max_allowed_packet as it's much bigger than the file you're trying to store.

One digression; you tried to put timeout in connect string, don't do that. Reggie is talking of property of MySQLCommand! So, MySQLCommand command.CommandTimeout=0. Please try and notify me of result.

It works!!!!

Sorry for my confusion with ConnectionTimeout, I didn't know the CommandTimeout property.

Tanks Tonci & Reggie.

Joan, I am glad things work now. Mr. Reggie Burnett is our c/NET team lead and his guesses are always correct.

Thanks for your interest in MySQL.