mysql分布式事务 没同步,链接服务器上的SQL Server和mysql之间的分布式事务

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.

Enable XA Transactions in MSDTC

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:

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

解决方案

Theoretically this should work.

I would suggest different steps to sort this out:

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

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.

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.