Page 1 of 1
Lost connection
Posted: Sat 04 Sep 2010 19:30
by Idsa
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
Posted: Mon 06 Sep 2010 16:37
by AndreyR
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?
Posted: Tue 07 Sep 2010 04:02
by Idsa
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).
Posted: Tue 07 Sep 2010 09:46
by AndreyR
I have sent you the cleaned solution (without .exe files).
Posted: Wed 08 Sep 2010 17:48
by Idsa
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
Posted: Fri 10 Sep 2010 18:16
by Idsa
Have you succeeded in "lost connection" problem reproducing?
Posted: Mon 13 Sep 2010 12:13
by AndreyR
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.
Posted: Tue 14 Sep 2010 20:05
by Idsa
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
Posted: Tue 21 Sep 2010 10:57
by AndreyR
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).
Posted: Thu 23 Sep 2010 04:02
by Idsa
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/Onlin ... 0level.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"?
Posted: Wed 29 Sep 2010 08:26
by AndreyR
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.
Posted: Wed 29 Sep 2010 08:39
by Idsa
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?
Posted: Wed 29 Sep 2010 11:15
by AndreyR
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.
Posted: Thu 16 Dec 2010 16:28
by AndreyR
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.