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 based on the suggestions on Stack Overflow (here and here) and MySQL forums? — Robert G
– Robert G, Commented May 18, 2023 at 18:37

Manuel Spigolon · Accepted Answer · 2023-05-18 18:57:04Z

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, 2023 at 18:57

Manuel Spigolon

13.4k7 gold badges60 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Dally Over a year ago

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.

Manuel Spigolon Over a year ago

If the query throws and error, the release is skipped - you should add a try/catch/finally statement

Dally Over a year ago

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.

Manuel Spigolon Over a year ago

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

Collectives™ on Stack Overflow

Best way to handle MySQL PROTOCOL_CONNECTION_LOST in Fastify - GCP hosted

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related