Pool > Execute > Uncaught error

[michaelpeterlee]

We experience a problem with execute(); it errors and we cannot handle in the immediate codeblock, is handled outside the immediate try/catch context.

execute() works outright in this context, however we receive this error after 20 mins of app runtime for some reason.

Q. Does anything look untoward in the execute() debug, below?

Add command: Prepare
Add command: Execute
6 13392050 <== Execute#unknown name(0,,81)
6 13392050 <== 4d000000170300000000010000000001fd00fd00050018494e564f4b45442072656672657368546f6b656e545328291941636365737320746f6b656e2068617320657870697265642e00807dad852d7842
Add command: Prepare
Add command: Execute
2 13392018 <== Execute#unknown name(0,,42)
2 13392018 <== 26000000170300000000010000000201fd00060005000a52454652455348494e4700807dad852d784200
Add command: Prepare
0 13392019 <== Prepare#unknown name(0,,27)
0 13392019 <== 170000001653454c45435420746f6b656e2046524f4d2075736572
Add command: Execute
Fri Aug 26 2022 16:13:28 GMT+1200 (New Zealand Standard Time) 1661487208417 Error {"stack":"UnhandledPromiseRejection: This error originated either by throwing inside of an async function without a catch block, or by rejecting a promise which was not handled with .catch(). The promise rejected with the reason \"#<Object>\".","message":"This error originated either by throwing inside of an async function without a catch block, or by rejecting a promise which was not handled with .catch(). The promise rejected with the reason \"#<Object>\".","name":"UnhandledPromiseRejection","code":"ERR_UNHANDLED_REJECTION"}

db = mysql.createPool({
        ..._oConfig,
        waitForConnections: true,
        connectionLimit: 10,
        queueLimit: 0,
        debug: true,
});

const myFunction = async () => {
    try {
        const [rows, fields] = await db.execute('SELECT * FROM user', []);
    } catch (oError) {
        console.log('HUH',oError); // NOTE: Not caught!
    }
};

[sidorares]

probably related https://github.com/sidorares/node-mysql2/issues/1505

[sidorares]

also https://github.com/sidorares/node-mysql2/issues/1297

[michaelpeterlee]

Thanks for responding, much appreciated.

We will not implement this library until errors bubble to callee.

[sidorares]

I just merged #1359 , can you try to install mysql2 from git master and see if that works for you?

[michaelpeterlee]

Updated to no-avail unfortunately.

git clone https://github.com/sidorares/node-mysql2

Then we replaced node_modules/mysql2.

[t638403]

Does not seem to work for me either.

Using "mysql2": "^2.3.3".

It errors because of a missing value for publish in the data whlie it is defined as a named parameter.

TypeError: Bind parameters must not contain undefined. To pass SQL NULL specify JS null

This is my code:

const mysql = require('mysql2/promise');
(async () => {

  const pool = mysql.createPool({
    host:     process.env.MYSQL_HOST,
    database: process.env.MYSQL_DATABASE,
    port:     process.env.MYSQL_PORT,
    user:     process.env.MYSQL_USER,
    password: process.env.MYSQL_PASSWORD,
    namedPlaceholders: true
  });
  const sql = `INSERT INTO content (name, content, publish) VALUES (:name, :content, :publish})`;
  try {
    const response = await pool.execute(sql, {name:'some name', content:'some content'});
    console.log(response);
  } catch (e) {
    console.log('Hooray I caught an error.')
  }

})();

[cosminbodnariuc]

Having the same error on version mysql2: ^3.1.2, is there any update on this? @sidorares. Thanks!

[sidorares]

@cosminbodnariuc do you have a short reliable way to reproduce?

[cosminbodnariuc]

@sidorares The pool config:

const poolConfig: mysql.PoolOptions = {
    user: "-",
    password: "-",
    database: "-",
    connectionLimit: 20,
    connectTimeout: 30000, // 30sec
    supportBigNumbers: true,
    multipleStatements: true,
    charset: "utf8mb4_unicode_ci",
    host: "mysql",
    port: 3306,
    socketPath: "-",
  };

The implementation (Typescript):

