search

sidorares / node-mysql2

:zap: fast mysqljs/mysql compatible mysql driver for node.js
https://sidorares.github.io/node-mysql2/
MIT License
3.99k stars 603 forks source link

Connection pool Google Cloud SQL connection loss issue using Cloud Functions for Firebase #1011

Open kireerik opened 4 years ago

kireerik commented 4 years ago

I am using connection pools with Promise wrapper in Cloud Functions for Firebase connecting to a Google Cloud SQL (for MySQL) instance.

I follow all the Connecting to Cloud SQL Connection Reuse recommendations:

I also follow all the Connecting to Cloud SQL Connection Pools & Connection Loss recommendations:

Sometimes the following error occurs in production:

Unhandled rejection
Error: Connection lost: The server closed the connection.
    at PromisePool.query (/srv/node_modules/mysql2/promise.js:330:22)
.
.
.
    at functions.region.https.onRequest (/srv/index/handleRequest/index.js:10:3)
    at cloudFunction (/srv/node_modules/firebase-functions/lib/providers/https.js:49:9)
    at /worker/worker.js:783:7
    at /worker/worker.js:766:11
    at _combinedTickCallback (internal/process/next_tick.js:132:7)

As I read this library should reconnect by it self: https://github.com/sidorares/node-mysql2/issues/572#issuecomment-304774468 https://github.com/sidorares/node-mysql2/issues/836#issuecomment-414281593 Am I correct?

