0

I have a hosted node app and mysql on google cloud. I tried all possible ways to connect mysql from node but every time it throws this error.

    let pool;
    const createPool = async () => {
      pool = await mysql.createPool({
       //   host: "35.200.129.217",
    socketPath:"/cloudsql/idyllic-anvil-256103:asia-south1:database",
      user: "abc@gmail.com",
      password: "pass",
      database: "db"
      });
    };
    createPool();

    app.get("/getnews", (request, response) => {
    createPool.query('SELECT * FROM db.mydb', function (err, result) {
        if (err) throw new Error(err)
        response.send(result);
    })
    });

Error:

    2019-10-19 16:33:40 default[20191019t215943]  Error: Error: connect ETIMEDOUT      at Query._callback (/srv/index.js:42:20)      at Query.Sequence.end (/srv/node_modules/mysql/lib/protocol/sequences/Sequence.js:83:24)      at /srv/node_modules/mysql/lib/Pool.js:205:13      at Handshake.onConnect (/srv/node_modules/mysql/lib/Pool.js:58:9)      at Handshake.<anonymous> (/srv/node_modules/mysql/lib/Connection.js:525:10)      at Handshake._callback (/srv/node_modules/mysql/lib/Connection.js:491:16)      at Handshake.Sequence.end (/srv/node_modules/mysql/lib/protocol/sequences/Sequence.js:83:24)      at Protocol.handleNetworkError (/srv/node_modules/mysql/lib/protocol/Protocol.js:369:14)      at PoolConnection.Connection._handleNetworkError (/srv/node_modules/mysql/lib/Connection.js:421:18)      at PoolConnection.Connection._handleConnectTimeout (/srv/node_modules/mysql/lib/Connection.js:417:8)
    2019-10-19 16:34:16 default[20191019t220300]  "GET / HTTP/1.1" 304
    2019-10-19 16:34:18 default[20191019t220300]  Server listening on port 8081...
    2019-10-19 16:34:18 default[20191019t220300]  Database connection was refused.
    2019-10-19 16:34:22 default[20191019t220300]  "GET /getnews HTTP/1.1" 502
    2019-10-19 16:34:22 default[20191019t220300]  /srv/index.js:42
    2019-10-19 16:34:22 default[20191019t220300]      if (err) throw new Error(err)
    2019-10-19 16:34:22 default[20191019t220300]               ^
    2019-10-19 16:34:22 default[20191019t220300]
    2019-10-19 16:34:23 default[20191019t220300]  Error: Error: connect ECONNREFUSED /cloudsql/idyllic-anvil-256103:asia-south1:database      at Query._callback (/srv/index.js:42:20)      at Query.Sequence.end (/srv/node_modules/mysql/lib/protocol/sequences/Sequence.js:83:24)      at /srv/node_modules/mysql/lib/Pool.js:205:13      at Handshake.onConnect (/srv/node_modules/mysql/lib/Pool.js:58:9)      at Handshake.<anonymous> (/srv/node_modules/mysql/lib/Connection.js:525:10)      at Handshake._callback (/srv/node_modules/mysql/lib/Connection.js:491:16)      at Handshake.Sequence.end (/srv/node_modules/mysql/lib/protocol/sequences/Sequence.js:83:24)      at Protocol.handleNetworkError `enter code here`(/srv/node_modules/mysql/lib/protocol/Protocol.js:369:14)      at PoolConnection.Connection._handleNetworkError (/srv/node_modules/mysql/lib/Connection.js:421:18)      at Socket.emit (events.js:198:13)
theduck
  • 2,589
  • 13
  • 17
  • 23
Tej
  • 59
  • 1
  • 8
  • Have you followed [these instructions](https://cloud.google.com/sql/docs/mysql/connect-app-engine) for setup, including verifying that the app engine service account has the correct IAM roles? Also, are you using app engine standard or app engine flexible? – robsiemb Oct 19 '19 at 17:52
  • Additionally, "createPool.query(" looks odd, since createPool is a function that you called earlier and didn't save the result of. The [example you seem to be using](https://github.com/GoogleCloudPlatform/nodejs-docs-samples/blob/master/cloud-sql/mysql/mysql/server.js) defines a pool variable to save the result in. You should probably use pool.query(. – robsiemb Oct 19 '19 at 17:55
  • i am using standard environment,where should i add the default sevice account while using standard environment. – Tej Oct 20 '19 at 16:08
  • The documentation I linked mentions which roles are required. You should also double check your code, as I mentioned above (but this is unlikely to be the connection issue itself) – robsiemb Oct 20 '19 at 16:29
  • i have added roles as owner for the account i am using and i have updated he code with pool.query – Tej Oct 20 '19 at 18:31
  • Hi, are your App engine and your Cloud SQl instance hosted in the same project? – Chris32 Oct 21 '19 at 08:23

1 Answers1

1

Make sure you follow the instructions at Connecting to Cloud SQL from App Engine. In particular, the following things are easy to miss:

  • Make sure your service account connecting has the Cloud SQL Client IAM role, or the permissions listed on the page
  • If using App Engine Flex, make sure you've spelled it correctly in your app.yaml

Finally, make sure to take a look at Managing Database Connections page. In particular, in the example you have above you are initializing a function with a name, and then calling query on that function. Instead, you should use the pool to do your queries:


let pool; // <---- This is the pool that `createPool` will set
const createPool = async () => {
  pool = await mysql.createPool({
    socketPath:"/cloudsql/idyllic-anvil-256103:asia-south1:database",
    user: "abc@gmail.com", //<--- just FYI, this should be the database user, not the GCP account connecting
    password: "pass",
    database: "db"
  });
};
createPool(); // <----- This is where `pool` is actually created


// Now we can use the pool to query like this:
try {
  const stmt = 'INSERT INTO votes (time_cast, candidate) VALUES (?, ?)';
  // Pool.query automatically checks out, uses, and releases a connection
  // back into the pool, ensuring it is always returned successfully.
  await pool.query(stmt, [timestamp, team]);
} catch (err) {
  // If something goes wrong, handle the error in this section. This might
  // involve retrying or adjusting parameters depending on the situation.
  // ...
}
kurtisvg
  • 3,412
  • 1
  • 8
  • 24