SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


MySQL as linked server


MySQL as linked server

Author
Message
obregonjose3
obregonjose3
SSC-Enthusiastic
SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)

Group: General Forum Members
Points: 111 Visits: 73
Hi everbody

I need some suggestions


I Have a sql server(2005) default instance in Windows Server 2003, I need to connect it to a MySQL server as a linked server. I dwonloaded mysql-connector-odbc-5.1.9-win32.msi and mysql-connector-odbc-5.1.9-winx64.msi. The second one is not supported by my server, so I use the first one. I added a System DSN for MySQL server. Testing the DSN is ok.

But when I configure the Linked sever, the windows "New Linked Server" never finishes when click "Ok". In the activity monitor are there some process related with ODBC (54,56,59). Then I try to kill them with "KILL #SPID", but it responses with this: "SPID 56: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds." and never finishes.

Some details:

kill 54


SPID 54: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.



select * from sys.sysprocesses where spid in (54, 56, 59)


SPID
54
56
59

KPID
9380
11248
11880
waittime
80670078
80865218
80696796
LastWaittype
OLEDB for all

cmd
KILLED/ROLLBACK
KILLED/ROLLBACK
KILLED/ROLLBACK

hostprocess
7712
10308
7712

blocked
0 for all


Some people suggested me I must restart the service, but I don't want to restart the services because is a production server. Another suggestion??

thanks and regards.
Gazareth
Gazareth
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4707 Visits: 5916
Hi, you'll need to kill them from the MySQL side.
obregonjose3
obregonjose3
SSC-Enthusiastic
SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)

Group: General Forum Members
Points: 111 Visits: 73
Thanks Gazareth, but there is not active connection in MySQL server from SQL Sever. I checked it with Mysql Workbench, in admin Server Status.
Gazareth
Gazareth
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4707 Visits: 5916
I used a script to create the linked server rather than the GUI, if that helps.

Mind if I ask what connection string you're using?
Thanks
obregonjose3
obregonjose3
SSC-Enthusiastic
SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)

Group: General Forum Members
Points: 111 Visits: 73
Thanks

I followed this tutorial http://dbperf.wordpress.com/2010/07/22/link-mysql-to-ms-sql-server2008/

I used this tutorial in my local machine (SQL Server 2008 R2 SE) and everything is ok, with both of modes, with scripts and GUI. But in the server does not function (SQL Server 2005 SE).

With Scripts:


USE [master]
GO
EXEC master.dbo.sp_addlinkedserver
@server = N'MYSQL'
, @srvproduct=N'MySQL'
, @provider=N'MSDASQL'
, @datasrc=N'MySQL'
, @provstr=N'DRIVER={MySQL ODBC 5.1 Driver};SERVER=172.16.1.82;PORT=3306;DATABASE=repltest; USER=user;PASSWORD=password;OPTION=3;'
, @catalog=N'repltest'

GO
EXEC master.dbo.sp_serveroption @server=N'MYSQL', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MYSQL', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'MYSQL', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MYSQL', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MYSQL', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'MYSQL', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'MYSQL', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'MYSQL', @optname=N'use remote collation', @optvalue=N'true'
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = N'MYSQL'
, @locallogin = NULL
, @useself = N'False'
, @rmtuser = N'user'
, @rmtpassword = N'password'
GO



So when query a table, never responds.
ballenger97
ballenger97
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 20
I have exactly the same problem, SPID and all. Followed the same tutorial, and tried another one with the same result as well.

working with sql 2008 r2 64x. Doesn't seem like many others are running into this problem. The ODBC test connection works fine. Really left scratching my head on this one...
Scott D. Jacobson
Scott D. Jacobson
Right there with Babe
Right there with Babe (758 reputation)Right there with Babe (758 reputation)Right there with Babe (758 reputation)Right there with Babe (758 reputation)Right there with Babe (758 reputation)Right there with Babe (758 reputation)Right there with Babe (758 reputation)Right there with Babe (758 reputation)

Group: General Forum Members
Points: 758 Visits: 1018
Have you guys had a look at this post?
ballenger97
ballenger97
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 20
unfortunately, that post isn't much help. It's actually less detailed than the other 2 tutorials already referenced here. And further, it doesn't describe the symptoms that we are experiencing. Following these instructions we are seeing the the SPID sits in an endless state when SSMS goes to test the linked server connection. Killing the SPID then even puts the SPID in an endless KILLED/ROLLBACK state. Clearly, this is either some kind of bug in MSSQL Server, or the MySQL ODBC driver. Either way, there doesn't seem to be a whole lot of people that have encountered this as far as my searching can tell...
agnew-1142997
agnew-1142997
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 44
hhmm.. I'm a MS-SQL *AND* MySQL admin...

can you connect via any "mysql" method to the MySQL db? (Like, mysql workbench, mysql command line client, or so forth)

?
ballenger97
ballenger97
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 20
Yes, I've got workbench setup for server administration and development. Connecting fine added my user and granted permissions to it for the SQL server IP there which I used to create my ODBC connection on the MSSQL server which also tests successfully.

I read something suggesting PREFETCH having a bug or something, so I tried:

DRIVER={MySQL ODBC 5.1 Driver};SERVER=myservername;PORT=4309;DATABASE=mydatabasename; PREFETCH=0

Didn't seem to help.


Also tried executing this below. Catch never happens, test just spins and spins. Then like I mention, after a few hours of waiting for completion and giving up, I try to kill the SPID and it just sits in a running task state with command "KILLED/ROLLBACK"

begin try
exec sp_testlinkedserver N'linkedServerName'
end try
begin catch
select error_number() as errornumber
,error_severity() as errorseverity
,error_state() as errorstate
,error_line() as errorline
,error_message() as errormessage
end catch
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search