C# with MySQL through Connector/NET

c# mysql mysql-connector

17,337

Solution 1

I notice that you open connections, but you don't close them when you are done with them. I prefer the approach of opening connections when they are needed, rather than possibly opening them if they are not already open. They may be stale.

Cache the connection string but not the connection itself.

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;
}

Resources should be freed when they are no longer required.

Solution 2

I suggest that you check this bug report regarding this issue.

Solution 3

MySQL uses a bunch of different timeout variables at different levels.

When connection is being established -> connect_timeout

When server waits idle for another query to be sent -> wait_timeout

If query is being read or result set is being sent back -> net_read_timeout and net_write_timeout

Both net_write_timeout and net_read_timeout are session level variables, so you could simply change them per connections when you know that the query will be troublesome and therefore it will not be affecting the rest of the server. (as a workaround)

But first, you should checks the default value for each of these timeout values on your server by executing something like :

show variables like '%timeout%'

You should also look at the command you're inserting and see if it can be simplified, or broken down intro smaller updates.

17,337

Author by

magic

Updated on June 11, 2022

Comments

magic almost 2 years

I'm developing CMS application in C#(4.0 Framework) which connects to MySQL database (5.0.95) on remote server by MySQL Connector (6.5.4).

I have problem with executing queries.

e.g. My connection string:

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

I have static class which manages database related stuff, and there I have private member _connection.

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

        return _connection;
    } 
    set { _connection = value; }
}

This is method which initializes connection:

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

This is method where I get exception:

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

    return false;
}

command.ExecuteNonQuery() throws exception: Fatal error encountered during command execution.

This is stack trace...

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:

Any suggestions?