Google App Scripts won't make a Jdbc connection to MySQL on a remote server

Question

I'm new to using Google Scripts and am trying to make a connection to a MySQL (5.5.65-MariaDB) database which is hosted on an external (non-Google) server running CentOS Linux 7.7.1908 Core.

I'm following the guide here: https://developers.google.com/apps-script/guides/jdbc#other_databases

In my script I have used the following function:

// export_sheet_data.gs
function writeOneRecord() {
    var conn = Jdbc.getConnection('jdbc:mysql://HOST_NAME.com:3306/DB_NAME', 'DB_USER', 'DB_PASSWORD');

    var stmt = conn.prepareStatement('INSERT INTO entries '
    + '(guestName, content) values (?, ?)');
    stmt.setString('andy', 'First Guest');
    stmt.execute();
}

Obviously the HOST_NAME, DB_NAME, DB_USER and DB_PASSWORD are strings (entered literally, not variables) which correspond to the appropriate values: host name of my external server, database name, database username and database password.

When I execute this in the Scripts console (Run > Run function > writeOneRecord()) it says

Running function writeOneRecord()

for approx 10 seconds.

It then errors with

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

If I click "Details" it doesn't really elaborate on this. It says

Exception: Failed to establish a database connection. Check connection string, username and password. (line 54, file "export_sheet_data")

I have done the following:

Added the IP addresses on the linked webpage to my servers firewall. Reloaded the firewall to make sure it's using this configuration.
Checked that the database, DB_NAME, exists on my server.
Checked that the hostname, HOST_NAME matches my server. Also tried it with the external IP address.
Checked MySQL is using port 3306
Checked the username/password (DB_USER and DB_PASSWORD) are correct.

There is nothing appearing in my firewall log on the server I'm connecting to about this.

I've checked that I'm using Jdbc.getConnection correctly.

How else can I debug this? There doesn't seem to be anything in the Google Scripts console which can help.

Your Mysql version? And were you able to connect to it from elsewhere? — TheMaster
Working with Apps Script's JDBC service is often an exercise in frustration. You're likely better off building out a custom mirco-service that interacts with the database and have Apps Script call out to it. I'd recommend using Apps Script in tandem with services such as Cloud Pub/Sub and Cloud Functions to build out the functionality you require. — TheAddonDepot
@TheAddonDepot the use-case here is reading data from a Google Sheet, and then inserting it into a MySQL database on a different server. My script already reads the data out of the Sheet correctly and the SQL required to insert it into MySQL will be very basic. It's simply the connection that isn't working. But because it didn't appear in my firewall log, I don't even know how far the connection was getting before it failed (I don't know if it even reached my server). Would the idea you've mentioned be more suitable for this type of use-case? I'm open to looking into it if so. — Andy
See if you have any luck with alternate signatures of getConnection: user, pwd and dbname as a object. — TheMaster

roma roma · Accepted Answer · 2020-06-15T02:22:25

Are you using VPS Linux?

I've had the same issue, and wasn't able to fix it. It appears to be a bug of GAS JDBC implementation with combination of custom network drivers used inside VPS and/or specific MYSQL implementation.

For some Linux/Mysql combinations I was able to connect to Linux Mysql, for some not.

The error in my case was "Got an error reading communication packet"(mysql logs) - very hard to track mysql error.

If you check mysql logs on your server and that's this error I think the best you can do is follow simplified version of @TheAddonDepot advice - write a small nodejs code to run sql for you and install it as linux service(listen http on custom port)- there are a lot of examples for that. It'd be easier to spend several hours for this, cause I don't think this issue would be resolved in near future.

I haven't tested it, but something like this should fit your case.

var http = require('http');
var url = require('url');
var mysql = require('mysql');

var con = mysql.createConnection({
  host: "localhost",
  user: "yourusername",
  password: "yourpassword",
  database: "mydb"
});

con.connect(function(err) {
  if (err) throw err;
  console.log("Connected to mysql!");
});

http.createServer(function (req, res) {
  var q = url.parse(req.url, true).query;
  var result;

  let stmt = `INSERT INTO entries (guestName, content) values (?, ?)`;
  let values = [q.guestName, q.content];
  console.log('Request', q);

  // execute the insert statment
  connection.query(stmt, values, (err, results, fields) => {
    if (err) {
      res.writeHead(400, {'Content-Type': 'application/json'});
      console.error(err.message);
      result = {error:err};
    } else {
      res.writeHead(200, {'Content-Type': 'application/json'});
      console.log('Insert Id:' + results.insertId);
      result = {success:true, id:results.insertId};
    }
  });
  res.end(JSON.stringify(result));
}).listen(7733);

and call it from GAS like

UrlFetchApp.fetch('http://your-server-ip:7733/?guestName=' + encodeURIComponent(guestName) + '&content=' + encodeURIComponent(content));

Alternatively, you can try switching Linux/Mysql version but it's more complicated, and I'm not sure switch combination works exactly cause in my case error was on Debian 18.04

Google App Scripts won't make a Jdbc connection to MySQL on a remote server

2 Answers