Fix MySQL 8.0 tests, properly close timed out connections

[Nothing4You]

What do these changes do?

Currently tests on timed out connections fail on MySQL 8.0.

This ports PyMySQL/PyMySQL#540.
Interestingly, the PyMySQL issue originally referred to MariaDB, however, I don't see this behavior on currently supported MariaDB versions.
MySQL 8.0 does behave this way though.

In addition to porting the connection related changes, due to us having a pool, we will also need to adjust the pool logic.
Currently the connection pool attempts to not return closed connections in Pool.acquire via Pool._fill_free_pool.
At this time the connection is not yet considered closed, that will only be determined on the next Connection._read_packet.

When MySQL 8.0 closes the connection on us e.g. due to a timeout, the connection will still have the packet containing the error message as a pending read. The TCP connection stays in CLOSE_WAIT state for this.
Having received EOF on the connection it isn't useful to us anymore, so while we may have data pending to be read we don't care about it.
There shouldn't be a case where we expect data available to be read here.

Unfortunately I was not able to find a public API that would provide this information on the connection opened by asyncio.open_connection(), using internal attributes it is possible to read StreamReader._eof.

To avoid accessing this internal attribute I've added a custom StreamReader that will expose whether reader.feed_eof() was called, which the Pool can then rely on.
This is logically exposing the same information that would otherwise be available as StreamReader._eof.

I was not yet able to properly this against unix socket connections.
On minimal local testing using the 2 previously failing test cases the same behavior shows on unix sockets and is also resolved with the same custom StreamReader.
#664 should be implemented before the next release though to ensure we don't have any other unexpected issues on unix sockets.

