Lost connection to MySQL server during query

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for MySQL
Post Reply
wtftc
Posts: 10
Joined: Mon 26 Jul 2010 04:10

Lost connection to MySQL server during query

Post by wtftc » Mon 26 Jul 2010 04:32

I am losing connection to MySQL on some queries.

I am running a Windows box and when I connect to my local box running MySQL, I don't get the connection lost issue. When I run my client on Windows and connect to an Ubuntu 9.1 server running MySqL, I get the connection dropping sometimes. When I run the test using Mono directly on the server connecting to the local MySQL server, I almost always get the connection drop.

To summarize:
Windows client -> Windows MySQL: no drops
Windows client -> Ubuntu MySQL: sometimes drops
Ubuntu mono client -> Ubuntu MySQL: almost always drops.

This is the error:

Code: Select all

Devart.Data.Linq.LinqCommandExecutionException: Error on executing DbCommand. ---> Lost connection to MySQL server during query
  --- End of inner exception stack trace ---
  at Devart.Data.Linq.LinqCommandExecutionException.a (System.String A_0, System.Exception A_1) [0x00000] in :0
  at Devart.Data.Linq.Provider.DataProvider.ExecuteQuery (Devart.Data.Linq.Provider.CompiledQuery compiledQuery, System.Object[] parentArgs, System.Object[] userArgs, System.Object lastResult) [0x00000] in :0

Unhandled Exception: Devart.Data.Linq.LinqCommandExecutionException: Error on executing DbCommand. ---> Lost connection to MySQL server during query
  --- End of inner exception stack trace ---
  at Devart.Data.Linq.LinqCommandExecutionException.a (System.String A_0, System.Exception A_1) [0x00000] in :0
  at Devart.Data.Linq.Provider.DataProvider.ExecuteQuery (Devart.Data.Linq.Provider.CompiledQuery compiledQuery, System.Object[] parentArgs, System.Object[] userArgs, System.Object lastResult) [0x00000] in :0
I managed to get Visual Studio to break on first exception and it was a SocketException whose message was: "A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond"

My ConnectionString is:
User Id=DbUser;Password=DbPass;Host=DbServer;Database=DbTest;Persist Security Info=True; Connection Timeout=360; Default Command Timeout=0

The error is happening in less than 90 seconds, so the Connection Timeout isn't having the desired effect.

On examining MySQL's logs, I see that although the first connection is being lost, dotConnect somehow manages to establish another connection and run a single query before the whole process gives up. Here is a sample:

Code: Select all

                   40 Query     INSERT INTO ...
                   40 Query     INSERT INTO ...
                   40 Query     INSERT INTO ...
                   40 Query     INSERT INTO ...
100725 20:00:37    41 Connect   DbUser@localhost on DbTest
                   41 Query     SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
                   41 Query     XA BEGIN '8588ecda-934b-b7ee-eb20-1bc6de9485ab,-8145-29f5d7b06f1a'
                   41 Query     SELECT ...
100725 20:01:07    40 Query     XA END '8588ecda-934b-b7ee-eb20-1bc6de9485ab,-a9d3-bff69127202c'
                   40 Query     XA ROLLBACK '8588ecda-934b-b7ee-eb20-1bc6de9485ab,-a9d3-bff69127202c'
                   41 Query     XA END '8588ecda-934b-b7ee-eb20-1bc6de9485ab,-8145-29f5d7b06f1a'
                   41 Query     XA ROLLBACK '8588ecda-934b-b7ee-eb20-1bc6de9485ab,-8145-29f5d7b06f1a'

The stack trace looked like this (I can provide the full unedited call stack separately if it will help, but I had to anonymize it for the forum):

