Aborted connection

[lvalladares]

Hello!

Im using aiomysql with SQLAlchemy and i get this warning from mysql:

Aborted connection to db: 'zzzzzzz' user: 'xxxx' host: 'XXXXXXXX' (Got an error reading communication packets)

I've readed a little bit about this error and this seems to happens when you dont close connection properly, im closing my conns like this:

    await connection.close()
    engine.close()
    await engine.wait_closed()

where engine is the var storing the engine and connection the result of engine.acquire(). Maybe im clossing the connection wrong?

Thanks!

[jettify]

Hi, usually you need just return connection to the pool (or engine in SQLA case), see example:

async def go(loop):
    engine = await create_engine(user='root', db='test_pymysql',
                                 host='127.0.0.1', password='', loop=loop)
    await create_table(engine)
    async with engine.acquire() as conn:
        await conn.execute(tbl.insert().values(val='abc'))
        await conn.execute(tbl.insert().values(val='xyz'))

        async for row in conn.execute(tbl.select()):
            print(row.id, row.val)

    engine.close()
    await engine.wait_closed()

pool should care about proper connections closing, during application shutdown

[lvalladares]

Hello! I removed the first line of my code (await connection.close()) and i still getting the same error, i checked on my code and is like yours. Any idea on what can be happening?

[jettify]

This could be result of connection timeout, some of your connections rarely used to execute queries, as result mysql kills it.

see more information here: https://www.percona.com/blog/2016/05/16/mysql-got-an-error-reading-communication-packet-errors/

[jettify]

you may also need to tweak max_allowed_packet: https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_max_allowed_packet

[lvalladares]

I think its not related with mysql settings, let me explain my situation:

Im working with microservices architecture, and i've about 10 micro services now, all using mysql, im now developing one with aiomysql (the other where developed under normal PyMySQL and sqlalchemy) and everytime i make a request to my new microservice (the one with aiomysql) i get the aborted connection message at the end of th requests.

The connections are created on each request and are dropped at the end (i've middlewares at the start of the request and at the end of th response for this) so my guess is the close signal of the connection is not being sended correctly, i've already tested the max allowed packet and i've duplicated the original value with the same results.

Its importat to say this happens with every connection created with aiomysql + sqlalchemy, if my request create 3 connection i get 3 error messages.

[luca-arch]

This is happening to me as well. I've wrapped everything into a small class as follows:

class DatabaseInterface:
    async def connect(self):
        db_connection = await aiomysql.connect(host=DB_HOST, port=DB_PORT,
                                              user=DB_USER, password=DB_PASS,
                                              db=DB_NAME, charset='utf8mb4')
        return db_connection

    async def get_cursor(self):
        cnx = await self.connect()
        cur = await cnx.cursor(aiomysql.DictCursor)

        return cnx, cur

    async def select(self, query, args=[]):
        cnx, cur = await self.get_cursor()

        await cur.execute(query, args)

        resultset = await cur.fetchall()

        await cur.close()

        cnx.close()

        return resultset

But any time I use, let's say rows = DatabaseInterface().select(), my logs get filled with

2018-04-14T05:35:09.492436Z 6791 [Note] Aborted connection 6791 to db: 'databaze' user: 'databaze' host: '172.25.0.4' (Got an error reading communication packets)

The database server is Percona Xtradb 5.7

Solved

Replace cnx.close() with await cnx.ensure_closed()

[andr-04]

But what about the pool of connections? I use pool_recycle=60 to ensure the connection from the pool is live. According to aiomysql/pool.py#L158 I often get the connection terminated unexpectedly and many warnings inside mysql log. On the other hand if to use await conn.ensure_closed() instead, on dead connections it can hangs for a long time.

What is the right behavior?