C # con MySQL a través de Connector / NET

Estoy desarrollando una aplicación CMS en C # (Framework 4.0) que se conecta a la base de datos MySQL (5.0.95) en un servidor remoto mediante MySQL Connector (6.5.4).

Tengo problemas con la ejecución de consultas.

Por ejemplo, mi cadena de conexión:

"Server=" + Options.DbServer + ";Database="+ Options.Database +";Uid=" + Options.DbUser + ";Pwd=" + Options.DbPassword + ";CharSet=utf8; Connect Timeout=30;"; 

Tengo una clase estática que administra cosas relacionadas con la base de datos, y allí tengo una conexión privada.

 private static MySqlConnection _connection; public static MySqlConnection Connection { get { if (_connection.State != ConnectionState.Open) _connection.Open(); return _connection; } set { _connection = value; } } 

Este es el método que inicializa la conexión:

 public static bool Init(string cs) { _connection = new MySqlConnection(cs); MySqlCommand command = new MySqlCommand("SET NAMES utf8", Connection); command.ExecuteNonQuery(); return true; } 

Este es el método donde obtengo una excepción:

 public static bool InsertRecord(MySqlCommand command) { command.Connection = Connection; if(command.ExecuteNonQuery() > 0) return true; return false; } 

command.ExecuteNonQuery() lanza una excepción: se encontró un error fatal durante la ejecución del comando.

Esta es la traza de stack …

 MySql.Data.MySqlClient.MySqlException was unhandled Message=Fatal error encountered during command execution. Source=MySql.Data ErrorCode=-2147467259 Number=0 StackTrace: at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior) at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery() at CMS.Database.InsertRecord(MySqlCommand command) in C:\_myStuff\VS2010\CMS\CMS\Database.cs:line 95 at CMS.frmAddItem.btnDo_Click(Object sender, EventArgs e) in C:\_myStuff\VS2010\CMS\CMS\frmAddItem.cs:line 138 at System.Windows.Forms.Control.OnClick(EventArgs e) at System.Windows.Forms.Button.OnClick(EventArgs e) at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent) at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks) at System.Windows.Forms.Control.WndProc(Message& m) at System.Windows.Forms.ButtonBase.WndProc(Message& m) at System.Windows.Forms.Button.WndProc(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m) at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam) at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg) at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData) at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context) at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context) at System.Windows.Forms.Application.RunDialog(Form form) at System.Windows.Forms.Form.ShowDialog(IWin32Window owner) at System.Windows.Forms.Form.ShowDialog() at CMS.frmMain.btnNovi_Click(Object sender, EventArgs e) in C:\_myStuff\VS2010\CMS\CMS\frmMain.cs:line 381 at System.Windows.Forms.Control.OnClick(EventArgs e) at System.Windows.Forms.Button.OnClick(EventArgs e) at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent) at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks) at System.Windows.Forms.Control.WndProc(Message& m) at System.Windows.Forms.ButtonBase.WndProc(Message& m) at System.Windows.Forms.Button.WndProc(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m) at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam) at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg) at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData) at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context) at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context) at System.Windows.Forms.Application.RunDialog(Form form) at System.Windows.Forms.Form.ShowDialog(IWin32Window owner) at System.Windows.Forms.Form.ShowDialog() at CMS.frmLogin.DoLogin() in C:\_myStuff\VS2010\CMS\CMS\frmLogin.cs:line 55 at CMS.frmLogin.button2_Click(Object sender, EventArgs e) in C:\_myStuff\VS2010\CMS\CMS\frmLogin.cs:line 31 at System.Windows.Forms.Control.OnClick(EventArgs e) at System.Windows.Forms.Button.OnClick(EventArgs e) at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent) at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks) at System.Windows.Forms.Control.WndProc(Message& m) at System.Windows.Forms.ButtonBase.WndProc(Message& m) at System.Windows.Forms.Button.WndProc(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m) at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam) at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg) at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData) at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context) at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context) at System.Windows.Forms.Application.Run(Form mainForm) at CMS.Program.Main() in C:\_myStuff\VS2010\CMS\CMS\Program.cs:line 18 at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args) at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args) at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly() at System.Threading.ThreadHelper.ThreadStart_Context(Object state) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state) at System.Threading.ThreadHelper.ThreadStart() InnerException: MySql.Data.MySqlClient.MySqlException Message=Fatal error encountered attempting to read the resultset. Source=MySql.Data ErrorCode=-2147467259 Number=0 StackTrace: at MySql.Data.MySqlClient.MySqlDataReader.NextResult() at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior) InnerException: MySql.Data.MySqlClient.MySqlException Message=Reading from the stream has failed. Source=MySql.Data ErrorCode=-2147467259 Number=0 StackTrace: 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, Boolean force) at MySql.Data.MySqlClient.MySqlDataReader.NextResult() InnerException: System.IO.EndOfStreamException Message=Attempted to read past the end of the stream. Source=MySql.Data StackTrace: at MySql.Data.MySqlClient.MySqlStream.ReadFully(Stream stream, Byte[] buffer, Int32 offset, Int32 count) at MySql.Data.MySqlClient.MySqlStream.LoadPacket() InnerException: 

¿Alguna sugerencia?

Me doy cuenta de que abres conexiones, pero no las cierras cuando terminas con ellas. Prefiero el enfoque de abrir conexiones cuando se necesitan, en lugar de abrirlas si aún no están abiertas. Pueden estar rancios.

Almacena en caché la cadena de conexión pero no la conexión en sí.

 public static string ConnectionString {get;set;} public static bool InsertRecord(sql) { bool success = false; using (var con = new Connection(ConnectionString)){ var command = new SqlCommand(sql,con); success = (command.ExecuteNonQuery() > 0); } return success; } 

Los recursos deben liberarse cuando ya no sean necesarios.

Le sugiero que revise este informe de error con respecto a este problema.

MySQL usa un montón de diferentes variables de tiempo de espera en diferentes niveles.

Cuando se está estableciendo la conexión -> connect_timeout

Cuando el servidor espera inactivo a que se envíe otra consulta -> wait_timeout

Si la consulta se está leyendo o el conjunto de resultados se está devolviendo -> net_read_timeout y net_write_timeout

Tanto net_write_timeout como net_read_timeout son variables de nivel de sesión, por lo que simplemente puede cambiarlas por conexiones cuando sepa que la consulta será problemática y, por lo tanto, no afectará al rest del servidor. (como una solución)

Pero primero, debe verificar el valor predeterminado para cada uno de estos valores de tiempo de espera en su servidor ejecutando algo como:

 show variables like '%timeout%' 

También debe mirar el comando que está insertando y ver si se puede simplificar o desglosar las actualizaciones más pequeñas.

Agregar MySqlDataAdapter.SelectCommand.CommandTimeout = 600 funcionó para mí. Aquí está el enlace donde lo encontré:

https://www.codeproject.com/Questions/665556/Connection-Timeout-Problem-with-MySqlDataAdapter-h