Unable to connect to MySQL database error

[jrioux]

I've been recently working with LiveCode 4.6's tutorial "How to connect to a MySQL database". I have a database set up on GoDaddy, which has reported back to me the host name, database name, user name, and password. Per the instructions there, I then set up a button with the script which follows. (The script works just fine for the example given in the tutorial, accessing a test database on runrev.) I consistently get an error when trying to connect. The tutorial recommends going back to be sure the variables are correctly set (which I have, many times), so I'm wondering whether there might be something else I'm missing.

on mouseUp
-- use a global variable to hold the connection ID so other scripts can use it
global gConnectionID

-- set up the connection parameters - edit these to suit your database
put "www.jrioux.db.2169997.hostedresource.com" into tDatabaseAddress
put "jrioux" into tDatabaseName
put "jrioux" into tDatabaseUser
put --here I put my password-- into tDatabasePassword

-- connect to the database
put revOpenDatabase("MySQL", tDatabaseAddress, tDatabaseName, tDatabaseUser, tDatabasePassword) into tResult

-- check if it worked and display an error message if it didn't
-- & set the connection ID global
if tResult is a number then
put tResult into gConnectionID
answer info "Connected to the database." & cr & "Connection ID = " & gConnectionID
else
put empty into gConnectionID
answer error "Unable to connect to the database:" & cr & tResult
end if
end mouseUp

[bangkok]

I see 3 possible causes :


-your firewall blocks the MySQL port, which is number 3306.
Check your firewall

-the hosted MySQL server doesn't allow (yet) connections from the "outside".
Usually, with webhosting companies, you get a "control pannel" with PhPMyAdmin etc. Usually, there is one function that allows remote connexions on the MySQL server (you can also block an IP address for instance, or a range of IP addresses, or allow only 1 IP address).
This function works with "widlcard" : %
Try to find out this "pannel".

-a problem with your script.

put revOpenDatabase("MySQL", tDatabaseAddress, tDatabaseName, tDatabaseUser, tDatabasePassword) into tResult

-- check if it worked and display an error message if it didn't
-- & set the connection ID global
if tResult is a number then

You have to add :
PUT THE RESULT INTO TRESULT
just after the revOpenDatabase line.

!! Otherwise tresult will never be a number... and therefore will always trigger the error message.

[jrioux]

Bangkok:

Many thanks.

As to the first, if the firewall were the problem, wouldn't it also block the port when I (successfully) connect to the test database on the runrev site?

As to the second, I'll check. (Thanks!)

As to the third, I'm not sure I understand why that would be a problem (as the command seems to put the result into tResult already), but I'll certainly give it a try.

[bangkok]

As to the first, if the firewall were the problem, wouldn't it also block the port when I (successfully) connect to the test database on the runrev site?

Correct.

As to the third, I'm not sure I understand why that would be a problem (as the command seems to put the result into tResult already), but I'll certainly give it a try.

Sorry. I read too quickly your code !

[jrioux]

Success!

It turns out that GoDaddy does not set hosted MySQL databases up for external access by default. You must create the database with that option selected (apparently it stores such databases on a separate server). So, the database was created, I successfully connected, and added a record through a LiveCode program. At this point, "the sky's the limit"!

Many thanks, bangkok. A dead-on solution.

[admin12]

Bangkok,

I followed your advice, logged into the cpanel and put my IP address in the appropriate place. I have been able to login to the hosted vps server's mysql database as a result. Now, since I and my client have DHCP routers/modems, the IP address physically entered will eventually expire. How do I make it so that the VPN server accepts any IPs from our two modems?

I am not sure I understand the wildcard option and do not know the range of IPs.

Mike

[bangkok]

I followed your advice, logged into the cpanel and put my IP address in the appropriate place. I have been able to login to the hosted vps server's mysql database as a result. Now, since I and my client have DHCP routers/modems, the IP address physically entered will eventually expire. How do I make it so that the VPN server accepts any IPs from our two modems?

I am not sure I understand the wildcard option and do not know the range of IPs.

Wildcard is "*" ("%" with MySQL). It means "whatever character after".

bol* will fetch bolo, bola, boli etc.

When someone gets an IP address (for instance 100.100.98.10), this address is always within a range. 100.100.98.1 to 100.100.98.255 (it goes from 1, to 255, with 3, 4, 5 etc).

Your customer might change IP address at every connexion, however this IP will always be within the range given to the ISP.

Let's say the ISP owns 255 addresses from 100.100.98.1 to 100.100.98.255.

In this case, you will put in your CPANEL an IP address with a wildcard :
100.100.98.*

That will allow 100.100.98.1
100.100.98.2
100.100.98.3
100.100.98.4 etc.

to connect.

So you have to discover the range used by the ISP of your customers. Tools like WHOIS on the web can do that.

However... by doing so, you have to understand that you increase the threat level. Someone with bad intentions, using the same ISP as your customer, could try to connect to your DB.

If you peruse the DB forum you'll see that many people are not at ease with a mySQL server "open" to the outside. They advocate to use a "lawyer" between, AKA a CGI on a webserver that will receive the SQL query, check it, and then forward it to the MySQL server, and then display back the result.

Furthermore don't forget that if you distribute your LiveCode app, then the login and password of the MySQL server would be embeded inside. You can of course encrypt it, but still : it's inside.

With the CGI option, no more trouble. The CGI on the webserver (same as the MySQL server) will open the connexion with the MySQL server, with the proper login and pwd.

I'm using now this solution with some of my LiveCode apps, and a On-Rev server.

[admin12]

To get around the problem, I intend to use .htaccess to limit who can use the software. This is ONLY for my client and perhaps one or two of their employees, so it will not be distributed to the masses at all. Because of this, I can either use .htaccess or build in a username/password capability to limit who uses the software.

Thank you so much for the explanation.

Mike

[askari]

Help!! Can't figure out why this is happening. I get the error;
Access denied for user 'user'@' 'c-xx-xx-xx-xxx.hsd1.site.domain'' (using password: YES)

Where the 'xx-xx-xx-xxx' turns out to be the IP address of my machine. I am connecting to a remote mysql server and this worked ok sometime during the day but now it's run amok. Any ideas on resolving this??

[admin12]

bankok,

How would I utilize CGI? Is there a script made to handle this or do I have to create one (not a CGI programmer)?

Mike

[peaslee]

Help!! Can't figure out why this is happening. I get the error;
Access denied for user 'user'@' 'c-xx-xx-xx-xxx.hsd1.site.domain'' (using password: YES)

Where the 'xx-xx-xx-xxx' turns out to be the IP address of my machine. I am connecting to a remote mysql server and this worked ok sometime during the day but now it's run amok. Any ideas on resolving this??

I had this problem for two reasons:

I didn't have the web site properly named. It is insertYourSiteHere.on-rev.com
I did not add my IP address to those which can access the site.

[askari]

Thanks peaslee, your second solution resolved my problem