MySQL Bugs: #108039: Connections are not released synchronously

Bug #108039	Connections are not released synchronously
Submitted:	31 Jul 2022 13:03	Modified:	17 Aug 2022 13:35
Reporter:	Michal Vorisek	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.7, 8.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	atomic, Connection, max connection, socket, user

Description:
In atk4/data php data framework we run unit tests with limited number of max. connections - https://github.com/atk4/data/blob/2c501c78f7e839ab2d55176d59ab258000ee1a76/.github/workflo...

When we limited it strictly to 1, we have observed random CI failures with MySQL and MariaDB databases.

Initially I thought this is issue in PHP mysqlnd and I opened https://github.com/php/php-src/issues/9151 where I described the issue exhaustively. Please see the php-src ticket for code to reproduce. I was able to reproduce it also using in lua.

Currently, it seems the MySQL release the client connection too early and does not synchronously wait until the connection is fully released from the database and decremented from the active connections counters.

This behaviour can be seen with MySQL and MariaDB only. I tested also Microsoft SQL Server and PostgreSQL and these databases release the connection socket synchronously with the counters.

How to repeat:
see https://github.com/php/php-src/issues/9151 for code and steps

Suggested fix:
Established client connection must not be released by the server until it is fully released internally and the server counters are decremented.

Hi Mr. Vorisek,

Thank you for your bug report.

However, we do not have a proper test case from you that would exhibit the wrong behaviour in server, because you have chosen a category of "Server" not "Client" or "PHP".

Hence, you can do a simple test case with our libmysqlclient C API, or you could send us a full test case by using our mysql CLI. A test case must be reproducible on our side and should prove the reported behaviour, that we can easily visualise ......

Last, but not least, please use our latest binary, 8.0.30.

Thanks in advance ......

Hi,

the description of this issue should be sufficient. The issue is not in the client, but in the server which does release the client connections too early and after a burst of tens of thousands (strictly one by one from single thread) reconnects, the server can be very consistently put into a state, when it shows more than one connection.

I have demonstrated this server issue with demo in PHP and cross verified it with demo in lua. At this moment I have no access to C compiler to provide a demo with C/libmysqlclient, but rewriting one of my very simple demo - which is basically a loop consisting of: connect, check if connection is working, disconnect, should be easy to reproduce on your side.

HI Mr. Vorisek,

Sorry, but we need a reproducible test case, either with MySQL CLI or with  C or C++ program. 

Not enough information was provided for us to be able to handle this bug. Please re-read the instructions at http://bugs.mysql.com/how-to-report.php 

If you can provide more information, feel free to add it to this bug and change the status back to 'Open'.  

Thank you for your interest in MySQL.

repro.c

Attachment: repro.c (application/octet-stream, text), 1.13 KiB.

C code to reproduce uploaded.

Command to build and run:
gcc repro.c -o repro -lmysqlclient && ./repro

Please confirm it can be reproduced on your side.

Example output:
iter: 0k
iter: 1k
iter: 2k
iter: 3k
Connect error: User 'atk4_test_user' has exceeded the 'max_user_connections' resource (current value: 2)

Hi Mr. Vorisek,

Thank you for your test case.

We have run your test and established that you are correct, but also that this has nothing to do with our code.

Simply, the operating system takes its time to close the port, especially the TCP ports. Our code waits for the kernel call (for closing the port) to be completed and then it does its own housecleaning. Due to the latency of the TCP port management by the kernel, our ports can not be fast enough for the code of your type. 

Luckily, in practice, application codes have their own housecleaning work to do, so this problem does not pop up in everyday practice, at all. Unless somebody writes a test code, like you did, where no work is done except for establishing and closing connections.

Not a bug.

This explanation might be true, but why the counters are decresed after the connection socket is released from the system? When a client sends a "quit" message, I would expect the server to:
a) process the connection termination incl. housekeeping (release buffers, ...)
b) decrese the active connections counter
c) reply/confirm the termination to the client
d) close server socket

This is how PostgreSQL and MSSQL servers behave. Can MySQL server be fixed to behave the same too?

Hi Mr. Vorisek,

What you are expecting to happen does not relate with reality.

Namely, closing a socket could return an error. That happens quite frequently, during creating, closing and using a socket.

Not a bug.