Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Problem to remotely access MySQL database using Google Apps Script
#1
[Solved]
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-scrip...e_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.
Code:
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/13151...ion-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
#2
[Solved]
Have you white listed the remote ip in the remote mysql section of cPanel?

Sent from my SM-T310 using Tapatalk
Please do NOT contact me via PM for support request`s, use the support forum instead, you will get a faster response.
resim

Follow me on Facebook: https://www.facebook.com/crawlrate/
#3
[Solved]
(09-11-2014, 07:22 PM)DJB Wrote: 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:
resim
#4
[Solved]
Just noted this on: https://developers.google.com/apps-script/guides/jdbc

Quote: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.
Please do NOT contact me via PM for support request`s, use the support forum instead, you will get a faster response.
resim

Follow me on Facebook: https://www.facebook.com/crawlrate/
#5
[Solved]
(09-12-2014, 09:21 AM)DJB Wrote: Just noted this on: https://developers.google.com/apps-script/guides/jdbc

Quote: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/drivebun...ps-script/
#6
[Solved]
@Gregoric. Not sure whether this has been solved. Is there anything more we can do for you, or can we close this thread?
#7
[Solved]
(09-12-2014, 09:54 AM)Genesis Wrote: @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):
Quote: 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:
resim

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?
Quote: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?
#8
[Solved]
OK, the support request is still open.

(09-12-2014, 10:12 AM)Gregoric Wrote: 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.
#9
[Solved]
(09-12-2014, 12:43 PM)Genesis Wrote: 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.
Code:
var conn = Jdbc.getConnection("jdbc:mysql://37.187.74.24:3306/avetrus_tgs", "avetrus", "pass");
Code:
var conn = Jdbc.getConnection("jdbc:sqlserver://37.187.74.24:3306;" +
   "databaseName=avetrus_tgs;user=avetrus_admin;password=pass;");
Code:
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.
#10
[Solved]
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
  




Users browsing this thread:
1 Guest(s)

Problem to remotely access MySQL database using Google Apps Script387