Code: Select all

 	System.dll!System.Net.Sockets.NetworkStream.Read(byte[] buffer, int offset, int size) + 0xe8 bytes	
 	Devart.Data.MySql.dll!Devart.Common.a.b(byte[] A_0 = {byte[16384]}, int A_1 = 0, int A_2 = 16384) + 0xbe bytes	
 	Devart.Data.MySql.dll!Devart.Common.au.c(byte[] A_0 = {byte[4]}, int A_1 = 0, int A_2 = 4) + 0x244 bytes	
 	Devart.Data.MySql.dll!Devart.Common.t.e(byte[] A_0 = {byte[4]}, int A_1 = 0, int A_2 = 4) + 0xa9 bytes	
 	Devart.Data.MySql.dll!Devart.Data.MySql.bk.a(byte[] A_0 = {byte[4]}, int A_1 = 0, int A_2 = 4) + 0x897 bytes	
 	Devart.Data.MySql.dll!Devart.Data.MySql.bk.a() + 0x56 bytes	
 	Devart.Data.MySql.dll!Devart.Data.MySql.bk.d() + 0x8c bytes	
 	Devart.Data.MySql.dll!Devart.Data.MySql.v.a(out Devart.Data.MySql.ah[] A_0 = null, out int A_1 = 0) + 0x65 bytes	
 	Devart.Data.MySql.dll!Devart.Data.MySql.v.a(byte[] A_0 = {byte[512]}, int A_1 = 300, bool A_2 = false) + 0xbc bytes	
 	Devart.Data.MySql.dll!Devart.Data.MySql.a2.e() + 0x13b bytes	
 	Devart.Data.MySql.dll!Devart.Data.MySql.a2.o() + 0x6c bytes	
 	Devart.Data.MySql.dll!Devart.Data.MySql.MySqlCommand.InternalExecute(System.Data.CommandBehavior behavior = Default, System.IDisposable stmt = {Devart.Data.MySql.a2}, int startRecord = 0, int maxRecords = 0) + 0x3a1 bytes	
 	Devart.Data.dll!Devart.Common.DbCommandBase.InternalExecute(System.Data.CommandBehavior behavior = Default, System.IDisposable stmt = {Devart.Data.MySql.a2}, int startRecord = 0, int maxRecords = 0, bool nonQuery = false) + 0x5c bytes	
 	Devart.Data.dll!Devart.Common.DbCommandBase.ExecuteDbDataReader(System.Data.CommandBehavior behavior = Default, bool nonQuery = false) + 0x1f5 bytes	
 	Devart.Data.dll!Devart.Common.DbCommandBase.ExecuteDbDataReader(System.Data.CommandBehavior behavior = Default) + 0x34 bytes	
 	System.Data.dll!System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader() + 0x15 bytes	
 	Devart.Data.Linq.dll!Devart.Data.Linq.Provider.DataProvider.ExecuteQuery(string commandText = "UPDATE ...", System.Collections.Generic.List resultsetParameters = null, System.Collections.Generic.IList parameters = Count = 10, bool isBatch = false, out System.Data.IDbCommand dbCommand = {Devart.Data.MySql.MySqlCommand}) + 0x49c bytes	
 	Devart.Data.Linq.dll!Devart.Data.Linq.Provider.DataProvider.ExecuteQuery(string commandText = "UPDATE ...", System.Collections.Generic.IList parameters = Count = 10, bool isBatch = false, out System.Data.IDbCommand dbCommand = {Devart.Data.MySql.MySqlCommand}) + 0x58 bytes	
 	Devart.Data.Linq.dll!Devart.Data.Linq.h.a(Devart.Data.Linq.SubmitCommand A_0 = {Devart.Data.Linq.SubmitCommand}, Devart.Data.Linq.SubmitCommandBuilder A_1 = {Devart.Data.MySql.Linq.e}) + 0xc5 bytes	
 	Devart.Data.Linq.dll!Devart.Data.Linq.h.a(Devart.Data.Linq.SubmitedObject A_0 = {Devart.Data.Linq.SubmitedObject}, Devart.Data.Linq.SubmitCommand A_1 = {Devart.Data.Linq.SubmitCommand}, Devart.Data.Linq.SubmitCommandBuilder A_2 = {Devart.Data.MySql.Linq.e}) + 0x42 bytes	
 	Devart.Data.Linq.dll!Devart.Data.Linq.q.a(Devart.Data.Linq.SubmitedObject A_0 = {Devart.Data.Linq.SubmitedObject}, Devart.Data.Linq.SubmitCommand A_1 = {Devart.Data.Linq.SubmitCommand}, Devart.Data.Linq.SubmitCommandBuilder A_2 = {Devart.Data.MySql.Linq.e}) + 0xeb bytes	
 	Devart.Data.Linq.dll!Devart.Data.Linq.r.a(Devart.Data.Linq.k A_0 = {Devart.Data.Linq.k}, Devart.Data.Linq.ac A_1 = {Devart.Data.Linq.EntityEntry}, object A_2 = {Context.LkIssue}, bool A_3 = true) + 0x670 bytes	
 	Devart.Data.Linq.dll!Devart.Data.Linq.y.a(Devart.Data.Linq.r A_0 = {Devart.Data.Linq.r}, Devart.Data.Linq.ac A_1 = {Devart.Data.Linq.EntityEntry}, bool A_2 = true) + 0x109 bytes	
 	Devart.Data.Linq.dll!Devart.Data.Linq.y.a(Devart.Data.Linq.DataContext A_0 = {Context.DataContext}, System.Data.Linq.ConflictMode A_1 = ContinueOnConflict) + 0x3d4 bytes	
 	Devart.Data.Linq.dll!Devart.Data.Linq.y.b(Devart.Data.Linq.DataContext A_0 = {Context.DataContext}, System.Data.Linq.ConflictMode A_1 = ContinueOnConflict) + 0x4b bytes	
 	Devart.Data.Linq.dll!Devart.Data.Linq.DataContext.SubmitChanges(System.Data.Linq.ConflictMode failureMode = ContinueOnConflict) + 0x2d3 bytes	
	
This is a semi-separate issue, but under Mono, the IsolationLevel isn't being handled properly. I am using IsolationLevel.ReadUncommitted, and Windows -> Windows and Windows -> Ubuntu use it correctly, but Ubuntu Mono -> Ubuntu somehow converts it to IsolationLevel.Serializable, according to MySQL's logs.

Am I doing something wrong, or how can I avoid this error?

Thanks,
bill

wtftc
Posts: 10
Joined: Mon 26 Jul 2010 04:10

Post by wtftc » Wed 28 Jul 2010 13:35

Hi, do you guys have any ideas here? This is still a point of trouble for me.

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Thu 29 Jul 2010 13:51

Could you please send me a small test project illustrating the problem with LINQ code?
We are investigating the situation with the ReadUncommitted isolation level in MySQL.

wtftc
Posts: 10
Joined: Mon 26 Jul 2010 04:10

Post by wtftc » Thu 29 Jul 2010 14:33

Andrey, could you provide me an e-mail where I could forward the test project?

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Thu 29 Jul 2010 15:14

Try support * devart * com.

Post Reply