sql server和mysql之间链接服务器上的分布式事务

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

我有一个表在SQL Server 2014和MySQL上表示Table1。

Table1
ID INT,Code VARCHAR(100)

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

我使用“Microsoft OLEDB Provider for ODBC”在SQL Server中创建了一个链接服务器MyLinkedServer。

**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.

链接服务器已成功创建，我可以在SQL Server中查询Mysql表。

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:

记录已插入。但是，当我启动一个事务并运行INSERT时，我收到一个错误：

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.

链接服务器“MyLinkedServer”的OLE DB提供程序“MSDASQL”返回消息“[MySQL] [ODBC 5.3（a）驱动程序]不支持可选功能”。消息7391，级别16，状态2，行8无法执行操作，因为链接服务器“MyLinkedServer”的OLE DB提供程序“MSDASQL”无法启动分布式事务。

What I have tried so far.

到目前为止我尝试了什么。

Enable XA Transactions in MSDTC

在MSDTC中启用XA事务
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

MySQL和MSSQL之间的分布式事务

SQL-Server and MySQL interoperability?

SQL-Server和MySQL的互操作性？

SQL Server and MySQL Syncing

SQL Server和MySQL同步

EDIT

编辑

Additional Details:

额外细节：

MySQL is using InnoDB storage engine on Ubuntu machine.

MySQL在Ubuntu机器上使用InnoDB存储引擎。
I have already configured the ODBC connector and used it to configure a ODBC System Data Source which is used in the Linked Server

我已经配置了ODBC连接器并使用它来配置链接服务器中使用的ODBC系统数据源

2 个解决方案

#1

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

你有没有检查过你的MySql存储引擎？它看起来只有InnoDB存储引擎支持按MySql文档分发事务：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.

看看你是否可以切换到使用MySQL连接器设置连接来连接到SQL Server中的MySql而不是OLEDB提供程序，它通过上面支持分发事务的MySql文档说明。
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.

如果仍然无法正常工作，可能是MSDTC服务本身有一些问题，看看你是否可以隔离就像在MySql服务器上运行一个SQL Server实例一样（如果你使用的是Windows MySql），或者尝试在Windows上安装Windows MySql在Sql Server框中获取两个MySql之间的分配事务。哪能指出实际问题。

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:

不幸的是，看起来你证明这不起作用，我仔细看看MySql文件，抱歉看起来我没有彻底阅读它，它说：

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

目前，在MySQL连接器中，MySQL Connector / J 5.0.0及更高版本直接支持XA

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.

通过其他一些谷歌搜索我发现了这个：https：//bugs.mysql.com/bug.php？id = 37283，人们多年前报告这个错误，他们将此标记为无法解决。

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)

有人在这里提出了一些建议：https：//social.msdn.microsoft.com/Forums/en-US/fc07937d-8b42-43da-8c75-3a4966ab95f9/xa-msdtc?forum=windowstransactionsprogramming，这是为了实现你自己的XA-适用于您的应用程序的兼容资源管理器（https://msdn.microsoft.com/en-us/library/ms684317.aspx）

#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

与SQL 2014一样，您可以将链接服务器配置为不在分布式事务中登记。虽然您可以尝试在@provstr参数中将“Enlist = false”添加到sp_addlinkedserver

#1