March 6, 2015 at 5:04 am
Hello everybody, I've encountered the following problem which I can't explain to myself.
My setup is: SQL 2014 + Linked server (name it 'EXTSYS') to a MySQL DB (v.5.1.73) through ODBC Connector
EXTSYS has two tables, EntityMaster and EntityDetail, linked by a foreign key from Detail to Master
SQL 2014 has a stored procedure to write in EXTSYS new Entities, writing first on EntityMaster and then on EntityDetail.
My INSERT syntax is something like
INSERT INTO OPENQUERY(EXTSYS, 'SELECT id, createdtime, modifiedtime FROM EntityMaster') (id, createdtime, modifiedtime)
VALUES (131177, getdate(), getdate())
INSERT INTO OPENQUERY(EXTSYS, 'SELECT id, name, surname, company FROM EntityDetail') (id, name, surname, company)
VALUES (131177, 'TestName', 'TestSurname', 'TestCompany')
Result is the following:
- First INSERT goes ok, in MySQL I find the new inserted record
- Second INSERT fails with the following message "Cannot add or update a child row: a foreign key constraint fails (`MySQLschemaname`.`EntityDetail`, CONSTRAINT `fk_1_EntityDetail` FOREIGN KEY (`id`) REFERENCES `EntityMaster` (`id`) ON DELETE CASCADE)"
BUT....if I change my INSERT syntax like this
EXECUTE ('INSERT INTO EntityMaster (id, createdtime, modifiedtime) VALUES (131177, NOW(), NOW())') AT EXTSYS
EXECUTE ('INSERT INTO EntityDetail (id, name, surname, company) VALUES (131177, ''TestName'', ''TestSurname'', ''TestCompany'')') AT EXTSYS
everything goes ok.
It seems some sort of COMMIT problems with OPENQUERY or caching (?) with OPENQUERY that EXECUTE bypasses.
EXECUTE is not a valid option because I would like to use OPENQUERY to prevent Injection and/or value cleaning.
Can anyone explain me if it's possible to correct my OPENQUERY syntax to avoid the "foreign key" error?
Thanks a lot!
March 6, 2015 at 6:52 am
I think it has to do with the way MySQL paticipates to distributed transactions. Did you check the rows are there after the first insert (with another OPENQUERY statement in the same transaction)?
-- Gianluca Sartori
March 6, 2015 at 7:05 am
Yes, with a SELECT * FROM OPENQUERY(EXTSYS, 'SELECT * FROM EntityMaster WHERE id=131177') put between first and second INSERT, I can see inserted row (that makes me crazy :crazy: ).
Even after error is thrown, row on EntityMaster is still there, via OPENQUERY and via MySQL Manager directly connected on MySQL Server.
March 6, 2015 at 7:18 am
I suppose that foreign key constraints on MySQL do not fire in the same transaction context as the distributed transaction. That would be very hard to demonstrate though.
-- Gianluca Sartori
March 6, 2015 at 7:28 am
My initial suspect was that OPENQUERY delays or uses a local cache. When I found that Master record was inserted in MySQL table I cannot explain why foreign key check throw error...
April 14, 2015 at 5:59 am
did you ever find an explanation for this issue?
April 14, 2015 at 6:35 am
No. Nothing leads me to a valid answer. In the end, I've adopted the following syntax (a bit complex with lot of parameters)
SET @SQL_Cmd = 'EXECUTE (''INSERT INTO EntityMaster (id, createdtime, modifiedtime) VALUES (?, ?, ?)'', @pId, @pCreatedTime, @pModifiedTime) AT EXTSYS'
SET @ParList = '@pIdbigint, @pCreatedTime datetime, @pModifiedTime datetime'
EXEC sp_executesql @SQL_Cmd, @ParList, @pId = 'VALUE1', @pCreatedTime = '20150101', @pModifiedTime = '20150101'
After this command, record is correctly inserted and transaction fully committed. So I suppose it's something related to remote transaction...
Hope this helps 😉
April 14, 2015 at 5:04 pm
Never mind, in my case the parent table was MyISAM.. duhhh.. thanks anyway.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply