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


MySQL as linked server


MySQL as linked server

Author
Message
agnew-1142997
agnew-1142997
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 44
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..
ballenger97
ballenger97
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
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.
agnew-1142997
agnew-1142997
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 44
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..)
ballenger97
ballenger97
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
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
agnew-1142997
agnew-1142997
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 44
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..
ballenger97
ballenger97
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
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...
ballenger97
ballenger97
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
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
agnew-1142997
agnew-1142997
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 44
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.
ballenger97
ballenger97
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
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...
agnew-1142997
agnew-1142997
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 44
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!
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