1

I've built a Node.js API that connects to a MySQL database. I use node-mysql2 as driver. The API and the database run in separate Docker container. At some point after deployment in a Kubernetes cluster I get the following error:

Error: Can't add new command when connection is in closed state                                                                                                                                                                                                    
    at PromiseConnection.query (/usr/src/app/node_modules/mysql2/promise.js:92:22)

I wonder why this error happens and how to catch and handle it using Node.js. These are code snippets of my Node.js API:

const mysql = require('mysql2/promise')
...

async function main() {
    try {
        const client = await mysql.createConnection({
            host: DATABASE_HOST,
            port: DATABASE_PORT,
            user: DATABASE_USERNAME,
            password: DATABASE_PASSWORD,
            database: DATABASE_NAME
        })

        client.on('error', error => {
            process.stderr.write(`${error.code}\n`) // PROTOCOL_CONNECTION_LOST
            process.stderr.write(`An error occurred while connecting to the db: ${error.message}\n`)
            process.exit(1)
        })

    } catch (error) {
        process.stderr.write(`Error while creating db connection: ${error.code}\n`)
    }

    ...
}

...


main().catch(err => {
  process.stderr.write(`Error message: ${err.message}\n`)
  process.exit(1)
})

Do you have an idea how to handle this error?

jengeb
  • 393
  • 1
  • 7
  • 21

1 Answers1

0

Do you close the connection after finishing with it?

client.end();

Also considered using a pool?

const pool = mysql.createPool({
  host: DATABASE_HOST,
  user: DATABASE_USERNAME,
  database: DATABASE_NAME,
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
});

More info about pools: https://github.com/sidorares/node-mysql2#using-connection-pools

Horatiu Jeflea
  • 7,256
  • 6
  • 38
  • 67
  • Thanks for your answer. But I read that "In general, you reuse connections instead of opening/closing all the time." (see https://stackoverflow.com/questions/19563474/how-to-close-database-connection-in-node-js) – jengeb Oct 10 '19 at 09:50
  • No, I haven't used pool connections so far. What's the advantage? – jengeb Oct 10 '19 at 09:51
  • 1
    Manages your connections and promotes reusability (if there are any idle). https://en.wikipedia.org/wiki/Connection_pool – Horatiu Jeflea Oct 10 '19 at 10:13