Problem with using SQL Server linked server to MySQL database
-
I have a MS SQL Server that does a lot of data manipulation and staging and then I need to push those changes to a MySQL database. I've had a lot of problems using a linked server to this MySQL database but the particular problem I'm having today is related to a delete statement.
I had some issues with getting errors when running a DELETE statement that ends up deleting 0 rows based on the where clause. So I had to write what I consider to be a sloppy check because I was getting the same error when using a IF EXISTS statement. So in my below example I am certain that I will be deleting something (in my test case it was 16 records).
IF EXISTS (select * from #exists_check)BEGIN
DELETE FROM MYSQL_LINKEDSVR...product
WHERE product_id IN (SELECT product_id from #Discontinued_Products)
END
I receive this error:
OLE DB provider "MSDASQL" for linked server "MYSQL_BFGLOCKER" returned message "Data provider or other service returned an E_FAIL status.".
Msg 7330, Level 16, State 2, Line 3
Cannot fetch a row from OLE DB provider "MSDASQL" for linked server
Now, I've seen this error a lot in a lot of other situations so it appears to be a generic error to me? Does this mean anything to anyone or could anyone explain why this legitimate delete statement is throwing and error when the several (identical except for the table name) statements run successfully?
NOTE: I get the same error when trying to update the same table.
IF EXISTS (select * from #exists_check)BEGIN
UPDATE MYSQL_LINKEDSVR...product
SET quantity = 0
WHERE product_id IN (SELECT product_id from #OutOfStock_Products)
END
Thanks,
Derik Hammer
Best Regards,
Derik Hammer
www.sqlhammer.com -
Could you please script out your Linked Server definition and post it here minus any sensitive information? Also, which MySQL driver, type and version, are you using?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
As seen below I'm using "MySQL ODBC 5.1 Driver".
The only things changed below is the actual Linked Server name (to match example scripts), domain, database name, and username/password.
/****** Object: LinkedServer [MYSQL_LINKEDSVR] Script Date: 01/13/2013 15:43:10 ******/EXEC master.dbo.sp_addlinkedserver @server = N'MYSQL_LINKEDSVR', @srvproduct=N'MySQL', @provider=N'MSDASQL',
@provstr=N'DRIVER={MySQL ODBC 5.1 Driver};SERVER=mydomain.com;Port=3306;USER=Manager;PASSWORD=SuperSecretPassword;OPTION=3;DATABASE=MyDatabase'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MYSQL_LINKEDSVR',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
GO
EXEC master.dbo.sp_serveroption @server=N'MYSQL_LINKEDSVR', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MYSQL_LINKEDSVR', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'MYSQL_LINKEDSVR', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MYSQL_LINKEDSVR', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MYSQL_LINKEDSVR', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MYSQL_LINKEDSVR', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MYSQL_LINKEDSVR', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MYSQL_LINKEDSVR', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'MYSQL_LINKEDSVR', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'MYSQL_LINKEDSVR', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MYSQL_LINKEDSVR', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'MYSQL_LINKEDSVR', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'MYSQL_LINKEDSVR', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
Best Regards,
Derik Hammer
www.sqlhammer.com -
I am unable to recreate the scenario, i.e. I am able to delete from remote tables referenced by 4-part naming using an IN with a temp table to achieve a JOIN even when no rows are affected on the MySQL side.
Is there anything different about the product table? Maybe it is a storage engine thing on MySQL.
Also, what are your MSDASQL provider settings?
I have the following checked:
Nested queries
Level zero only
Allow inprocess
Supports 'like'
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
I'm not sure if this was the information that you sought.
Best Regards,
Derik Hammer
www.sqlhammer.com -
I meant in the Linked Server node in SSMS Object Explorer, under Providers, right click MSDASQL and go to Properties. What do you have setup for options?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
I apologize for the delayed response I've been ill.
My MSDASQL settings are the same as yours.
Best Regards,
Derik Hammer
www.sqlhammer.com -
How about the storage engine (e.g. InnoDB) of the tables you're having trouble with? Any patterns there?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
To be frank, I'm pretty good with MSSQL but I'm basically a MySQL noob.
If you could point out anything that I should be looking for I would gladly perform the investigation but this MySQL database is hosted with a 3rd party. I have rights to it but I was not the administrator that set it up and the schema was built by OpenCart so I wasn't even involved in its design.
Best Regards,
Derik Hammer
www.sqlhammer.com -
Do you have a copy of MySQL Workbench installed? If you use it to connect to the database and navigate to the table there will be an indicator on one of the properties pages showing which storage engine the table is using (InnoDB, MyIsam, etc.). It's a longshot that something like a diff in the storage engine would matter in this instance but I have seen sillier things with MySQL.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
MyISAM is the storage engine being used.
Best Regards,
Derik Hammer
www.sqlhammer.com -
MyISAM is the engine.
Best Regards,
Derik Hammer
www.sqlhammer.com -
Is that different from the other tables that are working?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
They are all the same but I've tried this same setup on 10 different tables and receive the error in all cases. I do not have a successful case to compare against.
Best Regards,
Derik Hammer
www.sqlhammer.com -
All I can say is maybe start tracing activity on the MySQL side to see if you can capture what is coming across when it fails and compare that to what is sent when it succeeds, in an attempt to determine which system may be dropping the ball. You may also be able to log the activity by using the logging mechanisms provided by ODBC Data Source in Windows.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply