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 Tuesday, August 7, 2012 7:17 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, March 8, 2013 6:40 AM
Points: 10, Visits: 40
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..
Post #1341230
Posted Tuesday, August 7, 2012 7:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 30, 2012 10:16 AM
Points: 14, Visits: 20

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.
Post #1341252
Posted Tuesday, August 7, 2012 7:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, March 8, 2013 6:40 AM
Points: 10, Visits: 40
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..)

Post #1341259
Posted Tuesday, August 7, 2012 7:40 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 30, 2012 10:16 AM
Points: 14, Visits: 20
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
Post #1341263
Posted Tuesday, August 7, 2012 7:55 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, March 8, 2013 6:40 AM
Points: 10, Visits: 40
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..
Post #1341280
Posted Tuesday, August 7, 2012 1:40 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 30, 2012 10:16 AM
Points: 14, Visits: 20
Thanks for trying to help. Unfortunately I cannot install a 32bit driver on a windows 2008 R2 64 windows box successfully. The driver doesn't even show up as an option to create the System DSN. Back to the drawing board...
Post #1341512
Posted Wednesday, August 8, 2012 1:17 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 30, 2012 10:16 AM
Points: 14, Visits: 20
I suspect the problem is somehow related to this bug
http://bugs.mysql.com/bug.php?id=63386
My version is 5.0.8 enterprise, and this bug was reported in version is 5.1.9
Seems like the MySQL database version just doesn't support what I'm trying to do. Has anyone else success in setting up a MySQL linked server at all with a version similar to what I've posted?

Also noticed this MS bug, EXACTLY the symptoms I'm seeing but with an Oracle ODBC driver instead
http://connect.microsoft.com/SQLServer/feedback/details/187192/openquery-to-linked-server-hangs-leaving-spid-with-open-tran-that-cannot-be-killed-then-templog-ldf-grows-without-limit-requires-sql-server-restart-on-production-servers
Post #1342161
Posted Wednesday, August 8, 2012 1:33 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, March 8, 2013 6:40 AM
Points: 10, Visits: 40
wow... you've been digging!!!

our version is 5.1.46-sp1 Enterprise,

fooey.. wish I could have helped more.. I've done ms connects to mysql before, but before this job, never ms-sql-server..

will keep my eyes open, tho..

since our servers are 64-bits, we're using all 64bit s/w..

J.
Post #1342176
Posted Wednesday, August 8, 2012 1:47 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 30, 2012 10:16 AM
Points: 14, Visits: 20
Thanks for the info, every little bit helps.

At this point I think I'm willing to fork up some $ and resolve this with a 3rd party solution. Assuming that will even work...





Post #1342185
Posted Wednesday, August 8, 2012 1:54 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, March 8, 2013 6:40 AM
Points: 10, Visits: 40
in the past, we (about 15 years ago) got a VMS odbc driver for ingres from openlink software.

now, i'm *sure* that there are better products out there. these guys bent over backwards for support, and we used their product for years.. even survived the y2k...

you mileage WILL vary..

i have no knowledge of any mysql links with them, but was happy with them in the distant past, when the pyramids were new...

let me know how it goes!
Post #1342190
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse