Distributed Transaction between Microsoft SQL Server and MariaDB - A Myth

• Download LinkedServer_MariaDB_AutoGen_Script.zip - 772 B
• Download Distributed_Transactions_Involving_MSSQL_MariaDB.zip - 3 KB

Introduction 

MariaDB is a drop-in replacement for MySQL. It was created by Monty Widenius and his team (the one who created MySQL). Many software giants such as Google, RedHat, Wikimedia, etc., have migrated from MySQL to MariaDB because of its technical advantages (ref: link).

So MariaDB is a hot topic now and Distributed Transactions between MariaDB and the famous Microsoft SQL Server was a much needed one. And this article provides trials/experiments done and links which prove that Distributed Transactions between MariaDB and Microsoft SQL Server is a myth. 

Note: Since the developer is the same for MySQL and MariaDB their internal architectures are similar and migration is not a tough job.  

Background 

The moment Google migrated to MariaDB from MySQL and also devoted one of its engineers to MariaDB development MariaDB has become very famous. The main reason I am posting this article is that there is a lot of help in the Internet but only very few that tell the truth about distributed transactions involving MariaDB and MS SQL Server.  

Experiments carried out  

List of software installed before conducting this experiment (Prerequisites): 

1. Microsoft SQL Server 2008 R2 
2. MariaDB 5.5.33a – x86 
3. ADO.NET driver for MySQL (mysql-connector-net-6.7.4.msi) [Purpose explained later in this document] – x86
4. ODBC driver for Windows – x64 (mysql-connector-odbc-5.2.5-winx64.msi) [Purpose explained later in this document] 

Experiments made: 

• Try 1 – From MSSQL Server 
• Try 2 – From .NET (Code) – Transaction Scope
• Try 3 – From .NET (Code) – Transaction Scope with 2 Phase Commit
• Using the AutoEnlist keyword  

Try 1 – From MS SQL Server: 

The first try of this Distributed transaction was conducted using MS SQL Server 2008 R2 with the popular concept of 'Linked Servers'. For linking a MariaDB server though we'd need a driver: 'ODBC driver for Windows – x86 (mysql-connector-odbc-5.2.5-winx64.msi)'. 

You need to install this driver and configure a Data Source. This can be done through Start -> Control Panel -> All Control Panel Items -> Administrative Tools -> Data Sources (ODBC). Add a 'System DSN' there with the installed drivers (either the Unicode or ANSI one).

After adding a Data Source we have to create a Linked server, creating procedure is in a separate attachment. Or you can use 'MS SQL SERVER  2008 R2-> Object Explorer ->Server Objects -> Linked Servers' to create one. 

Now I tried to create a Stored Procedure using Distributed Transactions with both MSSQL and MariaDB only to find MSSQL throwing an error –

OLE DB provider "MSDASQL" for linked server "MARIADB" returned message 
  "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".
Msg 7303, Level 16, State 1, Procedure Proc_test_distributedtrans_sql_mariadb_ins, Line 21
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "MARIADB". 

Try 2 – From .NET (Code) – Transaction Scope: 

Now the .NET platform was brought into action.

(ADO.NET driver for MySQL (mysql-connector-net-6.7.4.msi) is needed for this. After installing it add the dll named 'MySql.Data.dll to the References of your project and Import it.')

A TransactionScope was established and two connection strings were bought together an MSSQL one and a MariaDB one. Here also during code execution when a MSSQL or MySQL (MariaDB) connection is opened there will be no issues, but the moment a second connection is opened in a single Transaction (Even after the first one is closed) an error shown below will be thrown 

"System.NotSupportedException was caught
  Message=Specified method is not supported."
(Or)
"MySQL Connector/Net does not currently support distributed transactions." 

Try 3 – From .NET (Code) – Transaction Scope with 2 Phase Commit: 

(ADO.NET driver for MySQL (mysql-connector-net-6.7.4.msi) is also needed for this) This method is similar to the above one as this one also operates under a TransactionScope but performs a ‘2 Phase commit’. One is called a Prepare phase and the next one is called the Commit Phase. Its source is provided in the attachment. 

Even the results were also similar, the same failure with the same bugs. 

"System.NotSupportedException was caught
  Message=Specified method is not supported."
(Or)
"MySQL Connector/Net does not currently support distributed transactions."  

Using the AutoEnlist keyword: 

This keyword can be found upon extended searches but this keyword is at times misunderstood.

Refer the MySQL documentation for this. If you are performing a MySQL (MariaDB) transaction alone and if the same error

"MySQL Connector/Net does not currently support distributed transactions"

is thrown then you can use this AutoEnlist and set it to false to make the compiler understand that what you are performing is not a Distributed Transaction between different databases.  

Now if you use this keyword in our case (Distributed Transaction involving different databases) then you will succeed in executing the program with no errors but the purpose of a Transaction will fail. The moment AutoEnlist <code>= false is compiled, the TransactionScope excludes that MariaDB batch from the current transaction and its results will be committed wherever there is an ExecuteNonQuery() statement. It will not wait for the Scope object to raise the Complete() method.

Inferred Stuff:  

From the above tries/experiments it was evident that Distributed Transaction is not allowed in MariaDB (MySQL) Database. (Even if Microsoft  Distributed Transaction Coordinator is running). 

And here is where it would be specified in the MySQL site itself that MySQL (MariaDB) does not permit 'Distributed Transactions'. And it is also not fixed in MySQL 6.1 alpha. 

(Refer to the comment posted named ‘[11 Jun 2008 12:01] Cyrille Giquello’ and other comments after that.)

Points of Interest  

I learnt one major thing, not only that Distributed transactions are not supported between MariaDB (MySQL) and MS SQL but also not to use Distributed Transactions in general. And below are some links which suggest not to use a 'Distributed Transaction' generally. The main reason they emphasize are network issues which can result in a non-Atomic transaction, which simply is not a transaction. 

• http://stackoverflow.com/questions/128377/what-is-the-best-way-to-do-distributed-transactions-across-multiple-databases 
• http://c2.com/cgi/wiki?DistributedTransactionsAreEvil 

History 

I will upload the MySQL to MariaDB migration procedure in the next article. I should have done that first, but since this got my interest very much, I am posting this first.