import * as mysql from "mysql2/promise";
import {FieldPacket, OkPacket, ResultSetHeader, RowDataPacket} from "mysql2";
import {Logger, MySqlService} from "../../";
import assert from "assert";

export class MySqlServiceImpl implements MySqlService {
  private pool: mysql.Pool | undefined;

  constructor(
    private readonly poolConfig: mysql.PoolOptions,
    private readonly logger: Logger
  ) {
  }

  async createPool(): Promise<mysql.Pool> {
    if (this.pool !== undefined) {
      return this.pool;
    }

    this.pool = await mysql.createPool(this.poolConfig);
    return this.pool;
  }

  async query<T extends RowDataPacket[][] | RowDataPacket[] | OkPacket | OkPacket[] | ResultSetHeader>(
    sql: string,
    values: any | any[] | { [param: string]: any },
    maxTry = 1, retryCount = 1,
  ): Promise<[T, FieldPacket[]]> {
    assert(this.pool !== undefined,
      "A pool connection must be created by calling [createPool] before calling this method.");

    try {
      return this.pool.query<T>(sql, values);
    } catch (error: any) {
      this.logger.log(`MySqlService.query ERROR: ${JSON.stringify(error)}`);
      if (error.code === "PROTOCOL_CONNECTION_LOST") {
        this.logger.log(`Retry pool query ${retryCount} failed.\n Error: ${JSON.stringify(error)}`);
        if (retryCount > maxTry) {
          throw error;
        }

        await this.delay((retryCount / 2) * 1000);
        await this.pool.end();
        this.pool = await mysql.createPool(this.poolConfig);
        return this.query(sql, values, maxTry, retryCount + 1);
      } else {
        throw error;
      }
    }
  }

  private async delay(ms: number): Promise<void> {
    return new Promise<void>((resolve) => setTimeout(resolve, ms));
  }
}

The compiled Js version of the above code:

"use strict";
var __createBinding = (this && this.__createBinding) || (Object.create ? (function(o, m, k, k2) {
    if (k2 === undefined) k2 = k;
    var desc = Object.getOwnPropertyDescriptor(m, k);
    if (!desc || ("get" in desc ? !m.__esModule : desc.writable || desc.configurable)) {
      desc = { enumerable: true, get: function() { return m[k]; } };
    }
    Object.defineProperty(o, k2, desc);
}) : (function(o, m, k, k2) {
    if (k2 === undefined) k2 = k;
    o[k2] = m[k];
}));
var __setModuleDefault = (this && this.__setModuleDefault) || (Object.create ? (function(o, v) {
    Object.defineProperty(o, "default", { enumerable: true, value: v });
}) : function(o, v) {
    o["default"] = v;
});
var __importStar = (this && this.__importStar) || function (mod) {
    if (mod && mod.__esModule) return mod;
    var result = {};
    if (mod != null) for (var k in mod) if (k !== "default" && Object.prototype.hasOwnProperty.call(mod, k)) __createBinding(result, mod, k);
    __setModuleDefault(result, mod);
    return result;
};
var __importDefault = (this && this.__importDefault) || function (mod) {
    return (mod && mod.__esModule) ? mod : { "default": mod };
};
Object.defineProperty(exports, "__esModule", { value: true });
exports.MySqlServiceImpl = void 0;
const mysql = __importStar(require("mysql2/promise"));
const assert_1 = __importDefault(require("assert"));
class MySqlServiceImpl {
    constructor(poolConfig, logger) {
        this.poolConfig = poolConfig;
        this.logger = logger;
    }
    async createPool() {
        if (this.pool !== undefined) {
            return this.pool;
        }
        this.pool = await mysql.createPool(this.poolConfig);
        return this.pool;
    }
    async query(sql, values, maxTry = 1, retryCount = 1) {
        (0, assert_1.default)(this.pool !== undefined, "A pool connection must be created by calling [createPool] before calling this method.");
        try {
            return this.pool.query(sql, values);
        }
        catch (error) {
            this.logger.log(`MySqlService.query ERROR: ${JSON.stringify(error)}`);
            if (error.code === "PROTOCOL_CONNECTION_LOST") {
                this.logger.log(`Retry pool query ${retryCount} failed.\n Error: ${JSON.stringify(error)}`);
                if (retryCount > maxTry) {
                    throw error;
                }
                await this.delay((retryCount / 2) * 1000);
                await this.pool.end();
                this.pool = await mysql.createPool(this.poolConfig);
                return this.query(sql, values, maxTry, retryCount + 1);
            }
            else {
                throw error;
            }
        }
    }
    async delay(ms) {
        return new Promise((resolve) => setTimeout(resolve, ms));
    }
    sqlifyInsert(data) {
        const entries = Object.entries(data);
        const columns = [];
        const values = [];
        const params = [];
        for (const [key, value] of entries) {
            columns.push(`\`${key}\``);
            values.push("?");
            params.push(value);
        }
        return { columns: columns.join(", "), values: values.join(", "), params };
    }
    sqlifyUpdate(data) {
        const entries = Object.entries(data);
        const values = [];
        const params = [];
        for (const [key, value] of entries) {
            values.push(`\`${key}\` = ?`);
            params.push(value);
        }
        return { values: values.join(", "), params };
    }
}
exports.MySqlServiceImpl = MySqlServiceImpl;
//# sourceMappingURL=mysql.service.impl.js.map

