MySQL Bugs: #67560: Rollback not working for TransactionScope

Bug #67560	Rollback not working for TransactionScope
Submitted:	12 Nov 2012 12:10	Modified:	1 Jul 2016 12:58
Reporter:	Kristoffer Danielsson	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	6.6.4	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	transaction, TransactionException, TransactionScope

Description:
MySQL Community Server 5.5.21
MySQL .NET Connector 6.6.4
Visual Studio 2010, C# 4.0
Distributed Transaction Coordinator service enabled

As soon as I add a TransactionScope around my database queries, I get an exception telling me that the operation is not valid for the state of the transaction - effectively disabling the rollback functionality.

I created a simple sample that demonstrates my problem:

var factory = System.Data.Common.DbProviderFactories.GetFactory("MySql.Data.MySqlClient");

using (var transaction = new System.Transactions.TransactionScope())
{
    var connection = factory.CreateConnection();
    connection.ConnectionString = "Server=localhost;Port=3306;Database=test;User ID=root;Password=test;Auto Enlist=true";
    connection.Open(); // <-- silent exception here!

    var command = connection.CreateCommand();
    command.CommandText = "CREATE TABLE TestTable (ID INT) ENGINE = InnoDB DEFAULT CHARSET=utf8;";

    command.ExecuteNonQuery();

    // ATTENTION! This should imply a rollback!
    // transaction.Complete();
}

This produces a TransactionException (note that in order to break on this type of exception, it needs to be explicitly enabled in Visual Studio, Ctrl+Alt+E). Then, when the code finishes, the table has indeed been created even though there should have been a rollback!

I thought MySQL supported TransactionScope. What am I doing wrong here? Is this a non-supported feature?

How to repeat:
See sample code above.

Kristoffer, as I understand, mysql does not support DDL in transactions.

http://dev.mysql.com/doc/refman/5.0/en/cannot-roll-back.html

Hello Kristoffer Danielsson,

Thank you for the bug report.
I could not repeat the issue at our end using with Visual Studio 2013, Connector/NET 6.9.8.
If you can provide more information, feel free to add it to this bug and change the status back to 'Open'.

user manual:
http://dev.mysql.com/doc/refman/5.7/en/cannot-roll-back.html

Thank you for your interest in MySQL.

Thanks,
Chiranjeevi.