Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2010-08-29
12
Medium Priority
?
1,195 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

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 blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
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…

610 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