C# · 12月 23, 2021

c# – 命令执行期间遇到致命错误

当我从我的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条记录),执行失败

解决方法 如果此处有多个操作,您的代码中可能存在线程安全问题,并且可能会受益于使用AsyncWaitHandle而不是轮询IAsyncResult的IsComplete属性.