Lost Connection to MySQL server during query

Lost Connection to MySQL server during query

Postby chern.ivan » Fri 12 Sep 2008 13:19

I've got a select statement that should return little over 10,000 records with 10 columns. In MySQL query browser, it fetches this data in 0.1428seconds.

When I try this in vb.net, I keep getting an error message saying lost connection to MySql server during query. When I dig into details, I see errorCode -2147467259

I have a try catch block where I check to see if the mysqlconnection is closed, and if so, i open it and try the mysqldatatable("select statement", connection") assignment.

Error occurs when I make the mysqldatabale.active = true.

Just to be safe, I made the MaxConnectionAge = 300000, still same problem occurs.

Any thoughts?

Stack trace is pasted below.
StackTrace: " at CoreLab.MySql.a7.a(Byte[] A_0, Int32 A_1, Int32 A_2)
at CoreLab.MySql.a7.a()
at CoreLab.MySql.a7.b(Byte[] A_0)
at CoreLab.MySql.b.a(Byte[] A_0)
at CoreLab.MySql.b.a(Byte[] A_0, Boolean A_1)
at CoreLab.MySql.bf.f()
at CoreLab.MySql.x.b(Boolean A_0)
at CoreLab.MySql.MySqlDataReader.a(Boolean A_0)
at CoreLab.MySql.MySqlDataReader.Close()
at CoreLab.Common.DbDataTable.k()
at CoreLab.Common.DbDataTable.h()
at CoreLab.Common.DbDataTable.CancelFetch()
at CoreLab.Common.DbDataTable.a(Int32 A_0, Boolean A_1)
at CoreLab.Common.DbDataTable.b(Boolean A_0)
at CoreLab.Common.DbDataTable.c()
at CoreLab.Common.DbDataTable.Open()
at CoreLab.Common.DbDataTable.set_Active(Boolean value)
at ExchangeFeeEstimator.Form1.RepopulateTable() in C:\Documents and Settings\CANDCTrading\My Documents\Visual Studio 2005\Projects\ExchangeFeeEstimator\ExchangeFeeEstimator\Form1.vb:line 56
at ExchangeFeeEstimator.Form1.SimpleButton1_Click(Object sender, EventArgs e) in C:\Documents and Settings\CANDCTrading\My Documents\Visual Studio 2005\Projects\ExchangeFeeEstimator\ExchangeFeeEstimator\Form1.vb:line 134
at System.Windows.Forms.Control.OnClick(EventArgs e)
at DevExpress.XtraEditors.BaseButton.OnClick(EventArgs e)
at DevExpress.XtraEditors.BaseButton.OnMouseUp(MouseEventArgs e)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at DevExpress.Utils.Controls.ControlBase.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(Int32 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(ApplicationContext context)
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
at ExchangeFeeEstimator.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
at System.Runtime.Hosting.ManifestRunner.Run(Boolean checkAptModel)
at System.Runtime.Hosting.ManifestRunner.ExecuteAsAssembly()
at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext, String[] activationCustomData)
at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssemblyDebugInZone()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()"
chern.ivan
 
Posts: 2
Joined: Wed 09 Jul 2008 13:14

Postby Shalex » Mon 15 Sep 2008 11:43

We could not reproduce the problem.
What MyDirect .NET version and edition are you using?
What OS do you have on the server?
What MySQL Server do you use?
Could you send a small test project to reproduce the problem?
Shalex
Devart Team
 
Posts: 7698
Joined: Thu 14 Aug 2008 12:44

Postby trygvelo » Wed 27 May 2009 07:48

Same problem here. It is not easy to reproduce because it works fine for a while, then this starts happening after a lot of requests towards the database. The same query works fine in MySQL Query Browser. I have used a .net typed dataset with a TableAdapter and let the CoreLab generated dataset code handle all the query execution, opening/closing of connections etc. The query returns ~16000 rows in 0.3 secs from a single table.

It used to work fine when I had a DataAdapter in my component code and called the Fill method "manually". That's the only change from the last version of the affected software.

I'm currently using CoreLab 4.70, should have upgraded to cure another bug, but I'm afraid to do it since I'll probably run into new bugs with a new version. At least that's been the case every time I've upgraded in the past.

Same stacktrace as thread starter too:

CoreLab.MySql.MySqlException: Lost connection to MySQL server during query
at CoreLab.MySql.a9.a(Byte[] A_0, Int32 A_1, Int32 A_2)
at CoreLab.MySql.a9.a()
at CoreLab.MySql.a9.b(Byte[] A_0)
at CoreLab.MySql.c.a(Byte[] A_0)
at CoreLab.MySql.c.a(Byte[] A_0, Boolean A_1)
at CoreLab.MySql.bi.f()
at CoreLab.MySql.z.b(Boolean A_0)
at CoreLab.MySql.MySqlDataReader.a(Boolean A_0)
at CoreLab.MySql.MySqlDataReader.Close()
at CoreLab.Common.DbDataTable.l()
at CoreLab.Common.DbDataTable.i()
at CoreLab.Common.DbDataTable.CancelFetch()
at CoreLab.Common.DbDataTable.a(Int32 A_0, Boolean A_1)
at CoreLab.Common.DbDataTable.b(Boolean A_0)
at CoreLab.Common.DbDataTable.Fill()
at CoreLab.Common.DbDataTable.Fill(Object[] parameterValues)
at ProTeria.DB.AESHandler.getAES_CodelistsNew(String countrycode)
trygvelo
 
Posts: 13
Joined: Wed 19 Jan 2005 10:53

Postby Shalex » Wed 27 May 2009 09:56

We recommend you to upgrade to the latest version (5.20.33) of dotConnect for MySQL.
We have added the DefaultCommandTimeout property to the MySqlConnection class.
So in this way you can assign the CommandTimeout property to all MySqlCommand objects that will be used by your DataSet.
Increase the default value (30 seconds) of the DefaultCommandTimeout property or set it to 0.

Please check the wait_timeout variable of your MySQL server. Try increasing it.
Shalex
Devart Team
 
Posts: 7698
Joined: Thu 14 Aug 2008 12:44

Postby trygvelo » Wed 27 May 2009 10:24

A restart of MySQL temporarily fixes the issue. It will resurface after 8-24 hours. There must be some issues with the connections in CoreLab connection pool.

How do I best close the connections of a TableAdapter (TableAdapter IN dataset, not a standalone DataAdaptor) or will the TableAdapter connection be closed automatically after it has been used?

I'm currently doing this:

Code: Select all
DATASETS.dsStatusLogicTableAdapters.statuslogicTableAdapter daSL = new DATASETS.dsStatusLogicTableAdapters.statuslogicTableAdapter();
try
{
    daSL.Fill(dsSL.statuslogic);
}
finally
{
    daSL.Connection.Close();
}
using (daSL.Connection)
{
    daSL.Fill(dsSL.statuslogic);
}



Can I use the "using" statement instead on either the TableAdapter.Connection OR on the TableAdapter object itself?

Code: Select all
DATASETS.dsStatusLogicTableAdapters.statuslogicTableAdapter daSL = new DATASETS.dsStatusLogicTableAdapters.statuslogicTableAdapter();

using (daSL.Connection)
{
    daSL.Fill(dsSL.statuslogic);
}


OR

Code: Select all
using (DATASETS.dsStatusLogicTableAdapters.statuslogicTableAdapter daSL = new DATASETS.dsStatusLogicTableAdapters.statuslogicTableAdapter())
{
    daSL.Fill(dsSL.statuslogic);
}
trygvelo
 
Posts: 13
Joined: Wed 19 Jan 2005 10:53

Postby Shalex » Wed 27 May 2009 14:30

Please use the first (try...finally) or the second (using(conn){...}) code snippet to control the connection object.
Shalex
Devart Team
 
Posts: 7698
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for MySQL