Lost connection

Lost connection

Postby Idsa » Sat 04 Sep 2010 19:30

I'm trying to connect to remote MySQL server by last beta of dotConnector and get the following error. Any suggestions?

Devart.Data.MySql.MySqlException : Lost connection to MySQL server during query
----> Devart.Common.i : Transport channel is closed.
at Devart.Data.MySql.bh.d(Exception A_0)
at Devart.Common.t.d(Byte[] A_0, Int32 A_1, Int32 A_2)
at Devart.Data.MySql.bk.d(Byte[] A_0, Int32 A_1, Int32 A_2)
at Devart.Data.MySql.bk.b(Byte[] A_0, Int32 A_1)
at Devart.Data.MySql.bk.n()
at Devart.Data.MySql.w.a(h A_0, Byte[] A_1, Int32 A_2, Boolean A_3)
at Devart.Data.MySql.w.a(Byte[] A_0, Int32 A_1, Boolean A_2)
at Devart.Data.MySql.MySqlInternalConnection.f()
at Devart.Data.MySql.MySqlInternalConnection.Rollback()
at Devart.Common.q.a(Enlistment A_0)
at System.Transactions.VolatileEnlistmentAborting.EnterState(InternalEnlistment enlistment)
at System.Transactions.VolatileEnlistmentActive.InternalAborted(InternalEnlistment enlistment)
at System.Transactions.TransactionStateAborted.EnterState(InternalTransaction tx)
at System.Transactions.TransactionStateActive.Rollback(InternalTransaction tx, Exception e)
at System.Transactions.Transaction.Rollback()
at System.Transactions.TransactionScope.InternalDispose()
at System.Transactions.TransactionScope.Dispose()
at Repositories.Repositories.Categories.CategoriesRepository.AddCategory(Category category) in CategoriesRepository.cs: line 38
at Repositories.Tests.Repositories.Categories.CategoriesRepositoryTests.CreateCategory() in CategoriesRepositoryTests.cs: line 32
at Repositories.Tests.Repositories.Categories.CategoriesRepositoryTests.AddCategoryAndGetCategories_IntegrationTest() in CategoriesRepositoryTests.cs: line 4
Idsa
 
Posts: 22
Joined: Mon 30 Mar 2009 16:00

Postby AndreyR » Mon 06 Sep 2010 16:37

I have tried to reproduce the problem, but everything went ok in a simple example.
I have sent a test project to the mail address you have provided in your profile, could you please either modify it or create a new project reproducing the problem?
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby Idsa » Tue 07 Sep 2010 04:02

Hm... I didn't receive any message from you. Please try once more. Or I will create my own project in the evening (I just have no access to the database currently).
Idsa
 
Posts: 22
Joined: Mon 30 Mar 2009 16:00

Postby AndreyR » Tue 07 Sep 2010 09:46

I have sent you the cleaned solution (without .exe files).
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby Idsa » Wed 08 Sep 2010 17:48

AndreyR, thank you: I have received your test project. I have played with it and found one very strange thing. Let's assume I have a repository with two methods: AddEntity and GetEntitiesById. If I call only AddEntity or only GetEntitiesById, it works. But if I call both of them (independently in what order), I get "connection lost exception" at second method call.

Here is my repository code:

Code: Select all
   public class SitesRepository : ISitesRepository
   {
      public void AddSite(Site site)
      {
         using (var ctx = new ef_testEntities())
         {
            ctx.AddObject("Sites", site);

            ctx.SaveChanges();
         }
      }

      public IList GetSites()
      {
         using (var ctx = new ef_testEntities())
         {
            return ctx.Sites.OrderBy(s => s.title).ToList();
         }
      }
   }


Here is the detailed exception:
System.Data.UpdateException: An error occurred while updating the entries. See the InnerException for details. ---> Lost connection to MySQL server during query
--- End of inner exception stack trace ---
at System.Data.Mapping.Update.Internal.UpdateTranslator.Update(IEntityStateManager stateManager, IEntityAdapter adapter)
at System.Data.EntityClient.EntityAdapter.Update(IEntityStateManager entityCache)
at System.Data.Objects.ObjectContext.SaveChanges(Boolean acceptChangesDuringSave)
at System.Data.Objects.ObjectContext.SaveChanges()
at Repositories.Repositories.SitesRepository.AddSite(Site site) in C:\Users\Idsa\Downloads\MyEFTScope\MyEFTScope\SitesRepository.cs:line 18
Idsa
 
