Unable to connect Apps Script to Cloud SQL using JDBC

Viewed 104

So... I have been trying to set up the connection to a Cloud SQL for MySQL database from an Apps Script function, however I am unable to figure out why it is not working. I have been following the documentation for JDBC, however no matter what I do, the connection always fails.

The following is the code I am using:

const connectionName = '<project_id>:<region>:<instance_id>';
const user = '<user>';
const userPwd = '<user_pass>';
const db = '<database>';

const dbUrl = 'jdbc:google:mysql://' + connectionName + '/' + db;

function connect() {
  const conn = Jdbc.getCloudSqlConnection(dbUrl, user, userPwd);

  let start = new Date();
  let stmt = conn.createStatement();

  stmt.setMaxRows(1000);

  let results = stmt.executeQuery('SELECT * FROM entries');
  let numCols = results.getMetaData().getColumnCount();

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

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

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

When running this code I get the following error:

enter image description here

Every time I run the code, I can see a new line in the mysqlerr logs:

enter image description here

I have the Cloud SQL instance set with the public IP, but I have not whitelisted any networks. The documentation does not mention it as a required step. Also I have set the project number for the GCP project of this Apps Script project, but still I get this error.

EDIT

As requested, I am adding more screenshots about the instance:

I am able to connect to the Cloud SQL instance using the gcloud sql instances connect command, and once inside I am able to query the table I want. (Bear in mind that the table is currently empty, as we are in the early stages of development at this point)

I believe this rules out the possibility of me using the wrong user/password.

enter image description here

As you can see I am using the default value for the max_allowed_packet flag. To be honest I have not set any flags on this instance yet.

About the connection name, I am using the format shared in the code snippet above, and actually I have copy and pasted it from the Cloud Console.

The next screenshot is the summary of the instance's settings:

enter image description here

Thanks in advance!

1 Answers

I work on the team that maintains Google Cloud SQL Connector libraries.

I was able to get your code sample working with my own project, so I don't think your code is the problem. The first thing I would try is double checking the values of user, userPwd, and connectionName. If those are all correct, then I would also ensure that the user you are trying to log in as has access to the database.

As for those errors you're seeing in the logs, this troubleshooting documentation has some suggestions regarding things you can try. Once suggestion would be to increase the max_allowed_packet flag

If you're sure that all of those values are correct, then please follow up to this comment and we can try to debug further.

Related