(Somewhat similar issue: https://github.com/mysqljs/mysql/issues/2151)

Relevant source code: mysqlPool.js:

const functions = require('firebase-functions')
, mysql = require('mysql2')

, {mysql: {user, password, database}} = functions.config()

, mysqlConfig = {
    user, password, database
    , connectionLimit: 1
    , decimalNumbers: true
}

if (process.env.NODE_ENV == 'production')
    mysqlConfig.socketPath = '/cloudsql/'
        + '<CLOUD SQL INSTANCE CONNECTION NAME>'

module.exports = mysql.createPool(mysqlConfig).promise()

Usage:

const mysqlPool = require('./mysqlPool')

I am using the mysqlPool.query and mysqlPool.execute functions multiple times as I need them.

Versions:

kireerik commented 4 years ago

@sidorares What do you think? The connection pool should reconnect on it's own, am I correct?

sidorares commented 4 years ago

Didn't have time to look into it yet. Pool doesn't actively reconnect. When you ask new connection from pool, it's reused if there is one idle available, or new connection created if all busy and you are below the limit of active connections (If over the limit you wait until somebody return connection)

When active connection dies for any reason it's just removed from the pool

niteshv0909 commented 4 years ago

I am also facing same issue with pool. here is my connection.js

const pool = mysql.createPool({
    connectionLimit: 3,
    host: config.database.host,
    user: config.database.user,
    password: config.database.password,
    database: config.database.db,

});
const promisifiedPool = pool.promise();

const executeQuery = async (query) => {
    return new Promise((resolve, reject) => {
        promisifiedPool.query(query)
            .then(([rows, fields]) => {
                return resolve(rows);
            }).catch(reject);
    });
};

Following is my stack trace. 

{ Error: Connection lost: The server closed the connection.
    at PromisePool.query (/var/www/lbb-product-feeds/node_modules/mysql2/promise.js:330:22)
    at /var/www/lbb-product-feeds/src/core/mysql.js:16:25
    at new Promise (<anonymous>)
    at executeQuery$ (/var/www/lbb-product-feeds/src/core/mysql.js:15:12)
    at tryCatch (/var/www/lbb-product-feeds/node_modules/regenerator-runtime/runtime.js:45:40)
    at Generator.invoke [as _invoke] (/var/www/lbb-product-feeds/node_modules/regenerator-runtime/runtime.js:271:22)
    at Generator.prototype.(anonymous function) [as next] (/var/www/lbb-product-feeds/node_modules/regenerator-runtime/runtime.js:97:21)
    at tryCatch (/var/www/lbb-product-feeds/node_modules/regenerator-runtime/runtime.js:45:40)
    at invoke (/var/www/lbb-product-feeds/node_modules/regenerator-runtime/runtime.js:135:20)
    at /var/www/lbb-product-feeds/node_modules/regenerator-runtime/runtime.js:170:11
  message: 'Connection lost: The server closed the connection.',
  code: 'PROTOCOL_CONNECTION_LOST',
  errno: undefined,
  sqlState: undefined,
  sqlMessage: undefined }

Versions : node : 8.16.1 mysql2: ^2.0.0

cfh-se commented 3 years ago

I have the exact same problem. Anyone found a solution?

mattostanik commented 3 years ago

I am having the same issue as noted by several others above. I am receiving a 'PROTOCOL_CONNECTION_LOST' error while connecting to a Google Cloud SQL instance from a Cloud Function.

There are a couple Stack Overflow discussions where someone has experienced the error with an individual connection, and the recommended solution is to switch to a connection pool. However I am using a connection pool already, similar to the others who have reported this.

const pool = mysql.createPool({
    socketPath  : '/cloudsql/project:us-central1:db1',
    user        : 'user1',
    password    : 'abc',
    database    : 'db',
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0
});

On the positive side, finding this discussion and open issue at least made me feel like I wasn't crazy or doing something completely wrong. This is a real unresolved issue even with connection pools.

My cloud function is reading a batch of 20 records from my SQL database, looping thru them and retrieving additional data for each record from an external API, then saving each record back to the db with an individual query. In a typical batch of 20 write queries, I am seeing anywhere from zero to 6 of them experience the timeout error. I'd say an average is 1 or 2 errors per batch, so a 5% or 10% incident rate.

This is not a great solution, but my fix is to put my query in a promise and watch for the error. If a timeout error occurs, it then retries the query. 90% of the time it succeeds on the second attempt. My code allows up to 5 attempts if necessary. It's not super elegant or efficient, but it keeps my function from losing the data.

      // function to save data to SQL database
      const saveData = async (param1, param2, param3, param4, param5, param6, param7) => {
        var success = false;
        var attempts = 1;

        while ((!success) && (attempts <= 5)) {
          success = await executeQuery(param1, param2, param3, param4, param5, param6, param7, attempts).catch(err => { console.log(err) });
          attempts += 1;

        }
      }

      // function to execute the SQL query inside a promise
      const executeQuery = (param1, param2, param3, param4, param5, param6, param7, attempts) => {

        return new Promise((resolve, reject) => {
          pool.query("update table1 set field1 = ?, field2 = ?, field3 = ?, field4= ?, field5 = ?, field6 = ? where field7 = ?", [param1, param2, param3, param4, param5, param6, param7], function(err, results) {

            if ((err) && ((err.code === 'PROTOCOL_CONNECTION_LOST') || (err.code === 'ETIMEDOUT'))) {
              return reject(false);
            } else {
              return resolve(true);
            }
          });
        });
      }

It would be great if there is a better solution to this issue in the future.

iturn commented 1 year ago

This also happens on cloud run

doender commented 10 months ago

I'm also facing this on cloud run now and then. Are there any configuration changes we can make to alleviate this issue?

adminDelcom commented 10 months ago

None that i've found @doender

panzerdp commented 10 months ago

Having the same problem on Cloud Run.

iturn commented 10 months ago

Are all if you also using the v2.x of this package? I haven't tried the v3 branche

panzerdp commented 10 months ago

Are all if you also using the v2.x of this package? I haven't tried the v3 branche

@iturn I'm using the version 3.6.0.

iturn commented 10 months ago

Ok so it remains a issue, well in any case i had a lot of google cloud premium support on it and they were sure it wasnt on their side

N-Andronopoulos commented 9 months ago

NestJS in Cloud Run with: "mysql2": "^3.6.0", "typeorm": "0.3.17"

Settings: 

{
  type: 'mysql',
  socketPath: `${cfg.get('DB_SOCKET_PATH') || '/cloudsql'}/${cfg.get('INSTANCE_CONNECTION_NAME')}`,
  username: cfg.get('DB_USER'),
  password: cfg.get('DB_PASS'),
  database: cfg.get('DB_NAME'),
  autoLoadEntities: true,
  synchronize: false,
  logging: false,
  keepConnectionAlive: true,
  connectTimeout: 5000,
  extra: {
    // Connection Pooling
    waitForConnections: true,
    connectionLimit: 80,
    maxIdle: 10, // max idle connections, the default value is the same as `connectionLimit`
    idleTimeout: 60000, // idle connections timeout, in milliseconds, the default value 60000
    queueLimit: 0,
  }
}

Happens to us too 8 times per month. Dunno if SQL Cloud proxy related.

Maybe we should use a lower idleTimeout: 60000 than wait_timeout: 28800?

weilinzung commented 8 months ago

We are using Cloud Run and have this error too with an Express app, here is the stack trace:

QueryFailedError
home/node_modules/.pnpm/typeorm@0.2.45_ioredis@5.3.2_mysql2@3.6.0_redis@3.1.1/node_modules/src/error/TypeORMError.ts:7
Connection lost: The server closed the connection.
Oct 19th 2023, 01:45:42 EDT

STACKTRACE

QueryFailedError Connection lost: The server closed the connection. 
    home/node_modules/.pnpm/typeorm@0.2.45_ioredis@5.3.2_mysql2@3.6.0_redis@3.1.1/node_modules/src/error/TypeORMError.ts:7:8 e.TypeORMError [as constructor]
    home/node_modules/.pnpm/typeorm@0.2.45_ioredis@5.3.2_mysql2@3.6.0_redis@3.1.1/node_modules/src/error/QueryFailedError.ts:9:8 new QueryFailedError
    home/node_modules/.pnpm/typeorm@0.2.45_ioredis@5.3.2_mysql2@3.6.0_redis@3.1.1/node_modules/typeorm/src/driver/mysql/MysqlQueryRunner.ts:196:36 t.onResult
    home/node_modules/.pnpm/mysql2@3.6.0/node_modules/mysql2/lib/connection.js:228:20 _k._notifyError
    home/node_modules/.pnpm/mysql2@3.6.0/node_modules/mysql2/lib/connection.js:123:11 Socket.<anonymous>
    node:events:513:28 Socket.emit
    node:domain:489:12 Socket.emit
    node:net:301:12 Pipe.<anonymous>
    node:internal/async_hooks:130:17 Pipe.callbackTrampoline
edudesouza commented 3 weeks ago

Hi there I had the same problem and resolve this way:

`const con_prod = mysql.createPool({

connectionLimit : 50, socketPath : "/cloudsql/......", user : ".....", password : ".....", database : "......", maxIdle : 10, idleTimeout : 60000, queueLimit : 0, enableKeepAlive : true, keepAliveInitialDelay : 0,

});`

`async function getConnectionFromPool(pool) {

return new Promise((resolve, reject) => {
  pool.getConnection((err, connection) => {

    if(err) {
      enviar_email("Edu", "eduardo@.......com.br", "ERRO BB Con SQL", sql_query+'<hr/>'+err.sqlMessage);   
    }else{
      resolve(connection);
    }          

  });
});

}`

then check the conection

const connection = await getConnectionFromPool(con_prod);

console.log('-> Conection: ', connection.state ); console.log('-> Conection: ', connection.threadId );

connection.query(sql_query, (err,rows) =>{}); connection.release();