Hello everyone
I have an issue here that I couldn't find much info about, I saw a lot about it when people where selecting a lot of data but not on an insert.
Heres some background. I have an application that adds items from our in house order/inventory manager to our online store, everything was working great on the local dev server, but now when testing on the live server, I'm getting "Connection unexpectedly terminated" errors randomly through the main loop.
Heres the error:
03/12/10 17:02 E Error: Connection unexpectedly terminated
03/12/10 17:02 E at MySql.Data.MySqlClient.PacketReader.ReadHeader()
at MySql.Data.MySqlClient.PacketReader.OpenPacket()
at MySql.Data.MySqlClient.NativeDriver.ReadResult(Int64& affectedRows, Int64& lastInsertId)
at MySql.Data.MySqlClient.CommandResult.ReadNextResult(Boolean isFirst)
at MySql.Data.MySqlClient.CommandResult..ctor(Driver d, Boolean isBinary)
at MySql.Data.MySqlClient.NativeDriver.SendQuery(Byte[] bytes, Int32 length, Boolean consume)
at MySql.Data.MySqlClient.MySqlCommand.GetNextResultSet(MySqlDataReader reader)
at MySql.Data.MySqlClient.MySqlCommand.Consume()
at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
at Service.PrestaShop.addItem(Product item)in the code, right before i do the SQL for the product i check that the connection is still open using
public Boolean addItem(Product item)
{
SrvLog.PutLog(LogLevel.D, "Adding item: " + item.Description);
const string sql = "INSERT INTO product" +
"(id_product, id_supplier, id_tax, id_category_default, on_sale, ean13, price, wholesale_price, supplier_reference, location, weight, out_of_stock, active, date_add, date_upd)" +
"VALUES " +
"(?product_id, ?supplier_id, 0, ?category_id, 0, ?product_upc, ?product_price, ?product_wholesale, ?product_number, ?product_location, ?product_weight, 1, 1, CURRENT_DATE, CURRENT_DATE);" +
"INSERT INTO product_lang" +
"(id_product, id_lang, description, link_rewrite , meta_description, meta_title, name)" +
"VALUES " +
"(?product_id, 1, ?product_description, ?product_link, ?product_description, ?product_name, ?product_name);" +
"INSERT INTO image" +
"(id_image ,id_product, position, cover)" +
"VALUES " +
"(?product_id, ?product_id, 1, 1);" +
"INSERT INTO category_product" +
"(id_category, id_product, position)" +
"VALUES " +
"(?category_id, ?product_id, 0);";
//reopen the connection just incase it closed
checkConnection();
var command = _db.CreateCommand();
var commandFile = _db.CreateCommand();
try
{
command.CommandText = sql;
command.Prepare();
//Fill in them infos
command.Parameters.Add("?product_id", item.SKU);
command.Parameters.Add("?supplier_id", item.SupplierNo);
command.Parameters.Add("?category_id", item.CategoryNo);
command.Parameters.Add("?product_upc", item.UPC);
command.Parameters.Add("?product_price", item.Price);
command.Parameters.Add("?product_wholesale", item.Wholesale);
command.Parameters.Add("?product_number", item.ProductNo);
command.Parameters.Add("?product_location", item.Bin);
command.Parameters.Add("?product_weight", item.Weight);
command.Parameters.Add("?product_description", Utils.MySqlEscape(item.LongDesc));
command.Parameters.Add("?product_link", Utils.buildLink(item.Description));
command.Parameters.Add("?product_name", Utils.MySqlEscape(item.Description));
command.Parameters.Add("?products_image", item.Images[0]);
command.ExecuteNonQuery();
if(item.Files.Count > 0)
{
const string sqlFile =
"INSERT INTO mp3player (id_product, mp3_filename, mp3_label, product_list, date_add, date_upd)" +
"VALUES (?product_id, ?file_name, ?file_desc, 1, NOW(), NOW())";
foreach(var file in item.Files)
{
try
{
commandFile.Parameters.Clear();
commandFile.CommandText = sqlFile;
commandFile.Prepare();
//Fill in them infos
commandFile.Parameters.Add("?product_id", item.SKU);
commandFile.Parameters.Add("?file_name", file.FileName);
commandFile.Parameters.Add("?file_desc", Utils.MySqlEscape(file.FileDesc));
commandFile.ExecuteNonQuery();
}
catch (Exception e)
{
SrvLog.PutLog(LogLevel.E, "Error adding MP3 to item: {0}", item.ProductNo);
SrvLog.PutLog(LogLevel.E, e.Message);
SrvLog.PutLog(LogLevel.E, e.StackTrace);
}
}
}
}
catch (Exception e)
{
SrvLog.PutLog(LogLevel.E, "Error Adding item: " + item.Description + "(" + item.ProductNo + ")");
SrvLog.PutLog(LogLevel.E, "Error: " + e.Message);
SrvLog.PutLog(LogLevel.E, e.StackTrace);
return false;
}
finally
{
SrvLog.PutLog(LogLevel.D, "Uploading the image");
item.uploadImage();
SrvLog.PutLog(LogLevel.D, "Uploading the Files");
item.uploadFiles();
SrvLog.PutLog(LogLevel.I, "Added item: " + item.Description + "(" + item.ProductNo + ")");
}
return true;
}any ideas on what i can do to stop it from disconnecting me?
There is a long pause during the image and file uploads but they are at the end and the connection is checked at the top and is run every time this method is call from the main loop
Thank you for any guidance
Don G.
