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"

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]