C＃与MySQL通过Connector / NET
我正在使用C＃（4.0 Framework）开发CMS应用程序，它通过MySQL Connector（6.5.4）连接到远程服务器上的MySQL数据库（5.0.95）。
我在执行查询时遇到问题。
例如我的连接字符串：
"Server=" + Options.DbServer + ";Database="+ Options.Database +";Uid=" + Options.DbUser + ";Pwd=" + Options.DbPassword + ";CharSet=utf8; Connect Timeout=30;";
我有管理数据库相关的东西的静态类，并在那里我有私人成员_connection。
private static MySqlConnection _connection;
public static MySqlConnection Connection
{
get
{
if (_connection.State != ConnectionState.Open)
_connection.Open();
return _connection;
}
set { _connection = value; }
}
这是初始化连接的方法：
public static bool Init(string cs)
{
_connection = new MySqlConnection(cs);
MySqlCommand command = new MySqlCommand("SET NAMES utf8", Connection);
command.ExecuteNonQuery();
return true;
}
这是我得到例外的方法：
public static bool InsertRecord(MySqlCommand command)
{
command.Connection = Connection;
if(command.ExecuteNonQuery() > 0)
return true;
return false;
}
command.ExecuteNonQuery()抛出异常：命令执行期间遇到致命错误。
这是堆栈跟踪...
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:_myStuffVS2010CMSCMSDatabase.cs:line 95
at CMS.frmAddItem.btnDo_Click(Object sender, EventArgs e) in C:_myStuffVS2010CMSCMSfrmAddItem.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:_myStuffVS2010CMSCMSfrmMain.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:_myStuffVS2010CMSCMSfrmLogin.cs:line 55
at CMS.frmLogin.button2_Click(Object sender, EventArgs e) in C:_myStuffVS2010CMSCMSfrmLogin.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:_myStuffVS2010CMSCMSProgram.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:
有什么建议么？
我注意到你打开了连接，但是当你完成这些连接时你不会关闭它们。 我更喜欢在需要时打开连接的方法，而不是在尚未打开的情况下打开连接。 它们可能是陈旧的。
缓存连接字符串，但不是连接本身。
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;
}
资源不再需要时应该被释放。
我建议你检查这个问题报告。
MySQL在不同的级别使用一堆不同的超时变量。
连接建立时 - > connect_timeout
当服务器等待空闲时发送另一个查询 - > wait_timeout
如果正在读取查询或返回结果集 - > net_read_timeout和net_write_timeout
net_write_timeout和net_read_timeout都是会话级别的变量，因此只要知道查询会很麻烦并且不会影响服务器的其余部分，就可以简单地更改它们的每个连接。 （作为解决方法）
但首先，您应该通过执行如下操作来检查服务器上每个超时值的默认值：
show variables like '%timeout%'
你还应该看看你插入的命令，看看它是否可以简化，或者分解为更小的更新。
链接地址: http://www.djcxy.com/p/60971.html