Lost connection to MySQL server during query

[[email protected]]

Hello

I am using dotConnect for MySQL in my ASP.NET application. When loading a webpage, I am performing some SQL LINQ statements. If I wait a very long time and make a refresh of the site, dotConnect generates the following exception if it tries to execute the SQL LINQ statements again:

Lost connection to MySQL server during query.

Why does this happen? How can I prevent this? I guess somehow the application still holds a reference to the connection which has been timed out. Shouldn't dotConnect open a new connection in this case?

Thanks and regards
________

[Shalex]

The reason of this problem can be the following. The connection is opened in your code and remains in this state a long time without usage, and is not closed after the usage. The MySQL server has the wait_timeout variable that contains the time limit for holding inactive connections to the server in the opened state; when the time is over, the connection is forcibly closed by the server.

If you use the Devart LINQ to MySQL support via its interface, the connection object state becomes closed after every interoperation with the MySQL server. But if you use it with some interference in its implementation (e.g., using the internal MySqlConnection object and opening it manually) or create your own MySqlConnection object and do not close it after the usage, this error may occur. Or maybe you open and do not close the reader somewhere in your code. We cannot identify the problem at the moment. Please review your code.

If the problem persists, please provide us with the following information:
1. the value of the wait_timeout variable at your MySQL server;
2. the call stack with our components when the problem occurs;
3. if it is possible, please create a small test application to reproduce the problem, and describe the steps we should follow to obtain the mentioned error. Please include definitions of the database objects and avoid using third-party components.

[[email protected]]

Hello

1. The value of wait_timeout is 28800
2. It seems, the error occurs in the DbSessionStateStore. I forgot to mention, that the sessions are stored in the MySQL db through your session state provider:

Code: Select all

[MySqlException (0x80004005): Lost connection to MySQL server during query]
   Devart.Common.Web.Providers.DbSessionStateStore.a(Boolean A_0, HttpContext A_1, String A_2, Boolean& A_3, TimeSpan& A_4, Object& A_5, SessionStateActions& A_6) +3114
   Devart.Common.Web.Providers.DbSessionStateStore.GetItemExclusive(HttpContext context, String id, Boolean& locked, TimeSpan& lockAge, Object& lockId, SessionStateActions& actionFlags) +57
   System.Web.SessionState.SessionStateModule.GetSessionStateItem() +117
   System.Web.SessionState.SessionStateModule.BeginAcquireState(Object source, EventArgs e, AsyncCallback cb, Object extraData) +487
   System.Web.AsyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +66
   System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +155

Do you think, this already helps to identify the problem? By the way... I don't use an own implementation of your MySqlConnection object. I just use the "Devart.Data.Linq.DataContext(string connectionString)" overload when working with LINQ.

Best regards

[Shalex]

1.

If I wait a very long time and make a refresh of the site, dotConnect generates the following exception...

Please specify the exact timeframe when the exception arises.

2. Please specify the connection string that is used by the Session State provider (it is specified in the web.config file of your application). Try setting the Pooling parameter to false - "Pooling=false;". Does it help?

[[email protected]]

Hello

We tried to disable pooling. Indeed this helped. But performance was so bad than, that we had to enable it again. The connection string of the SessionStateProvider looks as follows:

Code: Select all

Server=xyz;Port=3306;Database=xyz;User=xyz;Password=xyz;Persist Security Info=false;Unicode=true;Pooling=true;Min Pool Size=0;Max Pool Size=1000;Connection Lifetime=0;Connection Timeout=60;Compress=false;Protocol=tcp;Direct=true;Embedded=false

We are running over 100 databases. We get stack traces like this every day:

