January 11, 2012 at 11:27 am
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.
January 12, 2012 at 5:29 am
Hi, you'll need to kill them from the MySQL side.
January 12, 2012 at 4:25 pm
Thanks Gazareth, but there is not active connection in MySQL server from SQL Sever. I checked it with Mysql Workbench, in admin Server Status.
January 13, 2012 at 3:10 am
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
January 13, 2012 at 2:56 pm
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.
August 6, 2012 at 12:29 pm
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...
August 7, 2012 at 6:52 am
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...
August 7, 2012 at 6:58 am
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)
?
August 7, 2012 at 7:04 am
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
August 7, 2012 at 7:17 am
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..
August 7, 2012 at 7:32 am
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.
August 7, 2012 at 7:35 am
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..)
August 7, 2012 at 7:40 am
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
August 7, 2012 at 7:55 am
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