MySQL Bugs: #37283: Distributed Transactions (XA,MS DTC, ...)

Bug #37283	Distributed Transactions (XA,MS DTC, ...)
Submitted:	9 Jun 2008 14:29	Modified:	8 Apr 2009 8:55
Reporter:	Cyrille Giquello	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	5.x	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	Distributed Transaction, DTC, xa

Description:
Hello,

Mysql Connector/Net missing an enterprise feature : Distributed Transaction.

I try to push Mysql Server in some Windows projects but I fail because there is no support for Distributed Transaction (framework .Net 2 and 3 and MS DTC). I've to swith to Microsoft SQL Server, which make me sad ...

I think this feature should be planned.

Has someone know more about ??

Thanks
Cyrille.

How to repeat:
1/

MySQL Connector/Net does not currently support distributed transactions.
at MySql.Data.MySqlClient.MySqlConnection.EnlistTransaction(Transaction transaction)
at MySql.Data.MySqlClient.MySqlConnection.Open()

2/

Une exception de première chance de type 'System.NotSupportedException' s'est produite dans System.Data.dll
La méthode spécifiée n'est pas prise en charge.
at  MySql.Data.MySqlClient.MySqlPromotableTransaction.System.Transactions.ITransactionPromoter.Promote()
at System.Transactions.TransactionStatePSPEOperation.PSPEPromote(InternalTransaction tx)

There is a thrid error message case :

Multiple simultaneous connections or connections with different connection strings inside the same transaction are not cur
rently supported.
at MySql.Data.MySqlClient.MySqlConnection.Open()

Hello,
Is there a plan, or not, for Distributed Transactions ?

Reagards
Cyrille

hello

Is this fixed in version 6.x alpha?

Thanks

I used multiple connection with same connection string within a single transaction scope (.NET):

System.NotSupportedException: Multiple simultaneous connections or connections with different connection strings inside the same transaction are not currently supported.

I tested against 5.1 and 6.1 Alpha, no luck. If someone knows when a patch on this will be released please please please let us know many thanks

Norman, that is expected behavior of c/NET, take a look at pooling implementation.
Now for distributed transactions. I do not know but it is my opinion this will not be "fixed" in any of connectors but rather connectors will wait for proper implementation in server. Alas, for now, I am unaware of such plans in MySQL 6.0.

Thanks Tonci. I checked the code, the application opened two connections (SAME connection string) within single transaction:

conn #1: NHibernate conn - oConn1
conn #2: ADO.NET conn - oConn2

I will try to see if I simply use oConn1 (just one connection) will do the magic

   oConn2 = oConn1;

Thanks for your reply however, appreciate it.

"Won't Fix" as proper place for this is in server...

Hello,
 Tonci, I do not agree with you analize.

XA Distributed connections are implemented in Mysql server and have to be implemented in the Connector.Net.
Then to work, just have to configure the Transaction Coordinator, like MS-DTC.

There some documentation about implementing system.transaction in Connector to work with distributed transaction.
Also, there is the Npgsql2 (.Net PosgresSQL) source code where you can have a look to see how it works.

Sorry for my poor information, but I forgot them, because 6 months ago I had to stop using Mysql for a big project because the Connector does not implement distributed transaction.

Regards,
Cyrille.

Cyrille, true, but with many limitations. However and in spite of my ruling, I asked devs for second opinion here.

This time I managed to get around the problem by using same connection to make two/more calls instead of opening new in my code - which is indeed more efficient. And I got it working in the end. However, I do not rule out possibility in future where we'll NEED Distributed Transaction, that we do need one connection to a MySQL and another to an Oracle/M$SQL or other.

I am sorry but I had no feedback yet from c/NET team leader regarding this. I will inform you when I learn something new.

Bug#44334 was marked a duplicate of this report.

Until there is an incomplete XA support in server there is not much c/NET team can do. Sorry.

Bug #59035 was marked as a duplicate of this one.