Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

MySQL as linked server Expand / Collapse
Author
Message
Posted Wednesday, January 11, 2012 11:27 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, December 6, 2013 10:37 AM
Points: 83, 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.


Post #1234235
Posted Thursday, January 12, 2012 5:29 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:54 AM
Points: 2,059, Visits: 3,581
Hi, you'll need to kill them from the MySQL side.
Post #1234676
Posted Thursday, January 12, 2012 4:25 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, December 6, 2013 10:37 AM
Points: 83, 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.

Post #1235303
Posted Friday, January 13, 2012 3:10 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:54 AM
Points: 2,059, Visits: 3,581
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
Post #1235485
Posted Friday, January 13, 2012 2:56 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, December 6, 2013 10:37 AM
Points: 83, 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.
Post #1235989
Posted Monday, August 6, 2012 12:29 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 30, 2012 10:16 AM
Points: 14, 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...
Post #1340822
Posted Monday, August 6, 2012 1:07 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 8:13 PM
Points: 356, Visits: 921
Have you guys had a look at this post?
Post #1340848
Posted Tuesday, August 7, 2012 6:52 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 30, 2012 10:16 AM
Points: 14, 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...
Post #1341208
Posted Tuesday, August 7, 2012 6:58 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 24, 2014 2:00 PM
Points: 10, Visits: 41
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)

?

Post #1341212
Posted Tuesday, August 7, 2012 7:04 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 30, 2012 10:16 AM
Points: 14, 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
Post #1341221
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse