I'm having problems with connecting to a remote database

Report · Jul 13, 2011

Hi,

I'm trying to set up a remote connection to a MySQL database. The remote/testing server is all set up ok, but when I come to add a mySQL connection it connects to the site then throws back a "HTTP Error Code 403 Forbidden". My hosting is set up to accept remote connections as long as username/password are correct (which they are!).

There's possibly a problem with the port which should be 3306, but I can't find anywhere to check/change this? Is this what I should be looking for or do I need to try another way of connecting?

Report · Jul 13, 2011

Sounds like it's not accepting remote access. Some hosts may require you to manually enable it. Why leads you to believe there's a port issue? If you're on windows, the firewall exception should already be taken care of.

Report · Jul 13, 2011

My hosting is set up to accept remote connections as long as username/password are correct (which they are!).

First off that is a very risky setup. I'm surprised your host would allow it. The server should only allow connections from specific addresses.

There's possibly a problem with the port which should be 3306, but I can't find anywhere to check/change this? Is this what I should be looking for or do I need to try another way of connecting?

When you enter in the database information, you would need to use the hostname:port if you are using a non-standard port for your SQL server. DW doesn't explicitly state this but that's the way it's done. Typically though you really should not work on remote data while working on a local site because any mistakes are made, there is no undo button. It's recommended that you download a copy of the database for use on your testing server and then connect to the testing server.

Report · Jul 14, 2011

Thanks Snakeyez, I've taken your advice and set up the testing server on my machine and copied the database - all working much more smoothly! Thanks!

Report · Jul 14, 2011

Ok, that brings up a question that I've had. I had set up a teasing server locally, but used the remote or live database during development. I did not install a local version of mysql. So when it's time to go live, what is the best process? The local database will have to be replicated on the remote server, and the connection script will have to be changed. Do you still create a connection in DW to the live database?

Report · Jul 16, 2011

Lon Winters wrote:
Ok, that brings up a question that I've had. I had set up a teasing server locally, but used the remote or live database during development. I did not install a local version of mysql. So when it's time to go live, what is the best process? The local database will have to be replicated on the remote server, and the connection script will have to be changed. Do you still create a connection in DW to the live database?

Lon,

It really depends on the situation. But as far as I am concerned the testing data should always be separate. I typically would never push the local data to the remote data through synchronization, only the other way around. Even if you are updating a skin for a CMS, I would export and import to the live data. Or if it's a new user login system, I would sync the data from the live data and if it works produce the same steps on the live data. Because in a working live environment whether it be a forum script or a CMS, the data on the live environment is changing so fast that it doesn't make sense to try and sync the test data up. And it could lead to unforeseen problems.

In the case of new data, let's say a new table with new functionality, I would export the SQL and then import to the server. Because you can mimic database versions and PHP versions I can't think of a case of where you would want to synchronize out to the live environment. If you have a case where you think it's needed post about it. I'd be interested in hearing about the case.

Report · Jul 16, 2011

That's a very good explanation and from that I do plan on using a local database during development. The data synchronization as explained makes perfect sense. The only situation I can think of where I may be tempted to deviate is more about the entire testing site, not just the database. When it comes time for the clients to test, what's the best option there? Open a port for them, but wouldn't that run rather slow?

Report · Jul 17, 2011

When it comes time for the client to test, opening up a testing environment is usually the best idea. Before handing it to a client I would freeze the database and make a copy and tell them to play around with it and let them know changes will not be saved. This helps you for a few reasons, first it helps you iron out bugs without fear of them saying "I did this really important thing (eg: upload a picture) and now it's gone" or something stupid along those lines. Second, by not allowing them to have it on their server on this point you are still assured they are a paying client and want to move forward with anymore changes they may want. Once it's handed off to them you lose that control.

I don't understand why it would run slow. If the client is expecting large numbers of visitors you can run load tests to see how the server will handle loads and debate what is right. But it shouldn't run slow on a proper testing server or even a restricted part of your web server.