1

I'm trying to connect AWS RDS to Google Sheets, but all i get is

Failed to establish a database connection. Check connection string, username and password.

I have tried the method found in trackers i.e, "using the IP of the endpoint host address", but still the same error occurs,

I have:

  1. Set [RDS SETTINGS - public accessibilty- ON] & verified that endpoint and access details work on all other viewers (Navicat, Adminer etc.)

  2. added all the google IP's needed to white-list(refer: https://developers.google.com/apps-script/guides/jdbc ) in 'RDS VPC- Security Groups' inbound & outbound.

Here's the code i'm using:

var connectionName = 'x.x.x.x:3306'
var user = 'admin';
var userPwd = 'pass';
var db = 'dbname';

var dbUrl = 'jdbc:mysql://' + connectionName + '/' + db;

function readFromTable11() {
  var conn = Jdbc.getConnection(dbUrl, user, userPwd); //error here
  Logger.log(conn);
  var start = new Date();
  var stmt = conn.createStatement();
  stmt.setMaxRows(1000);
  var results = stmt.executeQuery('SELECT * FROM items');
  var numCols = results.getMetaData().getColumnCount();

  while (results.next()) {
    var rowString = '';
    for (var col = 0; col < numCols; col++) {
      rowString += results.getString(col + 1) + '\t';
    }
    Logger.log(rowString);
  }

  results.close();
  stmt.close();

  var end = new Date();
  Logger.log('Time elapsed: %sms', end - start);
}

I've scoured the net & still couldn't get it to work.

Tanmay Dwivedi
  • 71
  • 1
  • 9
  • Is admin user your root user? Have you tried creating another user specifically to edit your database? – Anton Dementiev Nov 24 '19 at 11:57
  • yes it's the root user, but i can't see how that will help as i'm providing all the necessary credentials and db name, but will try, please do add other possible solutions. – Tanmay Dwivedi Nov 25 '19 at 05:41
  • @TanmayDwivedi are you able to connect to the host using Apps Script? Have you tried issuing a request to it using `UrlFetchApp`? If so, what are the results? – carlesgg97 Nov 25 '19 at 08:52
  • No, it never once gave me a successful connection, also isin't UrlFetchApp is used to get certificates & then get text? – Tanmay Dwivedi Nov 25 '19 at 09:21
  • @carlesgg97, I think i'm missing something basic in connection settings to DB RDS..if you've done this before successfully, can you please suggest a guide link or explain it here? – Tanmay Dwivedi Nov 25 '19 at 09:28

3 Answers3

5

Okay, turns out that Google Scripts only supports MySql v5.5.x versions, so going above that causes the issue to be a "failed database connection." so to make sure successful connection, just make the same settings above in RDS but select MySQL Engine to have version 5.5.x & use this - (toolbox.googleapps.com/apps/dig) to lookup your endpoints IP address for possible DNS problems. I'll update more.

Tanmay Dwivedi
  • 71
  • 1
  • 9
  • Thank you so much for this explanation. I was going nuts... It was indeed a matter of having MySQL 5.5, not 8.*. – user1413114 Nov 23 '20 at 15:21
0

You can use the below code snippet, don't forget to use useSSL=false

  var server = '111.111.111.111';
  var port = 3306;
  var dbName = 'dbName';
  var username = 'dbName';
  var password = 'password';
  var url = 'jdbc:mysql://'+server+':'+port+'/'+dbName+'?useSSL=false';
Mr.Eboy
  • 45
  • 9
0

As of Jul/2021, I was able to connect RDS and AppScript with MySQL 5.7 using standard JDBC connection (accepted answer mentioned the issue with v5.5.x versions and above).

The only trick was to add/allow all IPs from this list (https://www.gstatic.com/ipranges/goog.txt) inside my RDS inbound security group as stated in AppScript JDBC documentation:

In order to use this method you must allow-list certain CIDR IP address ranges so that Apps Script's servers can connect to your database. https://developers.google.com/apps-script/guides/jdbc

PS: as the list is very long, I had to break and attach to 2 security groups.

czmarcos
  • 1
  • 1