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
Lost connection
Lost connection
I'm trying to connect to remote MySQL server by last beta of dotConnector and get the following error. Any suggestions?
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:
Here is the detailed exception:
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();
}
}
}
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
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).
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, so you tell it is not a bug but a feature, don't you?
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?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.
Did you mean "less severe"?As a solution, you can either get only a subset of the table rows or use more severe isolation level (e.g., RepeatableRead).
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.
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.
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.
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.