Packet sequence number wrong - got 3 expected 1

[jupiterbjy]

Describe the bug

This library fails to read database, and sadly after 6 hours of testing I can only say "I don't what's wrong".

One possibility I can think of is trio-mysql not supporting the mysql 8.x, while pymysql does.

Should I just downgrade or switch to sqlalchemy-aio for now?

To Reproduce

Schema:

CREATE DATABASE test_mysql CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE test_mysql

CREATE TABLE `test` (
    `id` int NOT NULL AUTO_INCREMENT,
    `val` varchar(1023) COLLATE utf8mb4_0900_ai_ci NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
AUTO_INCREMENT=1;

INSERT INTO test(val) VALUES("test post that shouldn't get deleted");

Should looks like this:

mysql> select * from test;
+----+--------------------------------------+
| id | val                                  |
+----+--------------------------------------+
|  1 | test post that shouldn't get deleted |
+----+--------------------------------------+

Code:

from getpass import getpass
import trio
import trio_mysql


user = input("DB User> ")
pwd = getpass("DB Pwd > ")
db_name = input("DB Name> ")


def get_db():
    conn = trio_mysql.connect(
        host="localhost",
        user=user,
        password=pwd,
        db=db_name,
        charset="utf8mb4",
        cursorclass=trio_mysql.cursors.DictCursor
    )
    print("Created DB connection")
    return conn


async def fetch_db():
    async with get_db() as conn:
        async with conn.cursor() as cur:
            await cur.execute(f"SELECT * FROM test")
            data = await cur.fetchall()
    print(data)


trio.run(fetch_db)

Output

DB User> root
DB Pwd > 
DB Name> test_mysql
Created DB connection
E:\github\StreamNotifier-Web\venv\lib\site-packages\trio_mysql\connections.py:670: RuntimeWarning: coroutine 'caching_sha2_password_auth' was never awaited
  await self._request_authentication()
RuntimeWarning: Enable tracemalloc to get the object allocation traceback
Traceback (most recent call last):
  File "C:\Users\jupiterbjy\AppData\Roaming\JetBrains\PyCharm2021.3\scratches\scratch.py", line 32, in <module>
    trio.run(fetch_db)
  File "E:\github\StreamNotifier-Web\venv\lib\site-packages\trio\_core\_run.py", line 1932, in run
    raise runner.main_task_outcome.error
  File "C:\Users\jupiterbjy\AppData\Roaming\JetBrains\PyCharm2021.3\scratches\scratch.py", line 25, in fetch_db
    async with get_db() as conn:
  File "E:\github\StreamNotifier-Web\venv\lib\site-packages\trio_mysql\connections.py", line 616, in __aenter__
    await self.connect()
  File "E:\github\StreamNotifier-Web\venv\lib\site-packages\trio_mysql\connections.py", line 683, in connect
    await self.autocommit(self.autocommit_mode)
  File "E:\github\StreamNotifier-Web\venv\lib\site-packages\trio_mysql\connections.py", line 442, in autocommit
    await self._send_autocommit_mode()
  File "E:\github\StreamNotifier-Web\venv\lib\site-packages\trio_mysql\connections.py", line 460, in _send_autocommit_mode
    await self._read_ok_packet()
  File "E:\github\StreamNotifier-Web\venv\lib\site-packages\trio_mysql\connections.py", line 448, in _read_ok_packet
    pkt = await self._read_packet()
  File "E:\github\StreamNotifier-Web\venv\lib\site-packages\trio_mysql\connections.py", line 742, in _read_packet
    raise err.InternalError(
trio_mysql.err.InternalError: Packet sequence number wrong - got 3 expected 1

Expected behavior

Equivalent code:

from getpass import getpass
import pymysql


user = input("DB User> ")
pwd = getpass("DB Pwd > ")
db_name = input("DB Name> ")


def get_db():
    conn = pymysql.connect(
        host="localhost",
        user=user,
        password=pwd,
        db=db_name,
        charset="utf8mb4",
        cursorclass=pymysql.cursors.DictCursor
    )
    print("Created DB connection")
    return conn


def fetch_db():
    with get_db() as conn:
        with conn.cursor() as cur:
            cur.execute(f"SELECT * FROM test")
            data = cur.fetchall()
    print(data)


fetch_db()

output:

DB User> root
DB Pwd > 
DB Name> test_mysql
Created DB connection
[{'id': 1, 'val': "test post that shouldn't get deleted"}]

Environment

• mysql: Ver 8.0.27 for Win64 on x86_64
• trio-mysql: 1.0.3
• python 3.10.1 x64 / python 3.9.2 x64
• trio 0.19.0

[jupiterbjy]

Seems like it's fine with latest mariadb (which is still on major version 10), if anyone having trouble, either downgrade mysql or just use mariadb.