Best way to handle MySQL PROTOCOL_CONNECTION_LOST in Fastify - GCP hosted

Question

I'm getting a couple of PROTOCOL_CONNECTION_LOST error codes in production for one of my projects when running SQL queries. What is the best way to handle this in Fastify?

I've searched Stackoverflow but there doesn't appear to be definitive answer to this. I've changed the MySQL config to allow for a higher connection limit but it hasn't solved the problem.

Traffic really isn't very high at the moment for this project and my concern is that when traffic does ramp up, this issue will become more prominent.

The project is hosted on GCP using Cloud Run and it's using GCP Cloud SQL. Unsure whether it's something GCP related that I may have to change but the production DB is running on a High Memory machine (4vCPU's, 26gb memory, 100gb SSD, high availability enabled).

The Cloud Run container has 4 CPU's, 2gb memory, 3600 request timeout, 1000 concurrent requests, startup CPU boost enabled, minimum number of instances set to 1 and max set to 100.

MySQL Plugin:

import fp from 'fastify-plugin'
import mysql from '@fastify/mysql'

const plugin = async (fastify, options, done) => {
    const { config } = fastify;

    fastify.register(mysql, {
        host: config.DB_HOST,
        socketPath: config.DB_SOCKET,
        port: config.DB_PORT,
        user: config.DB_USERNAME,
        password: config.DB_PASSWORD,
        database: config.DB_NAME,
        namedPlaceholders: true,
        promise: true,
        connectionLimit: 100,
        waitForConnections: true
    })

    done()
}

export default fp(plugin)

Error message:

Error: Connection lost: The server closed the connection.
    at PromisePoolConnection.query (/app/node_modules/mysql2/promise.js:93:22)
    at Object.getErrorSyncedTransactions (file:///app/models/cron.js:33:41)
    at runMicrotasks (<anonymous>)
    at processTicksAndRejections (node:internal/process/task_queues:96:5)
    at async Object.<anonymous> (file:///app/routes/v1/cron.js:89:39) {
  code: 'PROTOCOL_CONNECTION_LOST',
  errno: undefined,
  sql: undefined,
  sqlState: undefined,
  sqlMessage: undefined
}

Example Query:

import fp from 'fastify-plugin'

const model = (fastify, options, done) => {

    const getPendingPayments = async () => {
        const connection = await fastify.mysql.getConnection()
        const [rows] = await connection.query(
            `SELECT
            t.id,
            t.uuid,
            t.status_code,
            y.consent_token AS consent_token,
            y.payment_payload->>'$.data.id' AS payment_id,
            m.id AS merchant_id,
            m.uuid AS merchant_uuid,
            m.webhook_url,
            m.webhook_status
            FROM transactions t 
            JOIN transactions_yapily y 
            ON t.id = y.transaction_id
            JOIN merchants m
            ON m.id = t.merchant_id
            WHERE y.payment_payload IS NOT NULL
            AND t.status_code IN ('ACCP', 'ACSP', 'ACTC', 'ACWC', 'ACWP', 'ACFC', 'RCVD', 'PDNG')
            AND t.created_at >= DATE_SUB(NOW(), INTERVAL 3 HOUR)`
        )
        connection.release()
        return rows
    }

    fastify.decorate('cron', {
        getPendingPayments,
    })

    done()
}

export default fp(model)

Have you tried [error handling](https://github.com/mysqljs/mysql#error-handling) based on the suggestions on Stack Overflow ([here](https://stackoverflow.com/questions/62914438/db-error-connection-lost-server-closed-the-connection) and [here](https://stackoverflow.com/questions/56898269/nodejs-mysql-connection-lost-the-server-closed-the-connection)) and [MySQL forums](https://forums.mysql.com/read.php?44,688548,688612#msg-688612)? — Robert G, May 18 '23 at 18:37

score 0 · Answer 1 · answered May 18 '23 at 18:57

0

The code shows that there is a mix of async and sync code style.

In an async plugin function, do not use the done callback. This leads to unexpected behaviours.

Otherwise, it is possible to use the done callback by removing the async keyword.

import fp from 'fastify-plugin'
import mysql from '@fastify/mysql'

const plugin = async (fastify, options) => {
    const { config } = fastify;

    fastify.register(mysql, {
        host: config.DB_HOST,
        socketPath: config.DB_SOCKET,
        port: config.DB_PORT,
        user: config.DB_USERNAME,
        password: config.DB_PASSWORD,
        database: config.DB_NAME,
        namedPlaceholders: true,
        promise: true,
        connectionLimit: 100,
        waitForConnections: true
    })

    // done() in an async function do not use the `done` callback
}

export default fp(plugin)

Another important thing, since you are using the promise: true parameter, always release the connections in the handlers: connection.release()

answered May 18 '23 at 18:57

Manuel Spigolon

11,003
5
50
73

Hi Manuel, thank you for that suggestion. I completely missed the fact that ```done()``` does not need to be called in async functions. I am using ```connection.release()``` in all of my queries which are all async by the way. I've put an example in my original post of what my models that perform SQL queries look like. I'm still unsure whether this will fix the ```PROTOCOL_CONNECTION_LOST``` problem but if it doesn't, should I implement some sort of a fallback for that error. – Dally May 18 '23 at 20:42
If the query throws and error, the release is skipped - you should add a try/catch/finally statement – Manuel Spigolon May 19 '23 at 05:42
Do you mean if the query throws an ```PROTOCOL_CONNECTION_LOST``` error as the query itself shouldn't fail as it's just a simple ```SELECT``` statement. – Dally May 19 '23 at 08:30
A select could fail for many reasons due the input. Anyway I think that one of the main issue was the `done()` callback because calling it may not wait to establish correctly the db connection with the server so your handler may execute before even the connection is ready to use - generating a domino effect – Manuel Spigolon May 19 '23 at 10:14

Best way to handle MySQL PROTOCOL_CONNECTION_LOST in Fastify - GCP hosted

1 Answers1