Problem to remotely access MySQL database using Google Apps Script

[Gregoric]

cPanel User Name: avetrus

---

Name of Domain: surehow.gi9.co

---

Detailed Description: Dear staff member,


I have encountered a problem while trying to remotely access MySQL database located on Gigarank server. I am trying to connect to the database using Google Apps Script, which use JDBC to achieve such tasks.

Since I have read a topic with a bit similar issue (here: http://www.gigarocket.net/forum/thread-1753.html) I have ensured that I have set a "%" access host in the Remote MySQL section in cPanel, as Genesis had described in detail.

To connect to the database, I have literally copied a script presented on Google Developers tutorial page (here: https://developers.google.com/apps-script/guides/jdbc?hl=fr#read_from_the_database) and modified the crucial connection variables (user, password, etc.) to the proper ones.

Here is the configuration which I have prepared but which seemingly is set up wrongly.

var db_ip   = '37.187.74.24:3306',
    db_user = 'avetrus_admin',
    db_pass = 'my_password',
    db_name = 'avetrus_tgs';
var db_url = 'jdbc:mysql://' + db_ip + '/' + db_name;
var conn = Jdbc.getConnection(db_url, db_user, db_pass);

The IP I have used in "db_ip" is taken from cPanel. I did not use the "surehow.gi9.co" domain as I think that Google Apps have some problems with those (as described here: http://stackoverflow.com/questions/13151909/google-apps-script-jdbc-connection-failed).

When attempting to run the script, such an error appears: "Failed to establish a database connection. Check connection string, username and password. (line 142, file "kod-lista")"

Could I request any advice from yours? I am using the remote MySQL connection for the first time, as well as the JDBC so I could messed something up. Also, you may want to read "Setup for other databases" section on this page https://developers.google.com/apps-script/guides/jdbc


Thank you in advance, best regards,
Gregoric

---

Additional Information: Not answered

---

 

[admin]

Have you white listed the remote ip in the remote mysql section of cPanel?

Sent from my SM-T310 using Tapatalk

 

[Gregoric]

Have you white listed the remote ip in the remote mysql section of cPanel?

Yes, I think so, as mentioned above.
Since I cannot specify Google Apps IP, I have used "%" wildcard.

It looks like this:

 

[admin]

Just noted this on: https://developers.google.com/apps-script/guides/jdbc

Note that the JDBC service can only connect to ports 1025 and above. Ensure your database is not serving off a lower port.

Our SQL port is 3306.

We will not be able to modify that port number.

 

[Gregoric]

Just noted this on: https://developers.google.com/apps-script/guides/jdbc

Note that the JDBC service can only connect to ports 1025 and above. Ensure your database is not serving off a lower port.

Our SQL port is 3306.

We will not be able to modify that port number.

Well, if I have understood everything correctly, 3306 is all right? "[...] JDBC service can only connect to ports 1025 and above"? Or the ports work in a way that I do not correctly understand?

Anyways, I have used this port in my script (37.187.74.24:3306)

Edit
There's another article on that topic, but there is nothing essential to my case, except the fact that the author has 3306 port as well that can connect using Google Apps.
http://www.mousewhisperer.co.uk/drivebunny/connecting-to-mysql-databases-using-apps-script/

 

[Genesis]

@Gregoric. Not sure whether this has been solved. Is there anything more we can do for you, or can we close this thread?

 

[Gregoric]

@Gregoric. Not sure whether this has been solved. Is there anything more we can do for you, or can we close this thread?

Well, I am still not able to connect to my MySQL database. The problems seems to be on Gigarank's side as other users that execute similar code (but on other servers) do not encounter such problems. Then, I hope that I will be somehow able to solve my issue thanks to consulting you


Well, let me show you the details to you.

The connection variable looks like this (and it is the line in my code that causes the error):

var conn = Jdbc.getConnection('jdbc:mysql://37.187.74.24:3306/avetrus_tgs', 'avetrus_admin', 'my-password');

I would like to ensure that I have chose proper IP address, firstly. Is the IP address I have chosen OK? It is the same address that is in cPanel's Shared IP Address item.

The DB user is the same as in here:

And password is the correct one as I have literally copied it into the script, then checked it (and re-written) at least several times.


I have added "%" and "%.google.com" tp the Remote MySQL access hosts so any address should be accepted, am I right? Or do I have to set those addreses (that are mentioned here) manually?

First, you need to ensure that your database accepts connections from any of Apps Script's IP addresses. These are the address ranges you'll need to whitelist:

64.18.0.0 - 64.18.15.255
64.233.160.0 - 64.233.191.255
66.102.0.0 - 66.102.15.255
66.249.80.0 - 66.249.95.255
72.14.192.0 - 72.14.255.255
74.125.0.0 - 74.125.255.255
173.194.0.0 - 173.194.255.255
207.126.144.0 - 207.126.159.255
209.85.128.0 - 209.85.255.255
216.239.32.0 - 216.239.63.255

---

I have also tried connecting to MySQL database on another hosting: Frihsot.com. I have allowed the access to the % wildcard as well but the script seems not to run anyways - returns the same error as here.

By the way: do you know if anyone managed to configured their script on Google Apps in a way that allowed them to connect to a database that is hosted via Gigarank?

 

[Genesis]

OK, the support request is still open.

By the way: do you know if anyone managed to configured their script on Google Apps in a way that allowed them to connect to a database that is hosted via Gigarank?

I think someone else tried. Thought he was successful, and then we never heard from him again. He was from Canada.
http://www.gigarocket.net/forum/thread-1753.html

Also, will do some research and ask our Tech Admin for his input.

 

[Gregoric]

I'll check and see whether I can find a link.

@Genesis: I think what you mean is: http://www.gigarocket.net/forum/thread-1753.html


I have done a lot of research and found very little information on possible causes or similar issues. I have tried out two more connection options (listed below together with the original one from tutorial) but none worked.

var conn = Jdbc.getConnection("jdbc:mysql://37.187.74.24:3306/avetrus_tgs", "avetrus", "pass");

 var conn = Jdbc.getConnection("jdbc:sqlserver://37.187.74.24:3306;" +
   "databaseName=avetrus_tgs;user=avetrus_admin;password=pass;");

var conn = Jdbc.getConnection('jdbc:mysql://37.187.74.24:3306/',
                                {user: 'avetrus_admin', password: 'pass', databaseName: 'avetrus_tgs'});

I have extracted only the JDBC code to another Google Apps Script file to ensure that it is not somwhow interrupted by other parts of code I have in my main script - this did not work, as well.

The only left option is that Gigarank's server version is (somehow) not supported by the Google Apps. I have found one article with server version compatibility issue here. The author states that the script did not work with engine version numbered 5.5.8 whereas it successfully executed when the version was changed to 5.5.25a.

 

[Genesis]

OK, this is early days and I could be off the mark, but I've got a theory. Is your IP a dynamic IP. Sort of makes sense you have to have a static IP for the distance connection to work because you have to have a white listed IP (you doing the white listing). Have you tried a proxy IP?

Check the literature here - quite informative for recommending strings for your coding:
https://my.hostmonster.com/cgi/help/89

 

[Gregoric]

OK, this is early days and I could be off the mark, but I've got a theory. Is your IP a dynamic IP. Sort of makes sense you have to have a static IP for the distance connection to work because you have to have a white listed IP (you doing the white listing). Have you tried a proxy IP?

Check the literature here - quite informative for recommending strings for your coding:
https://my.hostmonster.com/cgi/help/89

The Google Apps Script runs on Google's servers, not my machine and thus, the script attempts to establish a connection between database and Google's servers. I guess I cannot extract the IP addresses of all the Google servers and so I have added a "%" and - additionally, however little sense it has - a "%.google.com" wildcards. This way, I do not have to manually set all the possible Google's addresses.

I will try to connect to the database from a local machine with some software now, just to ensure i am using the very correct credentials.

---

@Genesis Okay, I have attempted to connect to the database using HeidiSQL from my local machine. Did not succeed. This is what I did receive:

I have no idea what should I try to do now, what I do have, though, is another question: could you check (at least using the portable version of HeidiSQL from here) if you are able to connect to your database if you have any located here, on Gigarank? To ensure that it is possible to do on Gigarank.

 

[jaran]

Why dont you try to change your output file from MySQL to become JSON or XML file then calling to appengine ?

 

[Gregoric]

Why dont you try to change your output file from MySQL to become JSON or XML file then calling to appengine ?

I am not using App Engine. Google Apps Script is a completely different thing than Google App Engine is. And I am not trying to make a call from here to Google Apps but the other way around.

Could anyone check if they can access databases on their free hostings on Gigarank remotely? If it is possible, I could try to connect to my database, since so far two different approaches to achieve this did not work.

 

[Gregoric]

I have tried to connect to the database using an Android app (just to check it out). Still, no positive result.
What I got is this:

I would like to politely request somebody just to check if they can connect to their database remotely. If it is possible and the fault is on my side, I would then post the issue to Stackoverflow for further search for the cause.

Great thanks in advance!

 

[Gregoric]

I have created a free MySQL database using db4free.net service and tried connecting to their database - this solution worked without any problem. The problem does not lie in my script then.

Since nobody tried connecting to their database remotely, I have to try to find a solution on my own, it seems Anyways, if anyone would want to help, you could download HeidiSQL portable software, launch it type in your database's credentials and just check it. I would truly appreciate it!


Could you, administrators, read this article and ensure that the 3306 port is open in Gigarank's firewall, if you use any? I would be grateful.
http://www.liquidweb.com/kb/opening-ports-in-your-firewall/


Also, does anyone know if I should set the Remote Access wildcard in cPanel to

%

or to

%.%.%.%

?
None of above seems to work, but I did not find any documentation on this topic so I may have been doing it wrong for all the time. The db4free.net connection worked without setting anything in cPanel, since user does not have access to the cPanel there.

Just to be 100% sure: I have set all the addresses specified by Google in the tutorial page mentioned above. This (as I had expected) did not bring any effect. Now my Remote Access page looks like this:

A bit funny, but hey, at least I am sure

 

[un4saken]

Hello,
Are you able to login remotely now?

 

[Gregoric]

Hello,
Are you able to login remotely now?

Hello!

It works perfectly now! I am truly grateful for fixing this. What did you do, if I could ask? Was the issue related to my account or something else?

Thank you again!

Best regards to you & whole staff!


PS.: If anyone would need help with connecting to the database using Google Apps Script, I could gladly help.

 

[un4saken]

It was not related to your account, it was something with the firewall.

 

[Genesis]

un4. Out of curiosity. Does this mean that every one else should be able to access remotely as well now, or would they need to be set up similarly individually?

 

[un4saken]

yes, it's all okay now.