After doing my own port I also realized that we already have a pending PR to port this (#358) since end of 2018.
While #358 also ports the same PyMySQL patch it does not account for closed connections in the pool, especially as the closed connection state is only discovered the next time the connection is accessed.
That may however be due to the issue being reported (in #340) for MariaDB 10.2 back then and the tests only covering MariaDB up to 10.1.

It may still be worth to migrate and merge the test cases from #358 though.

Are there changes in behavior for the user?

Users accessing timed out MySQL 8.0 connections may previously have received pymysql.err.InternalError instead of an pymysql.err.OperationalError, this is now fixed.

Users retrieving MySQL 8.0 connections from a pool will no longer receive timed out connections.

Connections are now properly closed before raising a pymysql.err.OperationalError due to losing connection to the server.

Connections are now properly closed before raising a pymysql.err.InternalError when packet sequence numbers are out of sync.

Related issue number

May fix #340, may fix #614

Checklist

• I think the code is well written
• Unit tests for the changes exist
• Documentation reflects the changes
• Add a new news fragment into CHANGES.txt

[codecov]

Codecov Report

Merging #660 (3fde2cb) into master (fce0355) will increase coverage by 2.17%.
The diff coverage is 75.00%.

@@            Coverage Diff             @@
##           master     #660      +/-   ##
==========================================
+ Coverage   83.52%   85.70%   +2.17%     
==========================================
  Files          12       12              
  Lines        2052     2085      +33     
  Branches      331      336       +5     
==========================================
+ Hits         1714     1787      +73     
+ Misses        268      228      -40     
  Partials       70       70              

Flag	Coverage Δ
ubuntu-latest_3.7_mariadb-10.2	81.93% <60.00%> (-0.32%)	⬇️
ubuntu-latest_3.7_mariadb-10.3	81.93% <60.00%> (-0.32%)	⬇️
ubuntu-latest_3.7_mariadb-10.4	81.93% <60.00%> (-0.32%)	⬇️
ubuntu-latest_3.7_mariadb-10.5	81.93% <60.00%> (-0.32%)	⬇️
ubuntu-latest_3.7_mariadb-10.6	81.93% <60.00%> (-0.32%)	⬇️
ubuntu-latest_3.7_mariadb-10.7	81.93% <60.00%> (-0.32%)	⬇️
ubuntu-latest_3.7_mysql-5.7	82.81% <60.00%> (-0.33%)	⬇️
ubuntu-latest_3.7_mysql-8.0	84.73% <68.57%> (?)
ubuntu-latest_3.8_mariadb-10.2	82.34% <61.11%> (-0.31%)	⬇️
ubuntu-latest_3.8_mariadb-10.3	82.34% <61.11%> (-0.31%)	⬇️
ubuntu-latest_3.8_mariadb-10.4	82.34% <61.11%> (-0.31%)	⬇️
ubuntu-latest_3.8_mariadb-10.5	82.34% <61.11%> (-0.31%)	⬇️
ubuntu-latest_3.8_mariadb-10.6	82.34% <61.11%> (-0.31%)	⬇️
ubuntu-latest_3.8_mariadb-10.7	82.34% <61.11%> (-0.31%)	⬇️
ubuntu-latest_3.8_mysql-5.7	83.21% <61.11%> (-0.32%)	⬇️
ubuntu-latest_3.8_mysql-8.0	85.04% <69.44%> (?)
ubuntu-latest_3.9_mariadb-10.2	82.34% <61.11%> (-0.31%)	⬇️
ubuntu-latest_3.9_mariadb-10.3	82.34% <61.11%> (-0.31%)	⬇️
ubuntu-latest_3.9_mariadb-10.4	82.34% <61.11%> (-0.31%)	⬇️
ubuntu-latest_3.9_mariadb-10.5	82.34% <61.11%> (-0.31%)	⬇️
ubuntu-latest_3.9_mariadb-10.6	82.34% <61.11%> (-0.31%)	⬇️
ubuntu-latest_3.9_mariadb-10.7	82.34% <61.11%> (-0.31%)	⬇️
ubuntu-latest_3.9_mysql-5.7	83.21% <61.11%> (-0.32%)	⬇️
ubuntu-latest_3.9_mysql-8.0	85.04% <69.44%> (?)

Flags with carried forward coverage won't be shown. Click here to find out more.

Impacted Files	Coverage Δ
aiomysql/connection.py	82.75% <72.72%> (+5.56%)	⬆️
aiomysql/pool.py	96.98% <100.00%> (+0.05%)	⬆️

---

Continue to review full report at Codecov.

Legend - Click here to learn more
Δ = absolute <relative> (impact), ø = not affected, ? = missing data
Powered by Codecov. Last update fce0355...3fde2cb. Read the comment docs.

[Nothing4You]

for issue documentation sake, these are the values on a timed out mysql 8.0 connection returned from the pool without this:
tcp socket:

conn -> <aiomysql.connection.Connection object at 0x1031dc8b0>
conn._reader -> <StreamReader 149 bytes eof transport=<_SelectorSocketTransport fd=7 read=idle write=<idle, bufsize=0>>>
conn._reader.at_eof() -> False
conn._reader._eof -> True
conn._reader._transport -> <_SelectorSocketTransport fd=7 read=idle write=<idle, bufsize=0>>
conn._reader._transport.is_closing() -> False
conn._reader._transport._eof -> False
conn._reader._transport._protocol -> <asyncio.streams.StreamReaderProtocol object at 0x1031dc910>
conn._reader._transport._protocol_connected -> True
conn._reader._transport._protocol._stream_reader -> <StreamReader 149 bytes eof transport=<_SelectorSocketTransport fd=7 read=idle write=<idle, bufsize=0>>>
conn._writer -> <StreamWriter transport=<_SelectorSocketTransport fd=7 read=idle write=<idle, bufsize=0>> reader=<StreamReader 149 bytes eof transport=<_SelectorSocketTransport fd=7 read=idle write=<idle, bufsize=0>>>>
conn._writer.is_closing() -> False

unix socket:

conn -> <aiomysql.connection.Connection object at 0x1057543a0>
conn._reader -> <StreamReader 149 bytes eof transport=<_SelectorSocketTransport fd=7 read=idle write=<idle, bufsize=0>>>
conn._reader.at_eof() -> False
conn._reader._eof -> True
conn._reader._transport -> <_SelectorSocketTransport fd=7 read=idle write=<idle, bufsize=0>>
conn._reader._transport.is_closing() -> False
conn._reader._transport._eof -> False
conn._reader._transport._protocol -> <asyncio.streams.StreamReaderProtocol object at 0x105754460>
conn._reader._transport._protocol_connected -> True
conn._reader._transport._protocol._stream_reader -> <StreamReader 149 bytes eof transport=<_SelectorSocketTransport fd=7 read=idle write=<idle, bufsize=0>>>
conn._writer -> <StreamWriter transport=<_SelectorSocketTransport fd=7 read=idle write=<idle, bufsize=0>> reader=<StreamReader 149 bytes eof transport=<_SelectorSocketTransport fd=7 read=idle write=<idle, bufsize=0>>>>
conn._writer.is_closing() -> False

[Nothing4You]

And the actual error packet returned on MySQL 8.0:
(4031, 'The client was disconnected by the server because of inactivity. See wait_timeout and interactive_timeout for configuring this behavior.')

[prryplatypus]

Hi there! Just chipping in to express my interest in this PR being merged/released :)
ty in advance for the hard work.