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
.
精彩评论