CentOS Question posted in
The official documentation can be found here.

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

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.

Tags:

2

Answers


  1. 0

    I have had some difficulties accessing MySql databases on websites and found this approachment to work for me.

    var address=getDBData('address');//I keep the actuall data in a spreadsheet  
    var user=getDBData('user');
    var userPwd=getDBData('pass');
    var db=getDBData('db');
    var dbUrl='jdbc:mysql://' + address + '/' + db;
    s+='<table>';
    s+='<tr><th>Database Name: ' + db + '</th></tr>';
    s+='<tr><th>' + qry + '</th></tr></table>';
    try
    {
      var conn=Jdbc.getConnection(dbUrl, user, userPwd);
    }

    The address that seems to work often is the ip address for the hosting account.

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search