Lost connection to MySQL server during query

[IdeoG]

Hello, I'm using aiomysql library for a while in work project. I got asynchronious server with mysql database with follwing communication: aiohttp <-> aiomysql + sa.

Here is my sample code.

class UserTableModel:
    def __init__(self):
        self.metadata = sa.MetaData()
        self.table = sa.Table('users', self.metadata,
                              sa.Column('id', sa.Integer, primary_key=True),
                              sa.Column('login', sa.String(20)),
                              sa.Column('password', sa.String(20)),
                              )

        self.engine = None
        self.conn = None
        self.loop = None

        self.users = []

    async def insert_user(self, user):
        result = await self.conn.execute(self.table.insert().values(
            login=user['login'],
            password=user['password']
        ))
        uid = result.lastrowid
        await self.conn.execute("commit")
        return uid

    async def get_user_by_id(self, id):
        result = await self.conn.execute(self.table.select().where(self.table.c.id == id))
        user = await result.fetchone()
        return user

    async def start(self):
        self.engine = await create_engine(user=user', db=db,
                                          host='127.0.0.1', password=root,
                                          pool_recycle=60)
        self.conn = await self.engine.acquire()
        logging.debug("UserTableModel: start : MySQL Engine started.")

    async def close(self):
        await self.conn.close()
        self.engine.close()
        await self.engine.wait_closed()
        logging.debug("UserTableModel: close : MySQL Engine closed.")

user_table = UserTableModel()

This code works pretty good first 6 hours, but after some no-load operation, i get following error for every query to MySQL:

File "/home/user/.local/lib/python3.6/site-packages/aiomysql/sa/connection.py", line 121, in _execute
    await cursor.execute(str(compiled), post_processed_params[0])
  File "/home/user/.local/lib/python3.6/site-packages/aiomysql/cursors.py", line 235, in execute
    await self._query(query)
  File "/home/user/.local/lib/python3.6/site-packages/aiomysql/cursors.py", line 452, in _query
    await conn.query(q)
  File "/home/user/.local/lib/python3.6/site-packages/aiomysql/connection.py", line 396, in query
    await self._read_query_result(unbuffered=unbuffered)
  File "/home/user/.local/lib/python3.6/site-packages/aiomysql/connection.py", line 582, in _read_query_result
    await result.read()
  File "/home/user/.local/lib/python3.6/site-packages/aiomysql/connection.py", line 898, in read
    first_packet = await self.connection._read_packet()
  File "/home/user/.local/lib/python3.6/site-packages/aiomysql/connection.py", line 522, in _read_packet
    packet_header = await self._read_bytes(4)
  File "/home/user/.local/lib/python3.6/site-packages/aiomysql/connection.py", line 565, in _read_bytes
    raise OperationalError(2013, msg) from e
pymysql.err.OperationalError: (2013, 'Lost connection to MySQL server during query ([Errno 32] Broken pipe)')

I tried to increase pool_recycle param, but it doesn't worked for me.

How to solve the issue?

Best regards, Alex.

[IdeoG]

Ok, i have fixed the issue recently. It was my bad, because i used one connection for all queries.

[cnicodeme]

Sorry to comment on this, this late, but @IdeoG , what do you mean by used one connection for all queries? I'm having the same issue and I'd like to have some input on how you fixed this issue! Thanks :)

[IdeoG]

Hello, @cnicodeme As you can see in my previous code i created self.conn in start and used this singleton for whole queries i get. For example, for async insert_user or get_user_by_id. So, you schould create connection from engine each time then you execute an operation. In code it's like as:

async def get_user_by_login(self, login):
        async with self.engine.acquire() as conn:
            result = await conn.execute(self.table.select().where(self.table.c.login == login))
            user = await result.fetchone()
        return user

async def get_user_by_id(self, id):
        async with self.engine.acquire() as conn:
            result = await conn.execute(self.table.select().where(self.table.c.id == id))
            user = await result.fetchone()
        return user

Best regards, Alex

[cnicodeme]

Thank you your response @IdeoG . That's finally what I ended up doing while waiting for your answer so I think it's the right move :)