Node mysql2 intermittent timeouts out when command line works fine

Viewed 164

I'm seeing an interesting MySQL connection timeout using node that I can't explain. It happens 1/70 times. Here is the code I'm running:

import { exec } from 'child_process';
import * as util from 'util';
import mysql from 'mysql2/promise';


const logDiagnostics = async (): Promise<void> => {
    const telnet = await pExec(
      `echo -e '\x1dclose\x0d' | telnet ${process.env.MYSQL_HOST} ${process.env.MYSQL_PORT}`
    );
    console.log(`[telnet] ${telnet.stdout} ${telnet.stderr}`);


    const mysql = await pExec(
      `echo "SELECT VERSION() AS version" | mysql -h ${process.env.MYSQL_HOST} -u ${process.env.MYSQL_USER} -p${process.env.MYSQL_PASSWORD}`
    );
    console.log(`[mysql] ${mysql.stdout} ${mysql.stderr}`);
}

const doSomeWork = async (): Promise<void> => {
    const connection = await mysql.createConnection({
      timezone: '+00:00',
      dateStrings: ['DATE', 'DATETIME'],
      decimalNumbers: true,
      host: process.env.MYSQL_HOST,
      user: process.env.MYSQL_USER,
      password: process.env.MYSQL_PASSWORD,
      port: parseInt(process.env.MYSQL_PORT, 10),
      database: process.env.MYSQL_DATABASE,
      connectTimeout: 30000,
      ssl: { ca: fs.readFileSync('...', 'utf8') }
    });

    // do some stuff

    await connection.end();
}

const run = async (): Promise<void> => {
    await logDiagnostics();
    try {
        await doSomeWork();
    } catch (e) {
        console.log(e);
    }
    await logDiagnostics();
}

With that I'll get logs like:

Nov 23 19:12:53Z - [telnet] Trying [IP REDACTED]...
Nov 23 19:12:53Z - Connected to [HOST REDACTED].
Nov 23 19:12:53Z - Escape character is '^]'.
Nov 23 19:12:53Z - telnet> close
Nov 23 19:12:53Z - Connection closed.

Nov 23 19:12:53Z - [mysql] version
Nov 23 19:12:53Z - 8.0.17

// 30s and then it times out?
Nov 23 19:13:23Z - Error: connect ETIMEDOUT

Nov 23 19:13:23Z - [telnet] Trying [IP REDACTED]...
Nov 23 19:13:23Z - Connected to [HOST REDACTED].
Nov 23 19:13:23Z - Escape character is '^]'.
Nov 23 19:13:23Z - telnet> close
Nov 23 19:13:23Z - Connection closed.

Nov 23 19:13:23Z - [mysql] version
Nov 23 19:13:23Z - 8.0.17

I've checked the database (RDS) and it's sitting idle. Minimal connections, low IOPs, 1% CPU, tons of free memory. Any ideas with how I'm misusing node's mysql library?

1 Answers

I was never able to fully identify the problem but the eventual fix was to colocate the server and database in the same AWS region. Originally the server was in us-east-1 while the db was in us-east-2. Putting them in the same region stopped the bug from popping up.

Related