sql server和mysql之间的链接服务器上的分布式事务 [英] Distributed Transaction on Linked Server between sql server and mysql
问题描述
我在SQL Server 2014和MySQL上都有一个说Table1的表.
I have a table say Table1 on SQL Server 2014 and MySQL both.
Table1
ID INT,Code VARCHAR(100)
我使用用于ODBC的Microsoft OLEDB提供程序"在SQL Server中创建了链接服务器MyLinkedServer.
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='########'
链接服务器设置
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表.
The linked server is created successfully and I am able to query Mysql Table in SQL Server.
查询
我跑步时
INSERT INTO MyLinkedServer...Table1(ID,Code) SELECT 1,'Code1'
记录已插入.但是，当我开始交易并运行INSERT时，会出现错误:
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
错误:
链接服务器"MyLinkedServer"的OLE DB提供程序"MSDASQL"返回消息"[MySQL] [ODBC 5.3(a)驱动程序]不支持可选功能". 消息7391，第16级，州2，第8行 由于链接服务器"MyLinkedServer"的OLE DB提供程序"MSDASQL"无法开始分布式事务，因此无法执行该操作.
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.
-
在MSDTC中启用XA事务
Enable XA Transactions in MSDTC
在链接服务器提供程序中设置以下设置已启用
Enabled following setting in Linked Server Provider
- 嵌套查询
- 仅零级
- 允许进行中
- 支持赞"运算符
我检查了以下链接及其建议，但错误仍然存在:
I checked the following links and their suggestions however the error persists:
编辑
其他详细信息:
-
MySQL在Ubuntu计算机上使用InnoDB存储引擎.
MySQL is using InnoDB storage engine on Ubuntu machine.
我已经配置了 ODBC连接器，并使用它来配置一个链接服务器中使用的ODBC系统数据源
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:
-
您检查过MySql存储引擎了吗?看起来每个MySql文档仅InnoDB存储引擎支持分发事务: https://dev.mysql.com/doc/refman/5.7/en/xa.html
查看是否可以切换到使用MySQL连接器设置连接来连接到SQL Server中的MySql(而不是OLEDB提供程序)，该消息由上面的MySql文档声明支持分布式事务.
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.
如果仍然无法正常工作，则可能是MSDTC服务本身存在问题，请查看是否可以隔离该问题，例如让SQL Server实例在MySql服务器上运行(如果您使用的是Windows MySql)，或者请尝试在Sql Server框上安装Windows MySql，以使两个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.
不幸的是，您似乎证明了这是行不通的，我仔细查看了MySql文档，但很抱歉，我似乎没有完全阅读它，它说:
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连接器中，MySQL Connector/J 5.0.0和更高版本直接支持XA
Currently, among the MySQL Connectors, MySQL Connector/J 5.0.0 and higher supports XA directly
通过其他Google搜索，我发现了这一点: https://bugs.mysql.com /bug.php?id=37283 ，人们在很多年前就报告了此错误，并将其标记为无法修复".
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://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 )
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)
这篇关于sql server和mysql之间的链接服务器上的分布式事务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
