我无法从应用程序引擎中托管的nodejs应用程序连接到gcloud sql

Question

我在谷歌云上有一个托管节点应用程序和mysql。我尝试了所有可能的方法从节点连接mysql，但每次都会引发此错误。

let pool;
const createPool = async () => {
pool = await mysql.createPool({
//   host: "35.200.129.217",
socketPath:"/cloudsql/idyllic-anvil-256103:asia-south1:database",
user: "[e-mail]",
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);
})
});

错误：

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)

Answer 1

请务必按照从 App Engine 连接到 Cloud SQL 中的说明进行操作。特别是，以下几点很容易被忽略：

• 确保您的服务账户连接具有Cloud SQL ClientIAM 角色或页面上列出的权限
• 如果使用 App Engine Flex，请确保您在app.yaml中拼写正确

最后，请务必查看管理数据库连接页面。特别是，在上面的示例中，您将使用名称初始化一个函数，然后对该函数调用查询。相反，您应该使用池来执行查询：

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: "[e-mail]", //<--- 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.
// ...
}