Fatal error while executing command

Question

when I execute a stored procedure on a remote mysql from my C # code, I get a general error: "Fatal error while executing a command" general information:

MySql.Data.MySqlClient.MySqlException: Fatal error encountered during command execution. ---> MySql.Data.MySqlClient.MySqlException: Fatal error encountered attempting to read the resultset. ---> MySql.Data.MySqlClient.MySqlException: Reading from the stream has failed. ---> System.IO.EndOfStreamException: Attempted to read past the end of the stream. at MySql.Data.MySqlClient.MySqlStream.ReadFully(Stream stream, Byte[] buffer, Int32 offset, Int32 count) at MySql.Data.MySqlClient.MySqlStream.LoadPacket() --- End of inner exception stack trace --- at MySql.Data.MySqlClient.MySqlStream.LoadPacket() at MySql.Data.MySqlClient.MySqlStream.ReadPacket() at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int32& insertedId) at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int32& insertedId) at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId) at MySql.Data.MySqlClient.MySqlDataReader.NextResult() --- End of inner exception stack trace --- at MySql.Data.MySqlClient.MySqlDataReader.NextResult() at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior) --- End of inner exception stack trace --- at MySql.Data.MySqlClient.MySqlCommand.EndExecuteNonQuery(IAsyncResult asyncResult 

)

my c # code:

  internal void InsertUpdateMySql(string connectionstring, string storedprocedure) { string sLog = "internal void InsertUpdateMySql(string connectionstring, string storedprocedure)"; MySqlConnection conn = new MySqlConnection(); int rowsAffected = 0; try { if (!string.IsNullOrEmpty(storedprocedure) && !string.IsNullOrEmpty(connectionstring)) { conn.ConnectionString = connectionstring; MySqlCommand cmd = new MySqlCommand(storedprocedure); cmd.Connection = conn; conn.Open(); IAsyncResult myResult = cmd.BeginExecuteNonQuery(null, null); SetProgressinRichText("In progress\n"); while (!myResult.IsCompleted) { SetProgressinRichText("."); } rowsAffected = cmd.EndExecuteNonQuery(myResult); } } catch (MySqlException ex) { this.service.WriteToLog(source, sLog + "\n" + ex.Message + "\n" + ex.StackTrace); } catch (Exception ex) { this.service.WriteToLog(source, sLog + "\n" + ex.Message + "\n" + ex.StackTrace); } finally { if (conn.State != System.Data.ConnectionState.Closed) conn.Close(); } } 

sp is the same:

 DELIMITER $$ CREATE PROCEDURE `SPInsertUpdateCity`( in SP_CityName VARCHAR(100) charset utf8, in SP_CitySynonyms varchar(100) charset utf8, in SP_CityNumberPostOffice varchar(100)) BEGIN if(not exists(select CityID from city where CityName = SP_CityName)) then insert into city(CityName, CityNumberPostOffice) values(SP_CityName, SP_CityNumberPostOffice); insert into citysynonym (CityID, CitySynonymName) values(Last_insert_id(),SP_CitySynonyms); else if(not exists( select CitySynonymID from citysynonym where CitySynonymName = SP_CitySynonyms)) then insert into citysynonym (CityID, CitySynonymName) values((select cityid from city where CityName=SP_CityName), SP_CitySynonyms); end if; end if; END$$ DELIMITER ; 

connection string:

 Server=XXXX; Port=XXXX; Database=XXXX; Uid=XXXX;Pwd=XXXX;charset=utf8;default command timeout=7200; 

but when I rule out that on local mysql this is also a success! for remote mysql I tried to send small data (3 records) and executed successfully, but for big data (1500 records) the execution failed

Answer 1

If more than one operation occurs here, you may have thread safety issues in your code, and you can use AsyncWaitHandle instead of polling the IsComplete IAsyncResult .

Answer 2

I ran into this problem when running my query took longer than my default MysqlCommand CommandTimeout value. So I just increased this time, and then I didn’t have such an error.

 MysqlCommand cmd = new MysqlCommand(); cmd.CommandTimeout = 200;