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!
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!
ASKER
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.UnknownHostExcep
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.UnknownHostExcep
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).
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.UnknownHostExcep
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.UnknownHostExcep
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).
ASKER
For host, I have tried (local), local, (localhost) and localhost ... all with the same error, as posted above.
ASKER
Also, leaving username and password blank with host = (local) gives the same error.
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.UnknownHostExcep
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?
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.UnknownHostExcep
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?
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
ASKER
I found a version 3.0.17 ...
ASKER
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 ???
http://lists.mysql.com/commits/2814
What I can't tell is if this was incorporated into 3.1.12 ???
ASKER
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).
ASKER
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.
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.
ASKER
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:330
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.1
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:33
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".
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:330
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.1
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:33
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".
Sorry I was out during your last series of posts :(
But I'm glad you got it and thanks for the points!
But I'm glad you got it and thanks for the points!
ASKER
You set me in the right direction ...thanks.
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