• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 425
  • Last Modified:

How do I configure a data source in ColdFusion administrator for MySQL?

I've been using Microsoft Access db's and have had no trouble setting up connections in ColdFusion. Now I'm trying to switch to MySQL and I'm having trouble setting up the connection.
My database is named "CWAmembers" so there is a "db.opt" file in "c:\program files\MYSQL\MYSQL Server 5.0\data\cwamembers".
So first question is: in Coldfusion admin, what do I use as the "Database"? Do I use the path "c:\program files\MYSQL\MYSQL Server 5.0\data\cwamembers" or do I use the file "c:\program files\MYSQL\MYSQL Server 5.0\data\cwamembers\db.opt"?

Next question: My MySQL server is on my local pc ... so what do I use for "Server"? I've tried "localhost", "(localhost)", "local" and "(local)" ... I think "localhost" is correct but I'm not sure.

And finally what do I use for username and password. In MySQL, username is "root" and password, is, say, "xyzabc". That's what I am trying to use.

With "Server" set to "localhost" (port = 3306), "Database" set to either the path or the filename, username set to "root" and password set to "xyzabc", I get this error:

Connection verification failed for data source: connCWAmembers
java.sql.SQLException: Communication failure during handshake. Is there a server running on localhost:3306?
The root cause was that: java.sql.SQLException: Communication failure during handshake. Is there a server running on localhost:3306?

If I leave the username and password blank, I get this error:

Connection verification failed for data source: connCWAmembers
java.sql.SQLException: Invalid authorization specification: Access denied for user 'nobody'@'localhost' (using password: NO)
The root cause was that: java.sql.SQLException: Invalid authorization specification: Access denied for user 'nobody'@'localhost' (using password: NO)

If I leave username blank and set password to "xyzabc", I get this error:

Connection verification failed for data source: connCWAmembers
java.sql.SQLException: Invalid authorization specification: Access denied for user 'nobody'@'localhost' (using password: YES)
The root cause was that: java.sql.SQLException: Invalid authorization specification: Access denied for user 'nobody'@'localhost' (using password: YES)

The data source name "connCWAmembers" is getting created, but I can't verify the connection.
HELP!



0
alicia1234
Asked:
alicia1234
  • 11
  • 4
1 Solution
 
