在 SQL Server 和 MySQL 之间的联接服务器上进行分布式事务

Question

我在SQL Server 2014和MySQL上都有一张名为Table1的数据表。

Table1
ID INT,Code VARCHAR(100)

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

**链接服务器**

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='########'

链接服务器设置

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'

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

查询：

当我运行时：

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

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

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

错误：

OLE DB提供程序“MSDASQL”为链接服务器“MyLinkedServer”返回消息“[MySQL] [ODBC 5.3(a) Driver]不支持的可选功能”。Msg 7391，级别16，状态2，行8 由于OLE DB提供程序“MSDASQL”无法开始分布式事务，因此无法执行操作。

我到目前为止尝试了以下方法：

1. 在MSDTC中启用XA事务

2. 已启用链接服务器提供程序中的以下设置

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

我检查了以下链接和它们的建议，但是错误仍然存在：

MySQL和MSSQL之间的分布式事务

SQL Server和MySQL互操作性？

SQL Server和MySQL同步

编辑

其他细节：

• MySQL在Ubuntu机器上使用InnoDB存储引擎。

• 我已经配置了ODBC连接器并使用它来配置ODBC系统数据源，该数据源用于链接服务器。

- ughai

---

你能否也发布一下链接服务器创建的DDL呢？ - Philip Devine

@PhilipDevine - 更新了问题，并提供了链接服务器的详细信息。 - ughai

在这里，您可以找到一些检查，这些检查在另一种情况下对我有所帮助：https://dev59.com/XOo6XIcBkEYKwwoYSCg1 HTH - lucazav

Answer 1

理论上这应该可以解决问题。

我建议采取以下步骤来解决：

1. 您是否已经检查了您的MySql存储引擎？看起来只有InnoDB存储引擎支持每个MySql文档的分布式事务：https://dev.mysql.com/doc/refman/5.7/en/xa.html 2. 看看是否可以切换到使用MySQL Connectors设置连接以连接到SQL Server中的MySql，而不是OLEDB提供程序，它在上面的MySql文档中说明支持分布式事务。

3. 如果仍然不起作用，可能是MSDTC服务本身存在一些问题，请尝试隔离，如在MySql服务器框（如果您使用的是Windows MySql）上运行SQL Server实例，或者尝试在Sql Server框上安装Windows MySql以使两个MySql之间的分布式事务工作。这将能够指出实际的问题。

编辑：

不幸的是，看起来您已经证明了这种方法不能解决问题。我认真看了一下MySql文档，很抱歉我没有仔细阅读，它说：

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

通过一些其他搜索，我发现了这个： https://bugs.mysql.com/bug.php?id=37283，人们多年前就报告了这个错误，并标记为不修复。

有人在这里提出了一些建议：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)

- Simon Wang

---

谢谢，Mysql存储引擎是InnoDB，并且在Ubuntu机器上。让我在同一台Windows机器上尝试一下，然后我会更新您。 - ughai

首先，在 Sql Server Windows 系统上安装 MySQL 连接器怎么样？ - Simon Wang

我已经配置了ODBC连接器，并使用它配置了ODBC系统数据源，该数据源用于链接服务器。 - ughai

尝试在Windows系统上使用MySQL和SQL Server以及启用了所有设置的MSDTC，但是没有成功 :( "[MySQL] [ODBC 5.3(w) Driver]不支持的可选功能"表明ODBC连接器或MySQL本身不支持此功能。 - ughai

我已经更新了我的答案，看起来如果你真的想让它工作，你需要编写一些代码。 - Simon Wang

Answer 2

在SQL 2014上，您可以配置链接服务器不参与分布式事务。虽然您可以尝试在sp_addlinkedserver的@provstr参数中添加"Enlist=false"。

- Jagadish Sharma U

---

你可以配置链接服务器不参与分布式事务，但我需要一个分布式事务。没有事务时它能正常工作。 - ughai

请使用以下链接：https://dev59.com/vlrUa4cB1Zd3GeqPl6r4，希望能对您有所帮助。 - Jagadish Sharma U