Mysql connections - ISP remote connections and development to production upload setting changes

Wappler General
#1

There has been some discussion regarding this topic but I cannot find any definitive answer so I will ask.

My ISP does not allow remote connections as standard to their MySql databases however remote connections can be made by alternative settings with a unique port number.

For example the website connection may use database server: sql-svr1 username: userxxxx password: passwordxxx port: 3306
however to connect remotely from wappler i have to use an alternative port, say port 5575

So my first question is how do I manage this as working within Wappler when i must use port 5575 but the live site must use port 3306.

Secondly, just to complicate thing, obviously i do not normally author and test on the live site, i develop on my local network web and mysql server. I use dns to map a domain to my local web and mysql server and this is the way I normally work.

However when i upload the site from the local server to the production server I have to manually edit the connection file with the alternative MySql connections settings.

In Dreamweaver i would simply cloak the connections folder to prevent it uploading and overwriting the server copy as the contents are only the connection settings but in wappler the entire database structure is embedded in the file so it has to be uploaded and then the settings changed.
Anyone come up with an alternative method to manually editing the dmxConnections file?
It would be useful to be able to have settings configured for local or production settings

How to set database connection when host denies access from outside?
#2

Just a small background explanation of how Wappler database connectivity works will clear all the fog :slight_smile:

  • Wappler do not connect directly to your database, so external connection is not needed

What Wappler does is - it generates a connection file and uploads it to your server and then call it with regular http as you have defined in your project target.

So actually the connection file is local to your web server.

That is why usually for PHP for example with mySQL - the database server name is just “localhost”
if the web and database servers are on the same server.

Otherwise if you have a separate db server - well just enter its name and port as it is to be accessed from your web server

#3

Yes, i understand that George but the connection file in the dmxConnections folder on a development server contains the connection settings for the local environment (in my case Mysql server: 192.168.1.106) but when uploaded to the production server the connection settings in the connection file in the dmxConnections folder on the remote server will not match those of the production server (MySql server name: shareddb-h.hosting.stackcp.net) so the connection will fail?

In neither case, development or production, is the MySql server on the same IP address as the web server.

#4

Ah yes - it is challenging when you have different databases for local development and live site.
You really have to make sure their structure is exactly the same.

We are also thinking of offering separate database connections per target. So when defining a database connection you can have different options depending of the defined targets.

5 Likes
#5

That would be a great solution. I will just stick with manually editing the connection file for the moment, just wanted to be sure i was not missing something obvious. It’s no great hardship, i set the database name, port, user and password to the same values on both servers so it is just the server name to be edited.

#6

I suppose there are different problems and restrictions depending on how your server is set up, but I’ve never had to have different settings for local/remote sites. I also develop locally, using Wamp etc…

The remote server has naming requirements for the database and usernames etc… I mirror these when I set up my local databases (in Navicat). I have local and remote versions set up in Navicat. I can’t access the remote MySQL databases directly from my local machine - but I can with Navicat, using the HTTP tunnel option.

#7

@TomD basically i do the same but i cant mirror the MySql server name, just the login credentials. The problem is a growing one as more and more service providers move to cloud based hosting and the use of names rather than IP addresses becomes mandatory as a result. I also use navicat which does everything i need but that still does not address the server name issue in the connection settings

#8

ah you can always use alias in the hosts file :slight_smile:

https://support.managed.com/kb/a683/how-to-modify-your-hosts-file-so-you-can-work-on-a-site-that-is-not-yet-live.aspx

#9

I always use ‘localhost’ in the Host field (in Navicat), for both local and remote - and it works fine.

#10

lol @George, i have just done that and was about to post!!! I can alias the local MySql server as:

192.168.1.106 shareddb-h.hosting.stackcp.net
in the hosts file (i am a windows user)

@TomD wont work in this case as using localhost assumes that the Mysql server and web server are on the same IP address, most service providers now use separate servers so localhost cannot work

#11

Sorry - I forgot this was an issue.

#12

Hmmm - nothing ever works quite as you expect.

server name remapped as above. If i ping shareddb-h.hosting.stackcp.net it correctly returns with 192.168.1.106. I can connect with navicat using the mapped server name and it returns the correct local database but in Wappler i get the following error.

mysql-remapping-error
mysql-remapping-error.jpeg582×620 30 KB

I believe that is connection refused??

Hold on - perhaps it is also necessary to authorise the domain on local the sql server as it may be seeing it as an attempt at external access. Will investigate - DIDN’T HELP, PROBLEM STILL NOT RESOLVED

#13

@George Yes! That would be a perfect solution! I run into the same problem. I have same database structure local and online, but address and account are different. Would be great if database connection file would offer custom db connection links to each target.

#14

For anyone still following i have this working now using hosts redirection.
I simply created identical login and database name credentials to the online host then redirected the online host name in the hosts file to the IP address of my local MySql server.
for example in the case of:
Online Credentials
host1.mysqlhost.com
database: mydatabase
username; itsme
password: 123

Locally i created an database of identical name and login details and within the hosts file added
192.168.1.106 host1.mysqlhost.com

Not sure why it didn’t work when i tried a few days ago.

Just got to keep the tables in sync now.

1 Like
#15

Agree, that separate database connections for each target is needed. I certainly don’t like developing and testing connected to the live database.

4 Likes
#16