[createConnection] Can't add new command when connection is in closed state

[valcos-eb]

Hello!

Checking previous issues on Can't add new command when connection is in closed state, it seems that there is no workaround for this error and createConnection. Is this right or am I missing something?

I'm using MySQL 8 and mysql2 3.9.7.

[sidorares]

usually this means that you missed error handling somewhere and try to do something on a connection that is no longer alive ( you closed it earlier, or server disconnected it's side of a connection, or network link dies and OS marked tcp connection as closed and that was propagated as net socket 'error' event )

The connection itself is not designed to "reconnect", all state is meaningless for a new connection anyway. Whenever fatal error happens you supposed to discard a connection object and create a new one. Easiest way to do all that automatically is to use a pool ( even if you don't need pool for performance reasons )

[valcos-eb]

Thank you @sidorares for the prompt reply!

I've been digging a bit more into the issue. TLDR: the problem seems basically the one reported at #1398. The db has been recently updated from version 5 to 8 (mysql_aurora.3.06, which maps to 8.0.34, ref here). The new version is using utf8mb3 as charset, and from what I understand mysql2 doesn't support it anymore (am I right?).

I've tried to set the charset when creating the connection with createConnection, but the error is still there. Do you have any idea about how handle utf8mb3 with mysql2 3.9.7?

Long story:
In my code I have an insert and a select, which use the same connection and are executed one after the other.

I was seeing the error Can't add new command when connection is in closed on the select. Quite interesting was that the insert was creating a new row, and after that the connection was closed. So, the error was still there even when switching from the single connection to the pool (this might explain why someone reports that Can't add new command when connection is in closed exists also for the pool). By adding more logs in my code, I found the error Encoding not recognized: 'undefined', and then bumped into #1398.

[valcos-eb]

I've been trying different charset when creating the connection, but the error is still there. I've also enabled the debug flag, but the info doesn't seem useful (it is mostly alphanumeric strings, and statements like Execute#unknown name(0,,97)).

The connection gets closed after update and insert statements. As temporally workaround, I'm executing a connection.ping(), which seems to restore the connection.

....
import type { Connection, ResultSetHeader } from "mysql2/promise";

export class MySqlRepository {
  private _connection: Connection;

  constructor(mysqlConnection: Connection) {
    this._connection = mysqlConnection;
  }

  get connection(): Connection {
    (async () => {
      await this._connection.ping();
    })()
      .then(() => {
        console.log("Connection established");
      })
      .catch(() => {
        console.log("Connection lost");
      });

    return this._connection;
  }

  async create(
    ...
  ): Promise<string> {
    ...
    try {
      const response = await this.connection.execute(
        myQuery,
        MyQueryValues
      );
     ...
  }
}

[valcos-eb]

I've written some code in python (using pymsql) to check if the problem is on the DB side. In python, I'm not able to replicate the issue, the connection doesn't get closed after executing an insert statement

[sidorares]

@valcos-eb can you make a repro repo so I can try to debug you issue locally?

[valcos-eb]

hey @sidorares, sure! For the moment I'm not able to reproduce the issue locally.

I've tried with a docker-compose (https://github.com/valeriocos/test-mysql2), hoping that this was enough to replicate the error.

Next week I'll try with cdk + lambda (which is a better approximation of my use case), and let you if the issue is reproduced.

[outbackStack]

I'm experiencing this same issue. I have it as a pool already but sometime it can't get the connection anymore. The problem is that the error is not consistent so I don't know how to replicate it.

let conn = mainPool.getConnection();

[valcos-eb]

hey @outbackStack, if this can be of any help, the solution commented here is working in a prod environment (for the moment it seems an effective workaround)