Connection created from pool is never closed to success after `with` block

[stolenzc]

Describe the bug

I use this code running, you can see conn._writer is working after the with block, It means pool.release is not running as expected.

import asyncio

import aiomysql

async def mysql_pool(loop):
    config = {
        "user": "test_user",
        "password": "test_pwd",
        "db": "test_db",
        "port": 3306,
        "host": "localhost",
        "minsize": 1,
        "maxsize": 5,
    }

    mysql_pool = await aiomysql.create_pool(**config, loop=loop)
    return mysql_pool

async def main(loop):
    pool = await mysql_pool(loop)
    async with pool.acquire() as conn:
        async with conn.cursor() as cur:
            await cur.execute("SELECT 1")
            (r,) = await cur.fetchone()
            print(r)
    print(conn._writer)
    conn.close()
    print(conn._writer)

if __name__ == "__main__":
    loop = asyncio.get_event_loop()
    loop.run_until_complete(main(loop))

The console output is as follows

1
<StreamWriter transport=<_SelectorSocketTransport fd=8 read=polling write=<idle, bufsize=0>> reader=<StreamReader transport=<_SelectorSocketTransport fd=8 read=polling write=<idle, bufsize=0>>>>
None

To Reproduce

Reproduce like before code

Expected behavior

After with block, conn._writer should be None

Logs/tracebacks

There not has error raised

Python Version

$ python --version
Python 3.9.13

aiomysql Version

$ python -m pip show aiomysql
Name: aiomysql
Version: 0.1.1
Summary: MySQL driver for asyncio.
Home-page: https://github.com/aio-libs/aiomysql
Author: Nikolay Novik
Author-email: nickolainovik@gmail.com
License: MIT
Location: /Users/stolen/Documents/code/egg_info/venv/lib/python3.9/site-packages
Requires: PyMySQL
Required-by:

PyMySQL Version

$ python -m pip show PyMySQL
Name: PyMySQL
Version: 1.0.2
Summary: Pure Python MySQL Driver
Home-page: https://github.com/PyMySQL/PyMySQL/
Author: yutaka.matsubara
Author-email: yutaka.matsubara@gmail.com
License: "MIT"
Location: /Users/stolen/Documents/code/egg_info/venv/lib/python3.9/site-packages
Requires: 
Required-by: aiomysql

SQLAlchemy Version

$ python -m pip show sqlalchemy

OS

Mac

Database type and version

SELECT VERSION();
5.6.16-log

Additional context

No response

Code of Conduct

• [X] I agree to follow the aio-libs Code of Conduct

[asudoma]

I think you must close pool explicitly:

pool.close()
await pool.wait_closed()

or use context manager:

@asynccontextmanager
async def mysql_pool(loop):
    config = {
        "user": "user",
        "password": "password",
        "db": "database",
        "port": 3306,
        "host": "localhost",
        "minsize": 1,
        "maxsize": 5,
    }

    async with aiomysql.create_pool(**config, loop=loop) as pool:
        yield pool

async def main(loop):
    async with mysql_pool(loop) as pool:
        ...

[Nothing4You]

Indeed, returning a connection to the pool does not close them. The pool reuses connections as long as they're usable if pool_recycle is not passed when creating the pool. By specifying a timeout with pool_recycle, the pool will eventually close old connections (lazily, next time a connection is acquired).