MySQL as linked server

  • 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.

  • Hi, you'll need to kill them from the MySQL side.

  • Thanks Gazareth, but there is not active connection in MySQL server from SQL Sever. I checked it with Mysql Workbench, in admin Server Status.

  • 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

  • Thanks

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

    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.

  • 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...

  • Have you guys had a look at this post?

  • 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...

  • 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)

    ?

  • 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

  • hhmm... rumble ruminate....

    what versions of MySQL, and what versions of the odbc driver?

    I've seen STRANGE cases where upgrading a 0.001 version helped..

  • MySQL results of SHOW VARIABLES LIKE "%version%";

    protocol_version: 10

    version: 5.0.84sp1-enterprise-nt-log

    version_comment: MySQL Enterprise Server - Pro Edition (Commercial)

    version_compile_machine: ia32

    version_compile_os: Win32

    Microsoft SQL Server Standard Edition (64-bit) 2008

    Microsoft SQL Server version listed in Server Properties shows: 10.50.2500.0

    both servers are Windows 2008 R2 64 bit

    Unfortunately, I don't think I'll be able to do an upgrade on the MySQL if that's the case. It's a database of a third party product that we've licensed. So I think I'd have to talk to the vendor before proceeding with any upgrades to the MySQL database.

  • hhm.. what's the client version, and the odbc driver version?

    (by client, the pgm that tested the connection, like the mysql command at the dos prompt, or, workbench, or perl, or whatever..)

  • MySQL workbench 5.2.40 CE Revision 8790

    installed with mysql-workbench-gpl-5.2.40-win32.msi

    ODBC Driver version displays MySQL ODBC 5.1 Driver

    installed from mysql-connector-odbc-5.1.11-winx64.msi

    both were downloaded about a week ago

  • hhmm... not knowing the details of that connection, (I'm lead mysql, 2nd string ms-sql, and fellow who wipes down the desk of the Oracle fellows)

    in my experience, i'd try:

    a new 32-bit driver,

    or try a 32 bit 5.0.xxx driver...

    i gotta go back to work, so i'll take a look at this tomorrow... hope that's ok..

Viewing 15 posts - 1 through 15 (of 26 total)

You must be logged in to reply to this topic. Login to reply