ETIMEOUT error | Google Cloud SQL database with NodeJS

Question

I have created a mysql database on google cloud that I'd like to access from a separate node web application (also running on google cloud). I am testing the connection locally on my computer first, and when I run the following code locally I can successfully establish a connection to my database and see the data in it.

'use strict';

// [START app]
const express = require('express');
const bodyParser = require('body-parser');
const path = require('path');
const app = express();
const mysql      = require('mysql');

var connection = mysql.createConnection({
  host     : 'Cloud SQL IP',
  user      : 'username',
  password  : 'password',
  database  : 'db_name'
});



// parse application/x-www-form-urlencoded
app.use(bodyParser.urlencoded({ extended: false }))

// parse application/json
app.use(bodyParser.json())

// Make globals.js accessible
app.use(express.static(__dirname + '/'));


app.get('/', (req, res) => {
    connection.connect();

    connection.query('SELECT * FROM Users', function (error, results, fields) {
        if (error) throw error;
        console.log(results);
    });

    connection.end();
    res.status(200).send('Hello World!');
});

app.get('/login', (req, res) => {
    res.status(200).send();
});

// [START server]
const PORT = process.env.PORT || 8080;
app.listen(PORT, () => {
  console.log(`App listening on port ${PORT}`);
  console.log('Press Ctrl+C to quit.');
});
// [END app]

However when run this same code in my google app engine (for both debugging on port 8080 and fully deployed on https://myapp.appspot.com) I get the following timeout error:

{ Error: connect ETIMEDOUT
    at Connection._handleConnectTimeout         (/home/megan_cooper2900/project/node_modules/mysql/lib/Connection.js:419:13)
at Socket.g (events.js:292:16)
at emitNone (events.js:86:13)
at Socket.emit (events.js:185:7)
at Socket._onTimeout (net.js:338:8)
at ontimeout (timers.js:386:14)
at tryOnTimeout (timers.js:250:5)
at Timer.listOnTimeout (timers.js:214:5)
--------------------
at Protocol._enqueue (/home/megan_cooper2900/project/node_modules/mysql/lib/protocol/Protocol.js:145:48)
at Protocol.handshake (/home/megan_cooper2900/project/node_modules/mysql/lib/protocol/Protocol.js:52:23)
at Connection.connect (/home/megan_cooper2900/project/node_modules/mysql/lib/Connection.js:130:18)
at app.get (/home/megan_cooper2900/journeyma/app.js:31:13)
at Layer.handle [as handle_request] (/home/megan_cooper2900/project/node_modules/express/lib/router/layer.js:95:5)
at next (/home/megan_cooper2900/project/node_modules/express/lib/router/route.js:137:13)
at Route.dispatch (/home/megan_cooper2900/project/node_modules/express/lib/router/route.js:112:3)
at Layer.handle [as handle_request] (/home/megan_cooper2900/project/node_modules/express/lib/router/layer.js:95:5)
at     /home/megan_cooper2900/project/node_modules/express/lib/router/index.js:281:22
    at Function.process_params         (/home/megan_cooper2900/project/node_modules/express/lib/router/index.js:335:12)
  errorno: 'ETIMEDOUT',
  code: 'ETIMEDOUT',
  syscall: 'connect',
  fatal: true }

Why is this not working on the Google App Engine application?

Toile

Add your instance connection name in the host attribute – Sync

Isn't the host value usually in the form of a URL? – Toile

Please look through the many existing questions about connecting from NodeJS. tl;dr you need to be using the connectivity support in App Engine, not connecting by IP. e.g. #46750317. That question is about postgres but the solution is the same. – Faint

@Toile I have the same problem but I can't solve with solution above, have you another one ? Thanks – Amblyopia

@Amblyopia Apologies I have no other solution as the original solution worked for me – Toile

Answer 1

In your connection configuration for mysql,host does not work on App Engine. You have to use socketPath . socketPath is the path to a unix domain socket to connect to. When used host and port are ignored. (transferred knowledge from using Loopback on App Engine flex. it had me banging my head for days lol). It's value is your Cloud SQL Instance connection name

so in your case, it should look like this: /cloudsql/my-project-12345:us-central1:mydatabase

var connection = mysql.createConnection({
  socketPath     : '/cloudsql/my-project-12345:us-central1:mydatabase',
  user      : 'username',
  password  : 'password',
  database  : 'db_name'
});

It's a similar process if you're using Postgres on GCloud which is answered here

Selfrighteous

Dude, thank you so much. 6 hours of pain. Not a fun way to spend a Sunday. – Rubadub

I still have the same problem; what I've to do ! – Amblyopia

For that to work, you will also need to add the "Cloud SQL Client" role for "App Engine default service account" in IAM table – Backwards

Setting the socketPath and adding the sql client role for app engine ended this several hour nightmare, thank you – Espalier

small thing but it is '/cloudsql/my-project-12345:us-central1:instance-name not database for the socketPath – Edsel

Answer 2

I also did faced the same issue and I was using Kubernetes pods to access my CloudSQL instance. I got a fix by increasing the timeout in the configuration.

cloudSqlConfig: {
    connectionLimit: 10,
    host: 'your-host-ip',
    user: process.env.DB_USERNAME,
    password: process.env.DB_PASSKEY,
    database: 'myDB',
    connectTimeout: 20000,
    waitForConnections: true,
    queueLimit: 0
},

Pyridoxine