Fatal error encountered during command execution

when i execute stored procedure on remote mysql from my c# code i get the generic error: "Fatal error encountered during command execution" total details:

MySql.Data.MySqlClient.MySqlException: Fatal error encountered during command execution. --->  MySql.Data.MySqlClient.MySqlException: Fatal error encountered attempting to read the resultset. --->  MySql.Data.MySql开发者_JAVA百科Client.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();
        }
    }

the sp same like that:

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 ;

the connection string:

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

but when i exceute that on local mysql it success as well! for remote mysql i tryied to send little data (3 records) and it executed successfully, but for large data (1500 records) the execute is failed

---

I faced this problem when executing my query was taking more than my MysqlCommand CommandTimeout default value.So I simply increased that time and afterward I had no such error.

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

---

If there is more than one operation going on here you could have thread safety issues in your code, and could benefit from using an AsyncWaitHandle rather than polling on the IsComplete property of the IAsyncResult.