Bug #26754 EnlistTransaction throws false MySqlExeption "Already enlisted"
Submitted: 1 Mar 2007 17:01 Modified: 20 May 2007 6:18
Reporter: Vlad Untu Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S2 (Serious)
Version:5.0.3.0, 5.0.5 OS:Microsoft Windows (Windows)
Assigned to: CPU Architecture:Any

[1 Mar 2007 17:01] Vlad Untu
Description:
After enlisting a null transaction (witch throws an enlistment exception) the internal state of the Connection object becomes invalid.

If you try to enlist a valid transaction after the first you always get "Already enlisted" exception.

How to repeat:
the assertions between the lines of code are valid;

assert: Transaction.Current == null

MySqlConnection c = new MySqlConnection(conn_string);
c.Open();

assert: c.State == Open

try {
   c.EnlistTransaction(Transaction.Current)
}
catch { }

using (TransactionScope ts = new TransactionScope()) {

assert: Transaction.Current != null;

   c.EnlistTransaction(Transaction.Current)
}

Suggested fix:
public override void EnlistTransaction(System.Transactions.Transaction transaction)
{

if (currentTransaction != null <<<add && currentTransaction.BaseTransaction != null>>>) {
	if (currentTransaction.BaseTransaction == transaction)
		return;

	throw new MySqlException("Already enlisted");
}
....
[13 Mar 2007 19:38] Vlad Untu
It seems that sometimes the server reports InTransaction when no BEGIN statement or other transaction start function call was called (I've also checked to see if the server is in AUTOCOMMIT=0). <<I will investigate this issue to be able to reproduce this situation>>

Now, if the server reports InTransaction the BeginTransaction function throws an NestedTransaction exception and sice the EnlistTransaction calls BeginTransaction the internal state of connection becomes invalid and subsequent enlistments throws "Already enlisted".

This is not an issue for programs that maintain a persistent connection to the database. If you connect to the server within the TransactionScope the connector works fine.
[13 Mar 2007 19:41] Vlad Untu
CORRECTION:
THIS IS AN issue for programs that maintain a persistent connection to the
database.
[15 Mar 2007 8:43] Tonci Grgin
Hi Vlad and thanks for your report.
I must admit I don't quite follow you. Can you provide me with better problem description as well as with steps to reproduce it (and a test case too)?
[15 Mar 2007 16:41] Vlad Untu
[Test()]
public void EnlistTransactionNullTest() {   
   MySqlConnection conn = new MySqlConnection("server = 192.168.0.200; user = root;");
   conn.Open();

   try {
      MySqlCommand cmd = new MySqlCommand();
      cmd.Connection = conn;
      cmd.Connection.EnlistTransaction(null);
   }
   catch { }

   using (TransactionScope ts = new TransactionScope()) {
      MySqlCommand cmd = new MySqlCommand();
      cmd.Connection = conn;
      try {
         cmd.Connection.EnlistTransaction(Transaction.Current);
      }
      catch (MySqlException) {
         Assert.Fail("No exception should have been thrown");
      }
   }
}
[15 Mar 2007 16:52] Vlad Untu
[Test()]
public void EnlistTransactionWNestedTrxTest() {
   MySqlConnection conn = new MySqlConnection("server = 192.168.0.200; user = root;");
   conn.Open();

   MySqlTransaction t = conn.BeginTransaction();

   using (TransactionScope ts = new TransactionScope()) {
      MySqlCommand cmd = new MySqlCommand();
      cmd.Connection = conn;
      try {
         cmd.Connection.EnlistTransaction(Transaction.Current);
      }
      catch (InvalidOperationException) { /* caught NoNestedTransactions */  }
   }

   t.Rollback();

   using (TransactionScope ts = new TransactionScope()) {
      MySqlCommand cmd = new MySqlCommand();
      cmd.Connection = conn;
      try {
         cmd.Connection.EnlistTransaction(Transaction.Current);
      }
      catch (MySqlException) {
         //Assert.Fail("No exception should have been thrown");
      }
   }
}

This test demonstrates how the failure (this bug) occurs. 
There is another bug in the connector or in the server that sets the `driver.ServerStatus` = InTransaction even if the server is not in transaction. So first I get the NoNestedTransaction exception and then `Already Enlisted`. At this point the connection can no longer be used.
[19 Mar 2007 14:29] Tonci Grgin
Vlad, thanks for all your help regarding transactions.

Verified as described with MySQL 5.0.38BK on WinXP Pro SP2 localhost using NET fw 2.0.50727 and c/NET SVN sources.
connection.cs, 279.
	public new MySqlTransaction BeginTransaction(IsolationLevel iso)
	{
            // First check to see if we are in a current transaction
            if ((driver.ServerStatus & ServerStatusFlags.InTransaction) != 0)
                throw ...

driver.ServerStatus is allways InTransaction.
[11 May 2007 15:51] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/26495
[11 May 2007 15:51] Reggie Burnett
Fixed in 5.0.7
[20 May 2007 6:18] MC Brown
A note has been added to the 5.0.7 changelog.