We help IT Professionals succeed at work.

Insert into MySQL Table from MSSQL Insert Trigger

cf13
cf13 asked
on
Hi there!

I'm now a few days on solving the following problem:

I have an application, that runs on a MSSQL Database. Now an other Application, running on an MySQL-Database.
Now - they share some data, which makes it necessary to transfer partial data between two tables.

The approach is now, to create a trigger on the MSSQL-Table (t1) and to write the data from "Insert"-Table to the MySQL-Table (t2).
Remember: I do only need a partial set of data, where column names on t1 and t2 vary. (ffieldms1-data will be filled to fieldmy1)

MySQL-Database is set up as Linked Server via ODBC 5.1 Driver.

My konzeptual Trigger is the follow:


CREATE TRIGGER myInsertTrigger
    ON [MSSQLDB].[dbo].[t1]
    FOR INSERT,UPDATE
    AS
		DECLARE @ID int
		SELECT @ID = (SELECT [id] FROM Inserted)
		PRINT @ID
		IF ( @ID >= 5000 ) AND ( @ID < 5999 )
        BEGIN
		SET XACT_ABORT ON
		BEGIN TRANSACTION
			INSERT INTO 
				OPENQUERY([MYSQL-REMOTE],'
				SELECT fieldmy1, fieldmy2
				FROM t1')
			SELECT fieldms1, fieldms2
			FROM Inserted

			DELETE FROM [MSSQLDB].[dbo].[t1] WHERE [ID] = @ID
		COMMIT TRANSACTION
		END

Open in new window


When Trigger is executed (e.g. with a update of ID to above range) following error occurs

The operation could not be performed because the OLE DB provider 'MSDASQL' was unable to begin a distributed transaction.
[MYSQL][ODBC 5.1 Driver] Optional Feature not supported

I need specific help - google brings up a mass of threads, i did not find the right one.

If someone knows, how to configure the mssql-script, the mssql server or the mysql-server

THANK YOU
Comment
Watch Question

View Solutions Only

dqmq

Commented:
Sorry, I cannot help so much with your specific problem.  However, can't overlook some other issues with your trigger.

1. Generally, not a good idea to start a transaction inside a trigger.

2. Your trigger will not work properly on a multiple record insert or update.

3. I don't understand the delete part.  Don't think you can delete the same record(s) you are inserting/updating in T1.   Perhaps need an "instead of" trigger.

See if attached trigger moves you ahead.
CREATE TRIGGER myInsertTrigger
    ON [MSSQLDB].[dbo].[t1]
    FOR INSERT,UPDATE
    AS

        BEGIN
        SET XACT_ABORT ON

        INSERT INTO OPENQUERY([MYSQL-REMOTE],'SELECT fieldmy1, fieldmy2 FROM t1')
			SELECT fieldms1, fieldms2
			FROM Inserted
                            WHERE Inserted.ID >= 5000 
                              AND Inserted.ID < 5999

        --don't think you can do this
	DELETE FROM [MSSQLDB].[dbo].[t1] WHERE [ID] in 
         (Select ID FROM Inserted
                            WHERE Inserted.ID >= 5000 
                              AND Inserted.ID < 5999)
       END

Open in new window

cf13

Author

Commented:
I need to move the data from MSSQL to MySQL.

The solution i want is also only a workaround.
I can not insert data into the MySQL table.
Getting the error above.
cf13

Author

Commented:
I tried to execute my INSERT INTO OPENQUERY seperately.

Sure - With FROM t1 and some WHERE bla.

I Inserted this exact code into a Trigger.

ERROR!

Someone knows how to execute this trigger jobs like they were executed normally?
cf13

Author

Commented:
Now, i tried the following:

I packed my INSERT (into Mysql) Script into a STORED PROCEDURE with parameter - and...IT WORKS (manually)!

BUT:
If i execute the script from within trigger, i get exactly the same error.
My trigger now looks like

[code]
CREATE TRIGGER bla
    ON t1
    FOR INSERT,UPDATE
    AS
            DECLARE @ID int
            SELECT @ID = (SELECT [id] FROM Inserted)
            PRINT @ID
            IF ( @ID >= 5000 ) AND ( @ID < 5999 )
            BEGIN
              exec proce_dure @ID
            END
[/code]

Someone?
dqmq

Commented:
First, and this is NOT germaine to your problem, but something else to think about.  Inserted is a table.  which means it can have more than one row. Should it have more than one row, SELECT @ID=(Select ID from inserted)  will only return the the ID from one of the rows.  In other words, your trigger will miss some of the updates.


Second, and perhaps more relevant.  The error you were getting was related to distributed transactions.  That means updates on two different servers are being coordinated so that neither will succeed without the other.  That's probably a good thing for you, but it does make the update more complex.  I'm even suspecting from your error message that your ODBC provider does not support distributed transactions (or at least the way you have it configured).   So you need to get distributed transactions working.

The reason it works outside of the trigger is because, in that case, there is no transaction on the SQL Server side, thusNOT distributed.   But the trigger runs in the context of an insert/update on the SQL server side and that implies a transaction that must be coordinated with the update on MSSQL.  Thus a distributed transaction. You can easily prove/disprove my theory.  Change your standalone test to include an update on SQL Server.  Something like this:

--This works, because it is not distributed:
exec proce_dure @ID

--This fails (I'm guessing), because it is distributed
begin transaction
exec proce_dure @ID
commit transaction

--If my theory is correct, this may work. (The data integrity implications are ominous, but I will let you wrestle with that).



CREATE TRIGGER myInsertTrigger
    ON [MSSQLDB].[dbo].[t1]
    FOR INSERT,UPDATE
    AS

        BEGIN
        SET XACT_ABORT ON
        COMMIT TRANSACTION
        INSERT INTO OPENQUERY([MYSQL-REMOTE],'SELECT fieldmy1, fieldmy2 FROM t1')
			SELECT fieldms1, fieldms2
			FROM Inserted
                            WHERE Inserted.ID >= 5000 
                              AND Inserted.ID < 5999

       END

Open in new window

dqmq

Commented:
P.S.
I still don't understand why you have gravitated to a trigger, which complicates the scenario.  You have this:

Insert to MS SQL table
Fires insert trigger
   If certain Id range
      Insert to MYSQL table
      Delete from MS SQL table

Why not this:
If certain ID range
   Insert into MYSQL table
Else
   Insert into  MS SQL Table
cf13

Author

Commented:
Thanks for your help, dqmq!

You said, i have to enable distributed transactions - can you tell me where to look?

I need a trigger, because every moment Application1 writes data to MSSQL-db, it has to be converted and instantly inserted into the other db. An option is to execute a stored procedure every few seconds - i do not know how this performs.

Your code does not work either.
I tried a trigger, where only a fixed insert is given to MYSQL (no Insert-table, nothing regarding to the current INSERT/UPDATE).
It gives me the same error.
Also i tried INSTEAD OF and AFTER (which is implicit with FOR).
Same error.

It seems like a Trigger itself works inside some distributed context...
So where do i enable this distributed transactions via my ODBC driver?
dqmq
Commented:
>It seems like a Trigger itself works inside some distributed context...
Not inside a distributed context, rather inside a transaction context.  In other words, it implies there is already an SQL update in progress, namely the one  that caused the trigger to fire in the first place.   What makes the trigger distributed is when you attempt to do an update cross-platform while already inside a transaction context.  My idea to precede your remote update with a commit was an attempt to end the transaction context before the remote update.


>You said, i have to enable distributed transactions - can you tell me where to look?  
Hmmm...I tried to couch my comments with the disclaimer that I really don't know how to do it.  There's a good reason why.  Distributed transactions are an immature (and often unreliable) technology.  Even on the same platform, they are a challenge.  Across platforms, they are a nightmare.  Inside a trigger, even more so.  I've always managed to find an asynchronous alternative that does not require them.

So, the starting place is to decide if distributed transactions are worth the pain.  Distributed transactions means that application 1 transactions will be coupled with MySQL transactions and will wait for them to finish. If MySQL is unavailable, Application 1 is blocked.  Is that what you want?  If so, plan on some serious Googling about linked servers and distributed transactions.  Like here:

http://www.groupsrv.com/computers/about664440.html

If not, try to DISABLE/avoid/circumvent distributed transactions in some way. Moving your remote SQL outside of a trigger and outside of a transaction context are proactive steps.  The simplest approach is for Application 1 to perform the remote update before it does any local SQL.

Another classic solution is to "queue" up the remote requests and handle them in an asynchronous process.  Service Broker is designed for that and addresses your concern about scheduling a stored procedure every few seconds.

Sorry I cannot paint a better picture.
















For example, you could queue the requests for MYSQL and process them in a separate process.



 
   
Commented:
If you are going to do this in a trigger, I'm betting your driving reason is that you don't have control of the client issuing the INSERT statement.

If this is the case, I'd suggest that you create a new table - call it MySQLQueue and insert the data selectively that is pushed into this table, into that Queue table, then use a job that runs and pushes it over into MySQL.

If you need certain records deleted from your original table you could also do that inside your trigger, but I'd just suggest using an INSTEAD OF trigger to do the job.

Using this approach won't lock your inserts on your original table while you try to synchronize with a remote MySQL server.  It does, however, introduce some latency based on the frequency that the job processes your queue, but they are processed asynchronously at least.

While triggers are a very powerful tool, they can easily bring a system to its knees.  Use them with extreme caution and ALWAYS very judiciously.
cf13

Author

Commented:
Yeah right.
Meanwhile i found this solution by myself. It is like you suggested.
Thank you also for the great explanation of distributed transactions!

my way as follows:
[code]
CREATE TRIGGER processUpdate
    ON [table]
    FOR UPDATE
    AS
            INSERT INTO [temporaryTableWithSameStructure]              
            FROM Inserted
            SET IMPLICIT_TRANSACTIONS ON
            GO
            exec processUpdate_ProcedureWithMySQLAction
[/code]

I still did not test, if it works without the SET IMPLICIT_TRANSACTIONS ON