Lost connection to MySQL during query
at Devart.Data.MySql.ba.d(Exception A_0)
at Devart.Common.u.e(Byte[] A_0, Int32 A_1, Int32 A_2)
at Devart.Data.MySql.bd.d(Byte[] A_0, Int32 A_1, Int32 A_2)
at Devart.Data.MySql.bd.l()
at Devart.Data.MySql.s.a(g A_0, Byte[] A_1, Int32 A_2, Boolean A_3)
at Devart.Data.MySql.s.a(Byte[] A_0, Int32 A_1, Boolean A_2)
at Devart.Data.MySql.av.e()
at Devart.Data.MySql.MySqlCommand.a(CommandBehavior A_0, IDisposable A_1, Int32 A_2, Int32 A_3)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
at Devart.Common.DbCommandBase.ExecuteNonQuery()
at Devart.Data.MySql.MySqlConnection.i()
at Devart.Data.MySql.MySqlConnection.Open()
at Devart.Data.Linq.Provider.ConnectionManager.GetOpenConnection()
at Devart.Data.Linq.Provider.ConnectionManager.b(IConnectionUser A_0)
at Devart.Data.Linq.Provider.DataProvider.a(d A_0, f A_1, Object[] A_2, Object[] A_3, Object A_4)
at Devart.Data.Linq.Provider.DataProvider.a(d A_0, Object[] A_1)
at Devart.Data.Linq.DataQuery`1.i()

We urgently need your help to track down this problem.

Best regards
Thomas
________

[[email protected]]

Is there anything else we can do to help you to find the source of this bug as fast as possible?

[Shalex]

1. Please try the latest 5.40.42 version of dotConnect for MySQL. Does the problem persist with the 5.40.42 version as well?

2. Have you tried to increase the Default Command Timeout property of the MySqlConnection object you are using?

3. Please give us the following information to reproduce the error:
a) post here the whole section from your web.config;
b) the timeframe between requests before the application generates the error (how much time should application be undisturb?);
c) give us a piece of code that generates the error. A small test project will be appreciated.

[[email protected]]

Please use the following sample to reproduce this behaviour. Please also note the TODOs.

Code: Select all

using System;
using System.Collections.Generic;
using System.Data.Linq;
using System.Linq;
using System.Linq.Expressions;
using Devart.Data.Linq;
using System.Threading;

namespace DevartTest
{
	class Program
	{
		static void Main(string[] args)
		{
			// TODO:
			// - Set the wait_timeout of the MySql instance to 20 seconds
			// - Adjust the connection string
			// - Create a DataContext from any table and use this table for the query

			Console.WriteLine("Creates a connection on the MySql instance");
			Run();

			Console.WriteLine("Waits 10 seconds");
			Thread.Sleep(10*1000);

			Console.WriteLine("Reuses the connection on the MySql instance and resets its idle time back to 0");
			Run();

			Console.WriteLine("Waits 25 seconds until the connection gets killed on the MySql server");
			Thread.Sleep(25 * 1000);

			Console.WriteLine("Tries to reuse the connection on the MySql instance (instead of creating a new one) which throws a \"Lost connection to MySQL server during query\" error");
			Run();

			Thread.Sleep(60 * 1000);
		}

		static void Run()
		{
			try
			{
				DB db = new DB("Server=MyServer;Port=3306;Database=MyDatabase;User=MyUser;Password=MyPassword;Persist Security Info=false;Unicode=true;Pooling=true;Min Pool Size=0;Max Pool Size=100;Connection Lifetime=0;Connection Timeout=15;Compress=false;Protocol=tcp;Direct=true;Embedded=false");
				db.DeferredLoadingEnabled = false;

				var records = (from t in db.MyTable
							 select t).ToList();
			}
			catch (Exception ex)
			{
				Console.WriteLine();
				Console.WriteLine(ex.ToString());
			}
		}
	}
}

________

[[email protected]]

Hi

Were you able to reproduce this bug? We urgently need a solution. Thank you very much for your help.

Best regards
________

[[email protected]]

Hi

Do you need some more information from my side to accelerate the problem analysis? Please let me know.

Best regards
________

[Shalex]

Currently we don't validate connection when retrieving it from pool to increase performance, because pooling manager validates connections in pool every 60 seconds itself. We will investigate the situation when the wait_timeout variable of the server is less than 60 seconds. We see 2 possible ways: either implementing connection validation (ping) each time it is retrieved from pool, or implementing the additional connection string parameter to involve connection validation only if this parameter is set. As a temporary workaround, please rewrite your Run() method in the following way:

Code: Select all

      static void Run()
      {
         try
         {
            DB db = new DB("Server=MyServer;Port=3306;Database=MyDatabase;User=MyUser;Password=MyPassword;Persist Security Info=false;Unicode=true;Pooling=true;Min Pool Size=0;Max Pool Size=100;Connection Lifetime=0;Connection Timeout=15;Compress=false;Protocol=tcp;Direct=true;Embedded=false");

            try {
              db.Connection.Open();
              //Ping() returns false if connection is not valid
              while (!((MySqlConnection)db.Connection).Ping()) {
                db.Connection.Close();
                db.Connection.Open();
              }
            }
            finally {
              db.Connection.Close();
            }

            db.DeferredLoadingEnabled = false;

            var records = (from t in db.MyTable
                      select t).ToList();
         }
         catch (Exception ex)
         {
            Console.WriteLine();
            Console.WriteLine(ex.ToString());
         }
      }

[[email protected]]

Hello

Thank you for this workaround. Indeed this will decrease the performance.

You say, the pooling manager validates the connections in the pool every 60 seconds itself. Are you sure this is working? In our live environment we have a wait_timeout of 28800 (default). I just decreased this value to quickly reproduce the "lost connection to mysql server" error.

Do you think I just created a "new" error with my test but did not reproduce the same error we experience in our live environment?

Regards
________

[Shalex]

We make the pooling manager to validate connections in the pool every 60 seconds in a new thread by our code. But in live environment this time interval can be bigger, e.g. CPU is busy, and validates connections later (in our test environment it validates them approximately every 80 seconds). If the error occurs intermittently, I think, your application experiences the same issue, because the following situation is possible theoretically: 28800 seconds (8 hours) is over, and the connection is not valid already, but it is not checked by the pooling manager yet after becoming invalid. The application tries reusing this connection, and the error occurs.

We will notify you about our implementation of an additional connection pooling validation (see my previous post).

[Shalex]

We have made a deeper investigation of the pooling connections validation. Pooling manager calls Ping() every 60 (or several more) seconds, so idle time of a connection in the pool is reset to zero every 60 seceonds. So, if your wait_timeout contains the 28800 value, the reason of the problem you are experiencing is different. We have decided not to implement an additional connection pooling validation, because this will reduce performance essentially, and the settings, when the value of wait_timeout is less than 60 seconds, is not usually used in the deployment environments.

If you pass the connection string to the DataConext constructor, our internal implementation closes the created connection object after every usage. If you pass your connection object to DataContext, we don't change its state after using it: if a connection is passed in the closed state, it remains in the closed state after it was used by DataContext; but if a connection is passed in the opened state, it will be in the opened state after it was used by DataContext.

If I wait a very long time and make a refresh of the site, dotConnect generates the following exception...

Please specify the exact timeframe your application should be undisturbed to get the mentioned error. Is it more than 28800 seconds (8 hours)?

[tduong]

Hi,

We have also encountered this problem recently. Quite some time was spent before coming to the conclusion that this is an issue with the connection being returned from the pool.

Our desktop application uses two different connections - different meaning the strings are not identical, and are thus in separate pools.

Stripping away the details of what these connections strings are used for, our application starts, performs a set of steps, then exits.

Our app uses two connections:

Connection string 1
Connection string 2

Step 1 - Instantiates a MySqlConnection using connection string 1, and closes it. This step is relatively fast
Step 2 - Instantiates a MySqlConnection using connection string 2, and closes it. This step can take several hours. For the particular exception that we encountered, this step took > 8 hours, which is (surprise) the default wait_timeout value as set on the msyql server.
Step 3 - Creates a new MySqlConnection using connection string 1.

Exception occurs in step 3 when step 2 > wait_timeout. The server terminates connection 1, but the pool still returns this connection. You can even call "MySqlConnection.Open()" successfully, but when a command is executed, we get the infamous "lost connection to MySql server during query".

We thought about creating a wrapper around the MySqlConnection instantiation, and to perform validation of the connection ourself, but this seems like it is something that should be done by the connection pool manager that distributes these Connection objects. Another alternative would be for us to invalidate the pool after Step2, but, as you said before, there is a 30 second wait time out.

It would be nice if the underlying code returned a *good* connection.

Are there any future plans to do this?

Thanks