Distributed Transaction on Linked Server between sql server and mysql

Question

I have a table say Table1 on SQL Server 2014 and MySQL both.

Table1
ID INT,Code VARCHAR(100)

I created a linked server MyLinkedServer in SQL Server using "Microsoft OLEDB Provider for ODBC".

**Linked Server **

EXEC master.dbo.sp_addlinkedserver @server = N'MyLinkedServer', @srvproduct=N'MyLinkedServer', @provider=N'MSDASQL', @datasrc=N'MyLinkedServer'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MyLinkedServer',@useself=N'False',@locallogin=NULL,@rmtuser=N'username',@rmtpassword='########'

Linked Server Settings

EXEC master.dbo.sp_serveroption @server=N'MyLinkedServer', @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'MyLinkedServer', @optname=N'use remote collation', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'MyLinkedServer', @optname=N'remote proc transaction promotion', @optvalue=N'true'

The linked server is created successfully and I am able to query Mysql Table in SQL Server.

Query

When I run

INSERT INTO MyLinkedServer...Table1(ID,Code) SELECT 1,'Code1'

The record is inserted. However when I start a transaction and run the INSERT, I get an error:

BEGIN TRAN
INSERT INTO MyLinkedServer...Table1(ID,Code) SELECT 1,'Code1'
COMMIT

Error:

OLE DB provider "MSDASQL" for linked server "MyLinkedServer" returned message "[MySQL][ODBC 5.3(a) Driver]Optional feature not supported". Msg 7391, Level 16, State 2, Line 8 The operation could not be performed because OLE DB provider "MSDASQL" for linked server "MyLinkedServer" was unable to begin a distributed transaction.

What I have tried so far.

1. Enable XA Transactions in MSDTC

2. Enabled following setting in Linked Server Provider

   • Nested queries
   • Level zero only
   • Allow inprocess
   • Supports ‘Like’ Operator

I checked the following links and their suggestions however the error persists:

Distributed transactions between MySQL and MSSQL

SQL-Server and MySQL interoperability?

SQL Server and MySQL Syncing

EDIT

Additional Details:

• MySQL is using InnoDB storage engine on Ubuntu machine.

• I have already configured the ODBC connector and used it to configure a ODBC System Data Source which is used in the Linked Server

Answer 1

Theoretically this should work.

I would suggest different steps to sort this out:

1. Have you checked you MySql storage engine yet? It looks only InnoDB storage engine support distribute transaction per MySql document: https://dev.mysql.com/doc/refman/5.7/en/xa.html

2. See if you can switch to use MySQL Connectors setup connection to connect to MySql in SQL Server instead of OLEDB provider, which state by MySql document above that support distribute transaction.

3. If still not working, it might be the MSDTC service itself has some problem, see if you can isolate that like get a SQL Server instance running on the MySql server box(if you are using Windows MySql), or try install Windows MySql on the Sql Server box to get distribute transaction working between two MySql. Which would be able to point you to the actual problem.

EDIT:

Unfortunately it looks that you proved this not working, I've a closer look at the MySql document and sorry it looks that I wasn't reading it thoroughly, it says:

Currently, among the MySQL Connectors, MySQL Connector/J 5.0.0 and higher supports XA directly

And by some other Googling I found this: https://bugs.mysql.com/bug.php?id=37283, people report this bug many years ago and they marked this as a won't fix.

Some one suggested something here: https://social.msdn.microsoft.com/Forums/en-US/fc07937d-8b42-43da-8c75-3a4966ab95f9/xa-msdtc?forum=windowstransactionsprogramming, which is to implement your own XA-Compliant Resource Managers to be used by your application (https://msdn.microsoft.com/en-us/library/ms684317.aspx)

Answer 2

As on SQL 2014, you could have configured the linked server not to enlist in a distributed transaction. Although you could try adding "Enlist=false" in the @provstr argument to sp_addlinkedserver