Msg 7391: linked server unable to begin a distributed transaction (both svrs running locally)

Question

I setup a LinkedServer from SqlServer 2014 to MySQL 5.7.3 running on my Win 10 PC. Both select & insert queries work fine alone via openquery, but the insert query won't function in a trigger. Please don't mark this as a duplicate unless you find a 'cure' that isn't already listed below!

OLE DB provider "MSDASQL" for linked server "MYSQL" returned message "[MySQL][ODBC 5.3(w) Driver]Optional feature not supported".

*Msg 7391, Level 16, State 2, Procedure TRG_AfterEventInsert, Line 14

The operation could not be performed because OLE DB provider "MSDASQL" for linked server "MYSQL" was unable to begin a distributed transaction.

There are TONS of posts on this but I have done everything I can find and it still won't work. I found a MS utility called dtcping which failed at first until I flipped a registry setting but now it succeeds.

On the DTC Properties screen I have enabled Network DTC Admin, allowed remote, allowed input/outbound without authentication and Enabled XA Transactions. On my linked server I have rpc & rpc out = true and "enable promotion of DT" false. I added the msdtc app into the firewall exclusions.

I also tried to disable DTC for my LinkedServer but that didn't work. I still get the error.

Can anyone suggest debugging measures here? I have spent almost a full day on this without success. MySQL driver is 5.3 (32bit).

UPDATE: dtcPing runs without errors, but when I try the trigger insert I see the following in my dtctrace.log

TRANSACTION_BEGUN RM_ENLISTED_IN_TRANSACTION "resource manager #1001 enlisted as transaction enlistment #1. RM guid = '57c2b4b4-f37a-4017-a1fc-2d95bd64693d'"

RECEIVED_ABORT_REQUEST_FROM_BEGINNER "received request to abort the transaction from beginner"

TRANSACTION_ABORTING "transaction is aborting"

Celom

Answer 1

When you create trigger, use the following construct to avoid transactions:

create trigger ti on t for insert, update as
begin

    COMMIT -- Commit FIRST to avoid DTC...
    insert into  [mysql]...[mysql.customers] (a,b) -- Do you work
    select  i, 'Test'
    from    inserted
    BEGIN TRAN -- Start tran again otherwise it will crash!!
end
go

Note, the "[mysql]...[mysql.customers]" syntax, request Provider MSDASQL to have Level 0 only setting enabled (go to linked servers and set it on the provider).

But as other's suggested, you probably better of by just kicking a job from the trigger.

Full test code:

---------------
-- Run on MYSQL...
---------------
CREATE TABLE customers (a INT, b CHAR (20), INDEX (a)) ENGINE=InnoDB;

---------------
-- Run on SQL Server
---------------
create table t (i int)

go

create trigger ti on t for insert, update as
begin

    COMMIT -- Commit tran to avoid DTC...
    insert into  [mysql]...[mysql.customers] (a,b)
    select  i, 'Test'
    from    inserted
    begin tran -- Start tran again otherwise it will crash!
end
go

insert into t (i) select 1

-- Verify results
select *
from [mysql]...[mysql.customers]

Beeves

Answer 2

Do you mean MySQL 5.3 ? The current manual shows versions as low as 5.7 and points that distributed transactions are only supported for the InnoDB storage engine 14.3.7 XA Transactions.

Provided you have checked all about the MySQL part, have you checked with different kind of triggers on the SQL Server side? Are you perhaps using an unsupported data type?

Swaddle

Sorry...typo in the version it's 5.7 and yes I'm definitely using InnoDB storage. The trigger is 'after insert' and I just run the same insert query that runs fine in my normal SQL session. – Celom

Answer 3

"A distributed query that is wrapped in a trigger, even with no transaction explicitly specified, is also treated as a distributed transaction."

support.microsoft.com/en-us/kb/274348

So you need to use DTC OR use (2) from Calling linked server from trigger

Ironworks

I am trying use DTC. The alternate methods in the link aren't good. I can't do replication from SQLSVR to MYSQL and scheduling a task will cause lag. – Celom

@DeannaD, in comments I mentioned it is not necessary to wait till the task is triggered by schedule, you can start a job from the trigger, so the lag will be minimum and the records will be processed almost immediately. Alternatively you can run the job continuously which can check the table every X millisecond. – Ironworks

@DeannaD, as for replication, there are various customer (non-native) solutions to replicate from MSSQL to MySQL, e.g. symmetricds.org – Ironworks