search

aio-libs / aiomysql

aiomysql is a library for accessing a MySQL database from the asyncio
https://aiomysql.rtfd.io
MIT License
1.75k stars 255 forks source link

packet sequence number wrong #614

Closed swordjjjkkk closed 2 years ago

swordjjjkkk commented 3 years ago

i use aiomysql pool. here is my usage. image

when i need query sql, pool.acquire first , query done, i close cur and release conn. there is 2~4 coroutine use like this. is there any chance to have this problem --- packet sequence number wrong?

MarkReedZ commented 3 years ago

I'm also seeing this after the connection being idle for some time. 

<class 'pymysql.err.InternalError'>
InternalError('Packet sequence number wrong - got 0 expected 1')
Unhandled exception:
<class 'pymysql.err.InternalError'>
InternalError('Packet sequence number wrong - got 0 expected 1')
Unhandled exception:
<class 'pymysql.err.InternalError'>
InternalError('Packet sequence number wrong - got 0 expected 1')
Unhandled exception:
<class 'pymysql.err.InternalError'>
InternalError('Packet sequence number wrong - got 0 expected 1')
MarkReedZ commented 3 years ago

Looks like this problem exists in pymysql

https://github.com/ansible-collections/community.mysql/issues/73

Nagico commented 3 years ago

I'm also seeing this after the connection being idle for some time.

<class 'pymysql.err.InternalError'>
InternalError('Packet sequence number wrong - got 0 expected 1')
Unhandled exception:
<class 'pymysql.err.InternalError'>
InternalError('Packet sequence number wrong - got 0 expected 1')
Unhandled exception:
<class 'pymysql.err.InternalError'>
InternalError('Packet sequence number wrong - got 0 expected 1')
Unhandled exception:
<class 'pymysql.err.InternalError'>
InternalError('Packet sequence number wrong - got 0 expected 1')

I'm also having the same problem. Here's my Python traceback.

File "/root/BiliLiveData/Database/DAO/MsgDO.py", line 143, in _execute  # the code I wrote for executing a sql statement
    await cursor.execute(sql)
  File "/usr/local/lib/python3.8/dist-packages/aiomysql/cursors.py", line 239, in execute
    await self._query(query)
  File "/usr/local/lib/python3.8/dist-packages/aiomysql/cursors.py", line 457, in _query
    await conn.query(q)
  File "/usr/local/lib/python3.8/dist-packages/aiomysql/connection.py", line 428, in query
    await self._read_query_result(unbuffered=unbuffered)
  File "/usr/local/lib/python3.8/dist-packages/aiomysql/connection.py", line 622, in _read_query_result
    await result.read()
  File "/usr/local/lib/python3.8/dist-packages/aiomysql/connection.py", line 1105, in read
    first_packet = await self.connection._read_packet()
  File "/usr/local/lib/python3.8/dist-packages/aiomysql/connection.py", line 574, in _read_packet
    raise InternalError(
pymysql.err.InternalError: Packet sequence number wrong - got 0 expected 1
Exception ignored in: <function Connection.__del__ at 0x7f2e2bc9c1f0>
Traceback (most recent call last):
  File "/usr/local/lib/python3.8/dist-packages/aiomysql/connection.py", line 1072, in __del__
  File "/usr/local/lib/python3.8/dist-packages/aiomysql/connection.py", line 298, in close
  File "/usr/lib/python3.8/asyncio/selector_events.py", line 692, in close
  File "/usr/lib/python3.8/asyncio/base_events.py", line 719, in call_soon
  File "/usr/lib/python3.8/asyncio/base_events.py", line 508, in _check_closed
RuntimeError: Event loop is closed
Exception ignored in: <function Connection.__del__ at 0x7f2e2bc9c1f0>
Traceback (most recent call last):
  File "/usr/local/lib/python3.8/dist-packages/aiomysql/connection.py", line 1072, in __del__
  File "/usr/local/lib/python3.8/dist-packages/aiomysql/connection.py", line 298, in close
  File "/usr/lib/python3.8/asyncio/selector_events.py", line 692, in close
  File "/usr/lib/python3.8/asyncio/base_events.py", line 719, in call_soon
  File "/usr/lib/python3.8/asyncio/base_events.py", line 508, in _check_closed
RuntimeError: Event loop is closed
Exception ignored in: <function Connection.__del__ at 0x7f2e2bc9c1f0>
Traceback (most recent call last):
  File "/usr/local/lib/python3.8/dist-packages/aiomysql/connection.py", line 1072, in __del__
  File "/usr/local/lib/python3.8/dist-packages/aiomysql/connection.py", line 298, in close
  File "/usr/lib/python3.8/asyncio/selector_events.py", line 692, in close
  File "/usr/lib/python3.8/asyncio/base_events.py", line 719, in call_soon
  File "/usr/lib/python3.8/asyncio/base_events.py", line 508, in _check_closed
RuntimeError: Event loop is closed
Exception ignored in: <function Connection.__del__ at 0x7f2e2bc9c1f0>
Traceback (most recent call last):
  File "/usr/local/lib/python3.8/dist-packages/aiomysql/connection.py", line 1072, in __del__
  File "/usr/local/lib/python3.8/dist-packages/aiomysql/connection.py", line 298, in close
  File "/usr/lib/python3.8/asyncio/selector_events.py", line 692, in close
  File "/usr/lib/python3.8/asyncio/base_events.py", line 719, in call_soon
  File "/usr/lib/python3.8/asyncio/base_events.py", line 508, in _check_closed
RuntimeError: Event loop is closed
Exception ignored in: <function Connection.__del__ at 0x7f2e2bc9c1f0>
Traceback (most recent call last):
  File "/usr/local/lib/python3.8/dist-packages/aiomysql/connection.py", line 1072, in __del__
  File "/usr/local/lib/python3.8/dist-packages/aiomysql/connection.py", line 298, in close
  File "/usr/lib/python3.8/asyncio/selector_events.py", line 692, in close
  File "/usr/lib/python3.8/asyncio/base_events.py", line 719, in call_soon
  File "/usr/lib/python3.8/asyncio/base_events.py", line 508, in _check_closed
RuntimeError: Event loop is closed
panla commented 3 years ago

aiomysql/pool.py

def create_pool(minsize=1, maxsize=10, echo=False, pool_recycle=-1, loop=None, **kwargs):

use param pool_recycle will deal some questions;

Nagico commented 3 years ago

Maybe it's caused by multithread or coroutine. PyMySQL issue 422

Because only INSERT and UPDATE command I need to use in mysql, so I simply warp the original execute function in aiomysql.

Then remove the connection pool, using a single persistent connection instead and add a lock when a function want to execute a sql statement.

like this ↓

async def execute(self, sql: str):
    async with self.lock:
        async with self.connection.cursor() as cur:
            await cur.execute(sql)
            last_id = cur.lastrowid
            await self.connection.commit()
     return last_id

After 2 days testing, it works correctly. image

But I still want to use a connection pool, so I create and manage a pool on my own using asyncio.Queue.

https://github.com/NagisaCo/BiliLiveData/blob/1923e027df9b8b9cd467b512dcb9d4b01018bfd1/Database/MySQL.py#L11-L60

Nothing4You commented 2 years ago

Hi,

which database and database version are you seeing this on?