MySQL Bugs: #104374: Pooling connection closed

Bug #104374	Pooling connection closed
Submitted:	21 Jul 2021 8:06	Modified:	6 Oct 2021 21:43
Reporter:	ALEKSANDAR MAKEDONSKI	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector for Node.js	Severity:	S3 (Non-critical)
Version:	8.0.26	OS:	Linux (Ubuntu 20.04)
Assigned to:	Rui Quelhas	CPU Architecture:	Any

Description:
Node version: 16.4.1
mysql  Ver 8.0.25-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))

When create many multiple queries with pool, pool session is closed. When create parallel queries with pool connection, it should wait for connection to be free but in the example connection is closed and it is impossible to create a new one:

Error: This session was closed. Use "mysqlx.getSession()" or "mysqlx.getClient()" to create a new one.
    at Object.getError (/home/alexanderm/Projects/test/mysql-bug/node_modules/@mysql/xdevapi/lib/DevAPI/Connection.js:896:35)
    at Socket.<anonymous> (/home/alexanderm/Projects/test/mysql-bug/node_modules/@mysql/xdevapi/lib/DevAPI/Connection.js:664:40)
    at Object.onceWrapper (node:events:514:26)
    at Socket.emit (node:events:394:28)
    at TCP.<anonymous> (node:net:662:12)

How to repeat:
1. git clone https://github.com/naturalfreak/report
2. npm install
3. create database 

 create database test;
 use test;
 CREATE TABLE Persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
);

4.in index.js set username, password, port, ip and schema
{
  user: 'username',
  password: 'password',
  port: 33060,
  host: '127.0.0.1', // default value
  schema: 'test' // default value
}

5. node index.js

Problem is here ( ~/xdevapi/lib/DevAPI/Connection.js ) : in example (now - state.endpoints.unavailable[0].unavailableAt ) is always around 0 and state.retryAfter is 20000

      update () {
            const now = Date.now();
            // Check which unavailable endpoints can be re-tried.
            // If the current element in the list is not "retryable", neither
            // are the remaining. A retryable endpoint is any endpoint that has
            // been unavailable since at least the time interval defined by
            // state.retryAfter.
            while (state.endpoints.unavailable.length && now - state.endpoints.unavailable[0].unavailableAt > state.retryAfter) {
                // If an endpoint can be re-tried we need to move it back to
                // the list of available endpoints.
                state.endpoints.available.push(state.endpoints.unavailable.shift());
            }

            return this;
        }

Hi Aleksandar,

thanks for the report.

It seems the connection pool implementation has some parallelism issues indeed. However, they are in any way related to that piece of code, because that only applies to multi-host connections, which I guess is not the case. Also, the problem is not with the queries themselves, but with the underlying connections (which are acquired from the pool).

What happens is that you are hitting the maximum number of X Protocol connections in the server (mysqlx_max_connections), and because of the parallelism issues, the pool is not properly queuing the request to acquire further connections, when they become available.

In any case, this is a bug, and will eventually be addressed. However, I do not consider it an S2, so I'm updating the severity.

Posted by developer:
 
Fixed as of the upcoming MySQL Connector/Node 8.0.27 release, and here's the proposed changelog entry from the documentation team:

Improved the connection pool mechanism, namely parallelism issues that
stemmed from exceeding mysqlx_max_connections as the pool was not properly
queuing the request to acquire further connections when they become
available.

Thank you for the bug report.