Dreamweaver issue creating MySQL connection..

[vb2talent]

My first post to the forum so hopefully this is an easy problem to resolve.

While creating a new site in CS6 through the "Manage Sites" interface I'm able to create and connect to my webserver without issue so the FTP is working as expected. I've also checked that this connection will be for REMOTE and TEST and I realize this is not an effective way to do this but for now it will have to suffice. My problem seems to be while trying to create a connection to the database through the MySQL Connection popup. Supplying my connection information gives me the following message:

The same credentials (username/pass) can be used via PHPMyAdmin without any issue which leads me to believe something else is a miss. So I'm trying to narrow down the possible causes of this issue and could use some assistance.

I have a few questions if I may about the connection popup.

1-when server information can I use a hostname or does it have to be an IP and should it contain the port number the MySQL server is running on?

2-if the server is running on SSL does that create issues for the connection?

3-is there an actual log being created of the connection attempts being made while choosing the "Select" button?

Thanks for any assistance you could supply.

[Paul-M]

I'm assuming here you are trying to connect to MySQL at your web host remotely?

What have you got in the MySQL Server field in Dreamweaver?

Also, you may want to check with your host that they allow remote connections to MySQL, most do not but if you have CPanel for example there is usually in the DB managenet section of CPanel a section called 'Remote MySQL' which you would need to add your IP to.

[vb2talent]

Thanks for the reply.

I am trying to remotely connect but have also tried to connect via localhost with a version of MySQL locally installed with no success.

I have tried a number of combinations with no success but in general I've tried both the hostname or IP address of the remote MySQL server with and without the port number.

test

x.x.x.x:3306

myusername

mypassword

test

x.x.x.x

myusername

mypassword

or trying to use also the localhost version of MySQL installed with similar results

test

localhost

myusername

mypassword

[Ben M · Accepted solution]

Most webhosts, by default, restrict access to the SQL server to either local requests on the server-side, "localhost", or remote requests from a particular IP address. Have you ensured on the hosting side that your own IP address is allowed to access the server remotely via the account you are trying to login with.

[Paul-M]

Do you have a web hosting conrtrol panel? CPanel for example?

If not contact your web host and ask them to allow remote connections to MySQL from your IP.

If you can't connect to a local setup of MySQL, there's some set up isse, is MySQL definately running? Whats your loal setup, is it WAMP or some other package?

Also in your screenshots, have you left the databse name empty when attempting to connect to remote database?

[Paul-M]

.....Also on your local set up of MySQL have you set up the user with the desired password? If no user has been been set up, try setting  username to root and leave the password empty and see if that connects to MySQL locally. IF it doesn't then its likely MySQL is not running.

[osgood_]

Its possible that the code Dreamweaver creates is out_of_date and is using mysql (deprecated) to connect to your server which uses more up-to-date functionality - mysqli or PDO.

Get some simple mysqli or PDO connection code by Googling for it, it will only be a couple of lines.

Add the code to a .php page and view the page via a browser.

If that connects then its a problem with Dreamweaver.

Go to the url below, copy the bit of code into a .php file, obviously change the connection details. View in browser. If the page fails to connect you should see an error message.

PHP mysqli_connect() Function

[Nancy OShea]

#1 You should NOT be using the deprecated server behaviors in DW CS6. SBs were removed from DW CC for a reason.  The code is not secure, it's outdated and the old MySQL connection strings won't work on server's running PHP 7 or higher.

#2  Create a .php file called info.php with the following code.  Upload to your remote server to test.  This will tell you whicfh version of PHP your server has.

<?php

$con = mysqli_connect("localhost","your_username","your_password","your_database_name");

// Check connection

if (mysqli_connect_errno())

  {

  echo "Failed to connect to MySQL: " . mysqli_connect_error();

        }

        else echo "Successfully connected, happy coding!"

?>

<?php phpinfo() ?>

Ideally, your remote server and local testing server should contain the same versions of MySQL and PHP.

[vb2talent]

I wasn't able to view the thread earlier.. thanks for all the reply's.

Have you ensured on the hosting side that your own IP address is allowed.

I have a QNAP server that I have running MySQL on behind a router so the IP it should be seeing is a 192.x.x.x address which is currently permitted. I may be incorrect in this assumption and need to add my external IP address since all traffic goes out and then comes back in.

It is currently running an older version of MySQL (5.1.73) but this is being used for testing purposes only.

If you can't connect to a local setup of MySQL, there's some set up isse, is MySQL definately running? Whats your loal setup, is it WAMP or some other package?

I simply installed MySQL on my Windows desktop and am currently able to login and run queries, setup tables, etc.

Also in your screenshots, have you left the databse name empty when attempting to connect to remote database?

.....Also on your local set up of MySQL have you set up the user with the desired password?

I've tried both options, using the "Select" and adding the actual DB name both with similar results.

Its possible that the code Dreamweaver creates is out_of_date and is using mysql (deprecated) to connect to your server which uses more up-to-date functionality - mysqli or PDO.

So this is interesting, is CS6 simply trying to use the connect information from some sort of PHP page to try and connect? Is it possible to view that information?

Get some simple mysqli or PDO connection code by Googling for it, it will only be a couple of lines. Go to the url below, copy the bit of code into a .php file, obviously change the connection details. View in browser. If the page fails to connect you should see an error message.

I will try this as soon as possible.

#1 You should NOT be using the deprecated server behaviors in DW CS6. SBs were removed from DW CC for a reason.  The code is not secure, it's outdated and the old MySQL connection strings won't work on server's running PHP 7 or higher.

Not using it is creeping up fast on my list, thanks for highlighting.