?
Solved

ASP MySQL ODBC Connection not working on Windows Server 2008 64bit

Posted on 2010-08-29
12
Medium Priority
?
1,188 Views
Last Modified: 2012-05-10
Hi guys,

I'm moving websites from an old server to a new one, and I've hit a snag. One of our client websites is ASP based and uses a MySQL database. However, it simply refuses to connect to the database and throws up this error:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified


The code I'm using to call it is this:

Set contentConnect = Server.CreateObject("ADODB.Connection")
contentConnect.mode = 3 ' adModeReadWrite
contentConnect.open "Driver={MySQL ODBC 5.1 Driver}; Server=localhost; Option=16387; Port=3306; Database=databasename; Uid=Username; Pwd=password;"


I've also tried this:

Dim sConnection, objConn , objRS, constring, baseurl, uploadpath
ConString = "DRIVER={MySQL ODBC 5.1 Driver}; SERVER=localhost; DATABASE=Databasename; UID=Username;PASSWORD=Password; OPTION=3"


I've checked both the 32 and 64bit ODBC connectors and it exists in both instances. The name in the code matches the driver name in ODBC.

I've removed and re-installed the 5.1 ODBC driver

I've tried turning off 32bit applications in the App pool.

I've tried banging my head on the desk.

I've looked through the solutions on here but can't get any of them to work.

Any help would be very much appreciated!
0
Comment
Question by:Acteon
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 2
12 Comments
 
LVL 8

Expert Comment

by:jessmca
ID: 33552561
Unless you have also copied the MySQL database to the new server it wont be local host in the connection string but should be the IP address of the previoius computer running MySQL
0
 

Author Comment

by:Acteon
ID: 33552575
I have copied the database across, I also tried replacing localhost with the old IP, but received the same error unfortunately.

I'm pretty certain it's the ODBC connection that's failing.

Thanks for the speedy response though!
0
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 33553948
You need to make sure the 'mysqld' daemon is listening on port 3306.  You can use Sysinternals TCPView to check that.  Here's a page I use to check my connection strings although it does match what you posted.  Did you make sure the local MySQL database had the appropriate usernames and passwords when you transferred over from the other machine?  Is it still running on the old machine?
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

Author Comment

by:Acteon
ID: 33554065
It's still running on the old machine, and MySQL works for php-based connections (we have a few vBulletin forums already on there).

It only affects ASP sites using ODBC... The username/password has also been copied and no dice...
0
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 33554167
For what it's worth, here's the ASP code I use that works on Godaddy IIS7 and on Win2k IIS5.  I'm connecting to a remote MySQL in both cases.  And the same code works with 'localhost' on WinXP IIS5.1 where the MySQL server is here.  It uses an earlier version of the MySQL ODBC connector but that's the only difference I can see except for the 'mode 3'.
' Create ADO Connection Component to connect to database

connectstr = "Driver={MySQL ODBC 3.51 Driver};SERVER=" & db_server & ";DATABASE=" & db_name & ";UID=" & db_username & ";PWD=" & db_userpassword

Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open connectstr

Open in new window

0
 
LVL 8

Accepted Solution

by:
jessmca earned 750 total points
ID: 33554193
Make sure to use a system DSN, if it is a user DSN then make sure the userif in your connection string has suitable permissions to access it or you will get that same error
0
 

Author Comment

by:Acteon
ID: 33591645
http://bugs.mysql.com/bug.php?id=56233

This appears to be a bug with MySQL itself, has anyone else encountered this? And is there a way to connect with ASP without using DSN or ODBC? (Have to admit I haven't found one and it kinda defeates the object, but I'm looking for a workaround now...)
0
 
LVL 84

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 750 total points
ID: 33592042
If you read to the end of that article, one user said he was able to remove 5.1.7 and install 5.1.6 and have it work.
0
 

Author Comment

by:Acteon
ID: 33592213
I was using 5.1.6 with the same error and tried 5.1.7 before reading that article - same issue with both versions sadly.

I suppose I could try version 5.1.5 if I can find it. I'm pretty annoyed that MySQL would release any version with a bug this major.
0
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 33592643
The 3.51 version is still available for x86 64-bit on this page: http://www.mysql.com/downloads/connector/odbc/3.51.html#downloads
0
 

Author Comment

by:Acteon
ID: 33691115
I managed to get it working with 5.1.6 after numerous reboots and much swearing.

Since everyone helped I'm going to try to apportion points evenly.
0
 

Author Closing Comment

by:Acteon
ID: 33691141
Basically go back to version 5.61 and reboot a few times to ensure the new settings take effect.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question