ormar & uvicorn workers

[es3n1n]

So, i've got ormar running on prod(mysql) with uvicorn workers(15) and everything works fine, but sometime i'm getting
pymysql.err.InternalError: Packet sequence number wrong - got 35 expected 1 when i query database, after a few seconds in google i found out that it's because pymysql works fine only in singlethread and if you want to create a multithread app you need to create a multiple amount of instances. Well i'm creating database & other stuff in my app.state so it shouldn't be an issue.

Is there maybe any examples of this because i'm stuck for along time?

refs:
PyMySQL/PyMySQL#422
https://www.uvicorn.org/settings/#production

[collerek]

pymsql is used only for creation of db and migrations. Normally ormar uses aiomysql cause it uses databases to run queries.

That means that you probably call create database multiple times (can be run in multiple threads or processes depending on workers config).

In production you should be using alembic to run migrations, and those should be run before the app even starts, in a separate step in deployment.

[es3n1n]

In production you should be using alembic to run migrations, and those should be run before the app even starts, in a separate step in deployment.

That's how it normally works tho. Uncommented debugcheck for metadata.create_all(engine), looks like it was the problem

[collerek]

Yes i meant create-all (or separate calls to create by separate tables) from metadata when I wrote creating the database. Sorry for confusion.

Glad that it helped. Let me know if something is still wrong and I will see if I can help.

[es3n1n]

Still getting this error, create_all calls were removed from my project. Any ideas why is this still happening?

[es3n1n]

    await item.save()
          │    └ <function Model.save at 0x7f8c7005eef0>
          └ ***({'***...

  File "/home/ubuntu/.local/lib/python3.7/site-packages/ormar/models/model.py", line 337, in save
    pk = await self.Meta.database.execute(expr)
               │    │    │        │       └ <sqlalchemy.sql.dml.Insert object at 0x7f8c6c324a10>
               │    │    │        └ <function Database.execute at 0x7f8c70c0a200>
               │    │    └ <databases.core.Database object at 0x7f8c70b44a90>
               │    └ <class 'backend.database.models.user_report.***.Meta'>
               └ ***({'***...
  File "/home/ubuntu/.local/lib/python3.7/site-packages/databases/core.py", line 161, in execute
    return await connection.execute(query, values)
                 │          │       │      └ None
                 │          │       └ <sqlalchemy.sql.dml.Insert object at 0x7f8c6c324a10>
                 │          └ <function Connection.execute at 0x7f8c70c0a950>
                 └ <databases.core.Connection object at 0x7f8c6c324a50>
  File "/home/ubuntu/.local/lib/python3.7/site-packages/databases/core.py", line 263, in execute
    return await self._connection.execute(built_query)
                 │    │           │       └ <sqlalchemy.sql.dml.Insert object at 0x7f8c6c324a10>
                 │    │           └ <function MySQLConnection.execute at 0x7f8c702b7830>
                 │    └ <databases.backends.mysql.MySQLConnection object at 0x7f8c6c3249d0>
                 └ <databases.core.Connection object at 0x7f8c6c324a50>
  File "/home/ubuntu/.local/lib/python3.7/site-packages/databases/backends/mysql.py", line 137, in execute
    await cursor.execute(query, args)
          │      │       │      └ {***...
          │      │       └ 'INSERT INTO *** (***) VALUES (\'***...
          │      └ <function Cursor.execute at 0x7f8c702a67a0>
          └ <aiomysql.cursors.Cursor object at 0x7f8c6c324b10>
  File "/home/ubuntu/.local/lib/python3.7/site-packages/aiomysql/cursors.py", line 239, in execute
    await self._query(query)
          │    │      └ 'INSERT INTO *** (***) VALUES (\'***...
          │    └ <function Cursor._query at 0x7f8c702a6c20>
          └ <aiomysql.cursors.Cursor object at 0x7f8c6c324b10>
  File "/home/ubuntu/.local/lib/python3.7/site-packages/aiomysql/cursors.py", line 457, in _query
    await conn.query(q)
          │    │     └ 'INSERT INTO *** (***) VALUES (\'***...
          │    └ <function Connection.query at 0x7f8c702aeef0>
          └ <aiomysql.connection.Connection object at 0x7f8c685a3790>
  File "/home/ubuntu/.local/lib/python3.7/site-packages/aiomysql/connection.py", line 428, in query
    await self._read_query_result(unbuffered=unbuffered)
          │    │                             └ False
          │    └ <function Connection._read_query_result at 0x7f8c702ad680>
          └ <aiomysql.connection.Connection object at 0x7f8c685a3790>
  File "/home/ubuntu/.local/lib/python3.7/site-packages/aiomysql/connection.py", line 622, in _read_query_result
    await result.read()
          │      └ <function MySQLResult.read at 0x7f8c702b2290>
          └ <aiomysql.connection.MySQLResult object at 0x7f8c68e8c490>
  File "/home/ubuntu/.local/lib/python3.7/site-packages/aiomysql/connection.py", line 1105, in read
    first_packet = await self.connection._read_packet()
                         │    └ None
                         └ <aiomysql.connection.MySQLResult object at 0x7f8c68e8c490>
  File "/home/ubuntu/.local/lib/python3.7/site-packages/aiomysql/connection.py", line 576, in _read_packet
    (packet_number, self._next_seq_id))
     │              │    └ 1
     │              └ <aiomysql.connection.Connection object at 0x7f8c685a3790>
     └ 0

pymysql.err.InternalError: Packet sequence number wrong - got 0 expected 1``` 

[es3n1n]

@collerek any updates on this? It started to happen more and more frequently and it started to annoy me 🤔

[collerek]

It seems it's coming from aiomysql and would happen regardless if you use ormar or not aio-libs/aiomysql#614

But at the same time I'm using mysql in production for a long time already and it works fine. How do you deploy to production? Can you downgrade the system version and/or change system and/or downgrade python version?

Since aiomysql is not really longer supported there are some new drivers appearing. One of them is asyncmy. There is a PR for databases but it was not merged yet. Can you try to install databases from this branch and change your url to use this driver? encode/databases#382