Posts: 22
Joined: Mon 30 Mar 2009 16:00

Postby Idsa » Fri 10 Sep 2010 18:16

Have you succeeded in "lost connection" problem reproducing?
Idsa
 
Posts: 22
Joined: Mon 30 Mar 2009 16:00

Postby AndreyR » Mon 13 Sep 2010 12:13

Yes, we have found the error, and we are investigating its reasons.
Thank you for the inquiry and sorry for the delay.
I will let you know as soon as it is fixed.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby Idsa » Tue 14 Sep 2010 20:05

Could you provide any (at least rough) estimation when this bug(?) is fixed. It is very important for me as I have a choice: 1. wait till this problem is resolved 2. move to PostgreSQL. This choice will be much easier with some kind of problem resolving estimation :)
Idsa
 
Posts: 22
Joined: Mon 30 Mar 2009 16:00

Postby AndreyR » Tue 21 Sep 2010 10:57

The issue is associated with the fact that default TransactionScope transaction has Serializable isolation level. In MySQL this isolation level means that every SELECT ... FROM ..." query is transformed to "SELECT ... FROM ... LOCK IN SHARE MODE", and this means that all indexed rows in the table returned by query are locked until either commit or rollback.
That's why everything hangs on insert - the insert command waits for the rows to be unlocked, and they just can't be before the insert command succeeds.
As a solution, you can either get only a subset of the table rows or use more severe isolation level (e.g., RepeatableRead).
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby Idsa » Thu 23 Sep 2010 04:02

AndreyR, so you tell it is not a bug but a feature, don't you?

That's why everything hangs on insert - the insert command waits for the rows to be unlocked, and they just can't be before the insert command succeeds.

I guess, it should be locked for other transactions, not for current. According to http://www.canaimasoft.com/f90sql/OnlineManual/Chapter05/Serializable%20isolation%20level.htm, "NOTE: Transaction isolation levels do not affect a transaction’s ability to see its own changes." And why does original MySQL provider work in this circumstances?

As a solution, you can either get only a subset of the table rows or use more severe isolation level (e.g., RepeatableRead).

Did you mean "less severe"?
Idsa
 
Posts: 22
Joined: Mon 30 Mar 2009 16:00

Postby AndreyR » Wed 29 Sep 2010 08:26

Yes, I meant "less severe". I have succeeded in executing your code using both RepeatableRead and ReadCommitted isolation levels.
As for the original MySQL provider, there is a difference in our implementations of TransactionScope support.
Devart provider opens a distributed transaction with two-phase commit.
MySQL provider imitates the distributed transaction having actually a local transaction with one-phase commit.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby Idsa » Wed 29 Sep 2010 08:39

So it is by design and won't be fixed, isn't it? It is very dissapointing to have no full Serializable IsolationLevel support for distributed transactions.

Are there any advantages of your approach comparing to original MySQL provider?
Idsa
 
Posts: 22
Joined: Mon 30 Mar 2009 16:00

Postby AndreyR » Wed 29 Sep 2010 11:15

Yes, at the moment it is a designed behaviour. However, we will investigate the possibility to add one-phase commit support for convenience.
As for the advantages, here is an example.
Let one has two different contexts and wants their data to be updated in a transactional way. Our provider will succeed in this task - data will be either updated during commit, or rolled back in both cases.
MySQL will fail with the "Multiple simultaneous connections or connections with different connection strings inside the same transaction are not currently supported." error.
The typical use case of TransactionScope is work with two different connections (or two different database sessions). Connector /NET has two external connections in the above example, so far so good. But internally it has one session for these connections.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby AndreyR » Thu 16 Dec 2010 16:28

We have implemented the one-phase commit functionality. It is available in the latest build. Just add "Transaction Scope Local=true;" to your connection string to turn it on.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16


Return to dotConnect for MySQL