SSL handshake error with MySQL 8.0.34

[LeeMendelowitz]

I'm experiencing an issue where asyncmy 2.8.0 with Python 3.11 is generating an SSL Bad Handshake with MySQL 8.0.34. I'm not experiencing this issue with asyncmy when connecting to MySQL 8.0.26. I'm also not experiencing this issue if I switch to aiomysql and connect to MySQL 8.0.34.

So it appears that the issue is specific to asyncmy 2.8.0 and MySQL 8.0.34.

Here is some example code I'm using to reproduce the issue:

import ssl

from dotenv import dotenv_values
from sqlalchemy import text
from sqlalchemy.ext.asyncio import create_async_engine
import asyncio

env = dotenv_values(".env")

ctx = ssl.SSLContext(ssl.PROTOCOL_TLS_CLIENT)
ctx.check_hostname = False
ctx.load_verify_locations(cafile=env["SSL_CERT_PATH"])


engine_aiomysql = create_async_engine(
    f"mysql+aiomysql://{env['USERNAME']}:{env['PASSWORD']}@{env['HOST']}:{env['PORT']}",
    connect_args={"ssl": ctx}
)

engine_asyncmy = create_async_engine(
    f"mysql+asyncmy://{env['USERNAME']}:{env['PASSWORD']}@{env['HOST']}:{env['PORT']}",
    connect_args={"ssl": ctx}
)

async def test_connection_aiomysql():
    async with engine_aiomysql.connect() as conn:
        res = await conn.execute(text("SELECT CURRENT_USER();"))
        print(res.fetchall())

async def test_connection_asyncmy():
    async with engine_asyncmy.connect() as conn:
        res = await conn.execute(text("SELECT CURRENT_USER();"))
        print(res.fetchall())

async def main():
    try:
        await test_connection_aiomysql()
        print("Success with aiomysql")
    except Exception as e:
        print(e)
        print("Error with aiomysql")

    try:
        await test_connection_asyncmy()
        print("Success with asyncmy")
    except Exception as e:
        print(e)
        print("Error with asyncmy")

if __name__ == '__main__':
    asyncio.run(main())

which generates output when run against a connection to MySQL 8.0.34:

[('lee@%',)]
Success with aiomysql
(asyncmy.errors.OperationalError) (1043, 'Bad handshake')
(Background on this error at: https://sqlalche.me/e/20/e3q8)
Error with asyncmy

In case it's helpful, here are some outputs showing the differences in TLS versions between the MySQL 8.0.26 and MySQL 8.0.34 instances I'm using:

MySQL 8.0.26

mysql> show variables like '%tls%';
+------------------------+-------------------------------+
| Variable_name          | Value                         |
+------------------------+-------------------------------+
| admin_tls_ciphersuites |                               |
| admin_tls_version      | TLSv1,TLSv1.1,TLSv1.2,TLSv1.3 |
| tls_ciphersuites       |                               |
| tls_version            | TLSv1,TLSv1.1,TLSv1.2,TLSv1.3 |
+------------------------+-------------------------------+
4 rows in set (0.01 sec)

MySQL 8.0.34

mysql> show variables like '%tls%';
+------------------------+-----------------+
| Variable_name          | Value           |
+------------------------+-----------------+
| admin_tls_ciphersuites |                 |
| admin_tls_version      | TLSv1.2,TLSv1.3 |
| tls_ciphersuites       |                 |
| tls_version            | TLSv1.2,TLSv1.3 |
+------------------------+-----------------+
4 rows in set (0.01 sec)

[sublai]

If anybody is experimenting this issue, switch to the aiomysql driver, it works with the following code:

import ssl
settings = YOUR_SETTINGS


ssl_context = ssl.create_default_context(ssl.Purpose.SERVER_AUTH)
ssl_context.options |= ssl.OP_NO_TLSv1 | ssl.OP_NO_TLSv1_1
ssl_context.load_verify_locations(settings.SQL_SSL_CA)
ssl_context.load_cert_chain(certfile=settings.SQL_SSL_CERT, keyfile=settings.SQL_SSL_KEY)
ssl_context.check_hostname = False
ssl_args = {
    "ssl": ssl_context
}

engine = create_async_engine(
    settings.SQLALCHEMY_DATABASE_URI,
    connect_args=ssl_args
)

[Cycloctane]

It seems that this issue is more likely to be caused by inconsistent values of max_packet_size and character_set between SSLRequest and HandshakeResponse in mysql client packets (CLIENT_PROTOCOL_41) during the handshake.

https://dev.mysql.com/doc/dev/mysql-server/latest/page_protocol_connection_phase.html#sect_protocol_connection_phase_initial_handshake_ssl_handshake

asyncmy/asyncmy/connection.pyx

Line 755 in 161b2dd

data = IIB.pack(self._client_flag, 16777216, 33)

These values in client SSLRequest packet is hardcoded to 0x1000000(16777216) and 0x21(utf8) when ssl is enabled. However, these two values can be different in client HandshakeResponse packet (0xffffff and 0x2d(utf8mb4) by default). It seems that newer versions of mysql8 complain about these changes, which results in raising 1043 Bad Handshake.

I'd like to open a Pull Request to fix it. @long2ice

[Cycloctane]

Small patch: Cycloctane@72d8e81

Tested with python 3.11 and mysql server 8.0.36 on debian bookworm. Patched version works well with tlsv1.2 enabled, while v0.2.9 causes bad handshake errors.

import asyncio
import ssl
from sqlalchemy import text
from sqlalchemy.ext.asyncio import create_async_engine

ssl_context = ssl.SSLContext(ssl.PROTOCOL_TLSv1_2)
ssl_context.load_cert_chain(certfile=..., keyfile=...)
async_engine = create_async_engine("mysql+asyncmy://...", connect_args={"ssl": ssl_context})

async def main():
    async with async_engine.connect() as conn:
        result = await conn.execute(text("SHOW DATABASES"))
    print(result.all())

if __name__ == "__main__":
    loop = asyncio.get_event_loop()
    loop.run_until_complete(main())