命令执行过程中遇到致命错误

Question

当我从我的C#代码执行远程MySQL存储过程时，我收到了一条通用错误信息：

"在执行命令的过程中遇到致命错误"

详细信息：

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

我的C#代码：

 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与那个类似：

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 ;

连接字符串：

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

但是当我在本地 MySQL 上执行时，它也成功了！对于远程 MySQL，我尝试发送少量数据（3条记录），并且执行成功，但是对于大量数据（1500条记录），执行失败了。

- David Michaeli

Answer 1

当执行查询的时间超过我的 MysqlCommand CommandTimeout 默认值时，我遇到了这个问题。因此，我只是增加了时间，之后就没有出现这样的错误了。

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

- Siyavash

Answer 2

如果这里有多个操作，你的代码可能存在线程安全问题，使用 AsyncWaitHandle 而不是轮询 IAsyncResult 的 IsComplete 属性可能会有所帮助。

- Simen S

---

非常感谢，我添加了 while (!myResult.IsCompleted) { myResult.AsyncWaitHandle.WaitOne(); } 这段代码，问题得到了解决！再次感谢。 - David Michaeli