C# with MySQL through Connector/NET
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.
eg 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:_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:
Any suggestions?
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.
我建议你检查这个问题报告。
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.
链接地址: http://www.djcxy.com/p/60972.html上一篇: 连接状态MySql连接器/ NET