dgrafxCommented:
I'm not totally familiar with MySql, but in general:
Open your mysql control panel (whatever it's called) and create your database.
Then open odbc on your sever from windows control panel/administrative tools and create datasource from database you just created.
Open coldfusion administrator and create datasource - in name enter datasource name and for driver select MySql - click add and fill out the fields on next page.
Database: is just the name of the database as registered in mysql - not the path
Server: name of server like for localhost would be (local) include parenthesis
and Port : your port #
username : depends on system - I believe you can use sa
password : and admin password
0
 
alicia1234Author Commented:
In the Control Panel under ODBC ... when I try to add a data source, there is no choice in the list for MySQL! There are drivers for Access, dBase, Excel, Paradox, Visual FoxPro, ODBC for Oracle, & SQL Server. Is this part of the problem?

Besides ... when I created the datasources before for my Access databases, I didn't need to go in to Control Panel/ODBC ... I just did it from the ColdFusion Administrator.
SO I think I am still looking for the right combination of database, server, username and password that I am supposed to use there.

I just tried:
Database = cwamembers (that's the name it was created with in MySQL)
host = (local)
username = root
password = xyzabc

I got this error:
Connection verification failed for data source: connCWAmembers
java.sql.SQLException: Cannot connect to MySQL server on (local):3306. Is there a MySQL server running on the machine/port you are trying to connect to? (java.net.UnknownHostException)
The root cause was that: java.sql.SQLException: Cannot connect to MySQL server on (local):3306. Is there a MySQL server running on the machine/port you are trying to connect to? (java.net.UnknownHostException)

Then I tried:
I just tried:
Database = cwamembers (that's the name it was created with in MySQL)
host = localhost
username = root
password = xyzabc

And got this error:
Connection verification failed for data source: connCWAmembers
java.sql.SQLException: Communication failure during handshake. Is there a server running on localhost:3306?
The root cause was that: java.sql.SQLException: Communication failure during handshake. Is there a server running on localhost:3306?

I double-checked and "MySQL" is an active service (control panel > admin tools > services).
0
 
alicia1234Author Commented:
For host, I have tried (local), local, (localhost) and localhost ... all with the same error, as posted above.
0
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
alicia1234Author Commented:
Also, leaving username and password blank with host = (local) gives the same error.
0
 
dgrafxCommented:
ok - the problem, i believe, revolves around that you have no option for mysql when creating datasource - so what do you use?
anything besides mysql will fail!
yet you obviously have mysql installed or you wouldn't have been able to create database - right?

This is the deal:
The root cause was that: java.sql.SQLException: Cannot connect to MySQL server on (local):3306. Is there a MySQL server running on the machine/port you are trying to connect to? (java.net.UnknownHostException)

Important:
Are you developing on a network? or is this just one pc?
Like if you are on a network - and on that network you have coldfusion and web server installed on one machine - then (without getting into unneccessary details for your situation) you need to have mysql installed on the same machine.

Try opening odbc on the machine that coldfusion is installed on. Try creating a datasource - is mysql in the list of drivers?
0
 
alicia1234Author Commented:
It's just one pc ... I work at home for myself, and I am developing a commercial website for myself. Everything is on this one pc ... MySQL, ColdFusion MX developer edition ... everything.
I can use MySQL Administrator and MySQL Query Browser to access my datbase.

>>Cannot connect to MySQL server on (local):3306. Is there a MySQL server running on the machine/port you are trying to connect to? <<

How do I check this? How can I tell if MySQL server is running on the machine/port that I am trying to connect to?

Also ... I did note that in ColdFusion MX ... the list of databases to connect to ... lists MySQL vsn 3.1. I'm running V5. I found an article here:
http://www.adobe.com/cfusion/knowledgebase/index.cfm?id=tn_19170
about updating the JDBC driver for newer versions of MySQL ... should I do that?



0
 
dgrafxCommented:
OH - yeah
I use ms sql server and had to update the jdbc driver when I went to cf 7
I wasn't thinking of that :(
I'll bet it's worth a try.

Also - did you ever open windows odbc and see if there is a mysql option when you try to create a datasource?
0
 
alicia1234Author Commented:
Yes I did check the odbc data sources in Windows ... see the beginning of my 9:36am post today. There is nothing for MySQL.

Regarding the JDBC driver ... looking at the post that I provided the link for ... near the bottom it says that "Connector/J version 3.1 release 3.1.11 and higher cannot be used with ColdFusion MX 7 because of mySQL Bug 13469. Connector/J 3.1.10 should be used instead of the current release until this MySQL bug is fixed. All Connector/J version 3.0 releases and all (currently beta) version 3.2 releases work with ColdFusion MX 7".

When I go to download the Connector/J, the only releases available are 3.1.12 and the 5.0 beta ... I searched the list of fixes for 3.1.12 and for 5.0 and did not find any explicit reference to fixing bug #13469. So which version should I use?
0
 
alicia1234Author Commented:
I found a version 3.0.17 ...
0
 
alicia1234Author Commented:
I also found a patch for 3.1 here:
http://lists.mysql.com/commits/2814
What I can't tell is if this was incorporated into 3.1.12 ???
0
 
alicia1234Author Commented:
I found that version 3.1.12 is dated 11/30/2005, and the commit for the patch was dated 2/17/2006. So this tells me that the fix is not in 3.1.12. So I think I should stick with version 3.0.17 (since I can't find version 3.2 anywhere).
0
 
alicia1234Author Commented:
Well, I downloaded Connector/J version 3.0.17 and followed the instructions here:
http://www.adobe.com/cfusion/knowledgebase/index.cfm?id=tn_19170

When I clicked on "submit", I got this error:

Connection verification failed for data source: MySQL_test
java.sql.SQLException: No suitable driver
The root cause was that: java.sql.SQLException: No suitable driver

I'm upping the points on this question to 500.
0
 
alicia1234Author Commented:
Got it!!!!   ;-)
In the instructions that I followed (mentioned in my last post) ... it told what to enter in the JDBC URL field ... but some of the text was regular and some was greyed out ... so at first I just used the greyed out stuff:
//localhost:3306/test

But then I read it more carefully and realized that I was supposed to use:
jdbc:mysql://localhost:3306/test

So, to make a long story short, here is the FINAL SOLUTION:
Remember: my MySQL database name is "cwamembers"; my login for MySQL is "root", "xyzabc".
And I was basically following instructions from this document:
http://www.adobe.com/cfusion/knowledgebase/index.cfm?id=tn_19170

1. Downloaded Connector/J version 3.0.17 from the mySQL site (I downloaded the zip file)
2. Unzipped the file.
3. Copied file mysql-connector-java-3.0.17-ga-bin.jar to c:\CFusionMX7\wwwroot\WEB-INF\lib
4. Rebooted my PC to restart ColdFusion MX.
5. In ColdFusion MX Administrator (everything below is case sensitive):
    1. Went to datasources, and entered datasource name as "connCWAmembers" and selected driver "Other", and clicked "Add".
    2. For JDBC URL, entered  "jdbc:mysql://localhost:3306/CWAmembers
    3. For Driver class, entered "com.mysql.jdbc.Driver"
    4. For Driver name, entered "MySQL JDBC" (you can enter anything you want here)
    5. For username, entered "root"
    6. For password, entered "xyzabc"
    7. Clicked "submit".
0
 
dgrafxCommented:
Sorry I was out during your last series of posts :(
But I'm glad you got it and thanks for the points!
0
 
alicia1234Author Commented:
You set me in the right direction ...thanks.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

  • 11
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now