And here is the error with the stackTrace from the google cloud logs:

- stack: Error: Connection lost: The server closed the connection.
- at .PromisePool.query ( /usr/src/app/node_modules/mysql2/promise.js:341 )
- at .MySqlServiceImpl.query ( /usr/src/app/lib/app/data/impl/mysql.service.impl.js:43 )
- at .CalendarDatabaseImpl.query ( /usr/src/app/lib/app/data/impl/calendar.db.impl.js:83 )
- at .processTicksAndRejections ( node:internal/process/task_queues:96 )

The CalendarDatabaseImpl calls the query method from the MySqlService

[cosminbodnariuc]

@sidorares Do you have any suggestions or a workaround for the above? Thanks

[sidorares]

@cosminbodnariuc could you strip all the parts that not related to the issue you describing but add any dependant code?

[cosminbodnariuc]

@sidorares Sure, here is a minimal code sample that should reproduce the issue:

import * as mysql from "mysql2/promise";
import assert from "assert";


const poolConfig: mysql.PoolOptions = {
  user: "-",
  password: "-",
  database: "-",
  connectionLimit: 20,
  connectTimeout: 30000, // 30sec
  supportBigNumbers: true,
  multipleStatements: true,
  charset: "utf8mb4_unicode_ci",
  host: "mysql",
  port: 3306,
  socketPath: "-",
};

let pool = mysql.createPool(poolConfig);

async function main() {
  // language=MySQL
  const statement = "select 1 + 1;";
  const result = query(statement, []);
  console.log(`Result: ${result}`);

  process.exit(1);
}


async function query<T extends mysql.RowDataPacket[][] | mysql.RowDataPacket[] | mysql.OkPacket | mysql.OkPacket[] | mysql.ResultSetHeader>(
  sql: string,
  values: any | any[] | { [param: string]: any },
  maxTry = 1, retryCount = 1,
): Promise<[T, mysql.FieldPacket[]]> {
  assert(pool !== undefined, "The pool object is not initialized");

  try {
    return pool.query<T>(sql, values);
  } catch (error: any) {
    // This block is never reached.
    console.log(`Mysql query ERROR: ${JSON.stringify(error)}`);
    if (error.code === "PROTOCOL_CONNECTION_LOST") {
      console.log(`Retry pool query ${retryCount} failed.\n Error: ${JSON.stringify(error)}`);
      if (retryCount > maxTry) {
        throw error;
      }

      await delay((retryCount / 2) * 1000);
      await pool.end();
      pool = await mysql.createPool(poolConfig);
      return query(sql, values, maxTry, retryCount + 1);
    } else {
      throw error;
    }
  }
}

async function delay(ms: number): Promise<void> {
  return new Promise<void>((resolve) => setTimeout(resolve, ms));
}

main();

[michaelpeterlee]

We cannot use this library as need to handle exceptions.

If we can define/debug the problem here, perhaps another developer can assist.

[abentpole]

@cosminbodnariuc in your minimal example, you are never awaiting the call to query within main, is that intentional?