Distributed transaction on a linked server between sql server and mysql

Question

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

 Table1 ID INT,Code VARCHAR(100) 

I created a linked MyLinkedServer server in SQL Server using the "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='########' 

Related 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 was created successfully, and I can query the Mysql table in SQL Server.

Query

When i started

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

Record is inserted. However, when I start a transaction and run INSERT , I get an error message:

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

Mistake:

The OLE DB provider "MSDASQL" for the linked server "MyLinkedServer" returned the message "[MySQL] [ODBC 5.3 (a) Driver] Additional function not supported." Msg 7391, Level 16, State 2, Line 8 The operation could not be completed because the OLE DB provider "MSDASQL" for the linked server "MyLinkedServer" could not start the distributed transaction.

What I have tried so far.

• Enable XA Transactions in MSDTC

• The following option is enabled in the Linked Server Provider

  • Nested Queries
  • Level 0 only
  • Allow inprocess
  • Supports "as an operator"

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

Distributed Transactions Between MySQL and MSSQL

SQL Server and MySQL Compatibility?

SQL Server and MySQL Syncing

EDIT

Additional Information:

• MySQL uses the InnoDB storage engine on an Ubuntu machine.

• I have already configured the ODBC connector and used it to configure the ODBC system data source used by Linked Server

Answer 1

Theoretically, this should work.

I would suggest a few steps to figure this out:

• Have you checked the MySql storage system yet? It seems that InnoDB support engine support extends to every MySql document: https://dev.mysql.com/doc/refman/5.7/en/xa.html

• See if you can switch to using the MySQL Connectors installation connection to connect to MySql in SQL Server instead of the OLEDB provider, which is listed in the MySql document above that supports transaction propagation.

• If it still does not work, perhaps the MSDTC service itself has problems, see if you can isolate it, for example, get an instance of SQL Server running on the MySql server (if you use Windows MySql), or try installing Windows MySql in the Sql field Server to distribute the transaction between two MySql. Which could point you to a real problem.

EDIT:

Unfortunately, it seems that you have proved that this does not work, I look at the MySql document more carefully and regret that it looks like I did not read it completely, it says:

Among MySQL connectors, MySQL Connector / J 5.0.0 and higher currently supports XA directly

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

Someone suggested something here: https://social.msdn.microsoft.com/Forums/en-US/fc07937d-8b42-43da-8c75-3a4966ab95f9/xa-msdtc?forum=windowstransactionsprogramming , which should implement your own XA-Compliant resource managers that will be used by your application ( https://msdn.microsoft.com/en-us/library/ms684317.aspx )

Answer 2

As in SQL 2014, you could configure a linked server so that it does not enter a distributed transaction. Although you can try adding "Enlist = false" in the @provstr argument to sp_addlinkedserver