How does MySQL know when connection is "over"?

Question

I have just closed a MySQL shell by clicking x on my command prompt and I am now curious what happens to database connections when you close the window.

I know that the MySQL server runs on port 3306. If I understand correctly, the server waits for connection requests and passes them to a new thread/available thread for managing the connection.

How does the server know when the connection has been terminated, so that the thread can be released or put back into a pool of available threads?

Scenarios such as:

Blue Screen during a connection with a MySQL server on a remote connection.
Program terminated before it could call a disconnect request.

at some point the OS will clean up the tcp socket, or mysql will notice when it tries to send something and gets a failure back from the network stack. — Marc B
– Marc B, Commented Jun 27, 2016 at 16:12
@MarcB I can imagine the worker thread doing some kind of polling over every interval to check if the connection is active, this does seem a bit expensive if there are many idle connections. Although I can imagine that having many active connections is a sign of bigger issues in how the database is accessed(since few can just connect to your database and just keep it open forever, most requests are through specific indirect requests that just do something and disconnect). — Dmytro
– Dmytro, Commented Jun 27, 2016 at 16:15

Drew · Accepted Answer · 2016-06-27 17:35:15Z

1

From the mysql client:

show processlist;
+-----+------+-----------------+---------------+---------+-------+-------+------------------+
| Id  | User | Host            | db            | Command | Time  | State | Info             |
+-----+------+-----------------+---------------+---------+-------+-------+------------------+
|   2 | root | localhost:6509  | so_gibberish2 | Sleep   |   105 |       | NULL             |
|   3 | root | localhost:6510  | so_gibberish2 | Sleep   |   105 |       | NULL             |
| 820 | root | localhost:40286 | stackoverflow | Sleep   | 13457 |       | NULL             |
| 821 | root | localhost:40287 | stackoverflow | Sleep   |   966 |       | NULL             |
| 827 | root | localhost:42254 | so_gibberish2 | Query   |     0 | init  | show processlist |
| 831 | root | localhost:44036 | stackoverflow | Sleep   |   230 |       | NULL             |
+-----+------+-----------------+---------------+---------+-------+-------+------------------+

select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|             827 |
+-----------------+

Now hit [x] as you say to client the mysql client that ran the above.

Below is from Mysql Workbench:

show processlist;
+-----+------+-----------------+---------------+---------+-------+-------+------------------+
| Id  | User | Host            | db            | Command | Time  | State | Info             |
+-----+------+-----------------+---------------+---------+-------+-------+------------------+
|   2 | root | localhost:6509  | so_gibberish2 | Sleep   |   105 |       | NULL             |
|   3 | root | localhost:6510  | so_gibberish2 | Sleep   |   105 |       | NULL             |
| 820 | root | localhost:40286 | stackoverflow | Sleep   | 13457 |       | NULL             |
| 821 | root | localhost:40287 | stackoverflow | Sleep   |   966 |       | NULL             |
+-----+------+-----------------+---------------+---------+-------+-------+------------------+
select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|               3 |
+-----------------+

So the closing of a program naturally closes any open sockets it has.

Honestly, the latter output looks more like:

But I always use the mysql client for output formatting for tables which would throw off the results. So there you have it as an image.

edited Jun 27, 2016 at 17:35

answered Jun 27, 2016 at 17:28

Drew

25k10 gold badges47 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Dmytro Over a year ago

Ok do the socket does close; but what happens to the connection thread that handles client requests?

Drew Over a year ago

ok so in the case of a TCP (versus named pipes which I am still investigating). But for TCP you have a connected socket. When that socket disconnects (either side can close it), then the other side gets a socket event alerting of the closure. So at that point, it is done. The server in either case discards its thread. It is no different than any plain Jane writing of a socket communication where you write the server and client piece.

Drew Over a year ago

In the case of the "server has gone away", that is a mere connection timeout issue. The client (whatever that is, mysql client command line, Toad, Workbench), it too knows that status. It merely re-authenticates and proceeds with commands. But both sides are aware of the state, of no true connection, and the threads are blown away.

Dmytro Over a year ago

maybe the server thread blocks on the socket input, and as soon as it disconnects, it unblocks, realized there is no connection and terminates/notifies the server that created it(if necessary).

Drew Over a year ago

As far as Named Pipes goes, most of that is for security settings, locking down (disallowing) non-localhost usage of the server upon initial server configuration. Meaning, the only thing that can use the server initially is localhost. It may take some doing to show server usage in a named pipe setup. Part of what discourages that investigation is that it won't be prevalent in the field (where TCP is used).

|

Collectives™ on Stack Overflow

How does MySQL know when connection is "over"?

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related