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

[kireerik]

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:

• using a globally scoped connection pool

• not close connections at the end of the function call

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

• use a client library that supports connection pools that automatically reconnect broken client connections

• maximum connections is set to 1
• By now I am only using 1 connection pool per cloud function.

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:

• Node.js: 8.15.0 runtime
• mysql2: 1.7.0
• firebase-functions: 3.2.0

[kireerik]

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

[sidorares]

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]

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]

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

[mattostanik]

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]

This also happens on cloud run

[doender]

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

[adminDelcom]

None that i've found @doender

[panzerdp]

Having the same problem on Cloud Run.

[iturn]

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

[panzerdp]

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]

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]

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]

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

QueryFailedError
home/node_modules/.pnpm/[email protected][email protected][email protected][email protected]/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/[email protected][email protected][email protected][email protected]/node_modules/src/error/TypeORMError.ts:7:8 e.TypeORMError [as constructor]
    home/node_modules/.pnpm/[email protected][email protected][email protected][email protected]/node_modules/src/error/QueryFailedError.ts:9:8 new QueryFailedError
    home/node_modules/.pnpm/[email protected][email protected][email protected][email protected]/node_modules/typeorm/src/driver/mysql/MysqlQueryRunner.ts:196:36 t.onResult
    home/node_modules/.pnpm/[email protected]/node_modules/mysql2/lib/connection.js:228:20 _k._notifyError
    home/node_modules/.pnpm/[email protected]/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]

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", "[email protected]", "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();